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

 

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

 

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

 

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

 

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.

"Jim Thomlinson" wrote:

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

 

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