Change date format to custom

Discussion in 'Microsoft Excel Misc' started by Eric, May 2, 2008.

  1. Eric

    Eric Guest

    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, May 2, 2008
    #1
    1. Advertisements

  2. Eric

    Mike H Guest

    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!!
     
    Mike H, May 2, 2008
    #2
    1. Advertisements

  3. Eric

    Mike H Guest

    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!!
     
    Mike H, May 2, 2008
    #3
  4. 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!!
     
    Jim Thomlinson, May 2, 2008
    #4
  5. Eric

    Eric Guest

    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!!
     
    Eric, May 2, 2008
    #5
  6. 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
     
    Rick Rothstein \(MVP - VB\), May 3, 2008
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    1
    Views:
    296
    Guest
    Mar 30, 2005
  2. Guest

    Convert date from text format to date format

    Guest, Jun 4, 2007, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    337
    David Biddulph
    Jun 4, 2007
  3. Flagworld

    convert serial date format to normal date format

    Flagworld, Sep 23, 2008, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    583
    Dave Peterson
    Sep 23, 2008
  4. ejschmitt2000

    Auto Cell Format Change from General to Custom Date

    ejschmitt2000, Sep 11, 2009, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    325
    Dave Peterson
    Sep 11, 2009
  5. BlackNarcissus

    Converting complex date format to normal date format in Excel

    BlackNarcissus, Jul 28, 2011, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    554
    BlackNarcissus
    Jul 28, 2011
Loading...

Share This Page