PC Review


Reply
Thread Tools Rate Thread

Change date format to custom

 
 
Eric
Guest
Posts: n/a
 
      2nd May 2008
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!!
 
Reply With Quote
 
 
 
 
Mike H
Guest
Posts: n/a
 
      2nd May 2008
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!!

 
Reply With Quote
 
 
 
 
Mike H
Guest
Posts: n/a
 
      2nd May 2008
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!!

 
Reply With Quote
 
Jim Thomlinson
Guest
Posts: n/a
 
      2nd May 2008
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!!

 
Reply With Quote
 
Eric
Guest
Posts: n/a
 
      2nd May 2008
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!!

 
Reply With Quote
 
Rick Rothstein \(MVP - VB\)
Guest
Posts: n/a
 
      3rd May 2008
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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I change the US date format to UK date format? Bregelad Microsoft Word Document Management 1 11th Nov 2009 09:36 PM
Need help with converting CUSTOM format/TEXT format to DATE format Deo Cleto Microsoft Excel Worksheet Functions 6 2nd Jun 2009 08:14 PM
Can I change a date with no format (20051111) to date format? =?Utf-8?B?Um9zZQ==?= Microsoft Excel New Users 2 11th Nov 2005 10:03 PM
Change german date-format in englisch date-format? pedros25 Microsoft Excel Programming 2 16th Mar 2004 11:28 AM
Change a date in text format xx.xx.20xx to a recognised date format concatenator Microsoft Excel Programming 1 25th Nov 2003 12:33 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:03 AM.