Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Date formula?

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    georgia
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    does anyone have a formula for converting numbers to dates.

    ex. 03282002 = 03/28/2002

    Thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 12:21, trekcowboy wrote:
    does anyone have a formula for converting numbers to dates.

    ex. 03282002 = 03/28/2002

    Thanks
    =DATE(RIGHT(A10,4),LEFT(A10,2),MID(A10,3,2))

    But, that ASSUMES that the text length is 8 characters. I mean, month 3 is shown as 03 and day 8 is shown as 08
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 12:21, trekcowboy wrote:
    does anyone have a formula for converting numbers to dates.

    ex. 03282002 = 03/28/2002

    Thanks
    Hi trekcowboy:
    if 03282002 is in cell B11, then use

    =DATE(RIGHT(B11,4),LEFT(B11,2),MID(B11,3,2))


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Juan:
    We might have been posting at the same time!

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    georgia
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks to both of you, what if the month is not always two digits.

    on my spreadsheet excel is dropping the leading 0

    3282002

    thanks again

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What about the Day ? does it always have 2 digits ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about this formula ?

    =DATE(RIGHT(A11,4),LEFT(A11,(LEN(A11)=8)+1),MID(A11,(LEN(A11)=8)+2,2))
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 12:34, trekcowboy wrote:
    thanks to both of you, what if the month is not always two digits.

    on my spreadsheet excel is dropping the leading 0

    3282002

    thanks again
    Use =TEXT(REPT(0,8-LEN(A10))&A10,"00-00-0000")+0

    [ This Message was edited by: Mark W. on 2002-03-28 14:40 ]

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 13:12, Mark W. wrote:

    Use =TEXT(REPT(0,8-LEN(A10))&A10,"00-00-0000")+0
    Mark, just confirming, this assumes that the Date system uses mm-dd-yyyy right ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 13:14, Juan Pablo G. wrote:
    On 2002-03-28 13:12, Mark W. wrote:

    Use =TEXT(REPT(0,8-LEN(A10))&A10,"00-00-0000")+0
    Mark, just confirming, this assumes that the Date system uses mm-dd-yyyy right ?
    If your Regional date setting is dd/mm/yy and you enter 28032002 into cell A10 it will work as well.

    If you're concerned about the dashes instead of slashes... it makes no difference.

Some videos you may like

User Tag List

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
  •