Page 1 of 3 123 LastLast
Results 1 to 10 of 29
Like Tree2Likes

Convert number to date

This is a discussion on Convert number to date within the Excel Questions forums, part of the Question Forums category; I have a spreadsheet with a column of 3500 numbers (in this format 20030129 or 19971112 for example) they are ...

  1. #1
    New Member
    Join Date
    Jan 2003
    Posts
    1

    Default

    I have a spreadsheet with a column of 3500 numbers (in this format 20030129 or 19971112 for example) they are infact dates - I am struggling to format them as such. HELP!

    Many thanks in advance

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,464

    Default

    You will have to use a formula to convert them - they won't be recognised as dates by Excel.

    =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

    Change the reference to A1 as required and choose your date format.
    Benjaminmin likes this.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    =TEXT(A1,"0000-00-00")+0

    Format the cell containing this formula with an appropriate date format.

    [ This Message was edited by: Mark W. on 2003-01-31 13:30 ]
    proficient likes this.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,464

    Default

    On 2003-01-31 13:29, Mark W. wrote:
    =TEXT(A1,"0000-00-00")+0

    Format the cell containing this formula with an appropriate date format.

    [ This Message was edited by: Mark W. on 2003-01-31 13:30 ]
    Cool!

  5. #5
    New Member
    Join Date
    Dec 2008
    Posts
    1

    Default Re: Convert number to date

    I am trying to convert 38370 to date 19-Jan-2009.
    Using formula =DATE(RIGHT(A1,2),MID(A1,5,2),LEFT(A1,4)).
    But it gives starnge date.

    Number 38370 is generated by converting date "19-Jan-2009" to general format in excel sheet.

  6. #6
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: Convert number to date

    Just Format the cell as

    dd-mmm-yyyy

  7. #7
    New Member
    Join Date
    Jun 2011
    Posts
    5

    Default Re: Convert number to date

    Quote Originally Posted by Andrew Poulsom View Post
    Cool!
    This by far makes most sense.
    But what does +0 do in the function? Does it make the cell numeric?

  8. #8
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Convert number to date

    Quote Originally Posted by happypig View Post
    This by far makes most sense.
    But what does +0 do in the function? Does it make the cell numeric?
    Yes

    The TEXT( ) function returns a TEXT data type even if looks like a number.

    The math operation of adding 0 (+0) coerces the text string that represents a date into the true numeric date value.

    Another way to do it is:

    =--TEXT(A1,"0000-00-00")
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  9. #9
    New Member
    Join Date
    Jun 2011
    Posts
    5

    Question Re: Convert number to date

    Quote Originally Posted by T. Valko View Post
    Yes

    The TEXT( ) function returns a TEXT data type even if looks like a number.

    The math operation of adding 0 (+0) coerces the text string that represents a date into the true numeric date value.

    Another way to do it is:

    =--TEXT(A1,"0000-00-00")

    That is awesome. I knew taking a couple of programming courses at university should help understand Excel functions better in the future, haha!

    Thanks so much for your explanation.

  10. #10
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Convert number to date

    Quote Originally Posted by happypig View Post
    That is awesome. I knew taking a couple of programming courses at university should help understand Excel functions better in the future, haha!

    Thanks so much for your explanation.
    You're welcome. Thanks for the feedback!
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com