Change date format to custom

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Hello,

I have a spreadsheet containing dates in this format, using today, Friday
May 2nd, 2008 as an example:

05/02 F

I wish to change all of these to this format:

May 2, Fri

How do I do this?!

Thanks!!
 
Additionally, if you don't want the month truncated to 3 letters use

mmmm dd, ddd

Mike
 
Your existing date appears to just be text and not actually an XL date.
Assuming that to be the case there are a couple of options. 1 is to convert
your text to an actual date and then format the date. The other is to convert
your text to a date and then back to text...

Here is how to get the date
=DATE(2008,VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2)))
You can format that date using a custom format of "Mmm d, ddd"

Here is how you get the text
=TEXT(DATE(2008,VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2))), "Mmm d, ddd")
 
Thank you! You're right, the person who created the file just typed in the
date like that, it wasn't formatted a certain way.

I appreciate you clearing it up.
 
Here are some formulas that are a little bit shorter...
Here is how to get the date
=DATE(2008,VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2)))
You can format that date using a custom format of "Mmm d, ddd"
=--(LEFT(A2,5)&"/2008")

Here is how you get the text
=TEXT(DATE(2008,VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2))), "Mmm d, ddd")
Click to expand...

=TEXT(--(LEFT(A2,5)&"/2008"),"mmm d, ddd")

Rick
 
Back
Top