Change date format to custom
 

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

Change date format to custom
 

Eric,

Use the custom format

mmm dd, ddd

Mike

"Eric" wrote:

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

Change date format to custom
 

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

mmmm dd, ddd

Mike

"Eric" wrote:

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

Change date format to custom
 

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

Jim Thomlinson


"Eric" wrote:

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

Change date format to custom
 

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