Change date format to custom

[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!!

 

[Mike H]

Eric,

Use the custom format

mmm dd, ddd

Mike

 

[Mike H]

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

mmmm dd, ddd

Mike

 

[Jim Thomlinson]

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")

 

[Eric]

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.

 

[Rick Rothstein \(MVP - VB\)]

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")

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

Rick