Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert Date 'number' to mm/dd/yy (Excel 2000)

    I have a file that I inherited that shows the date as mm/dd/yy. I need to separate the month and year into 2 separate columns. When I use the =LEFT(xxx) and =RIGHT(xxx) commands, it gives me the digits as if the date is a number rather than the date. How can I convert (not format) these to be in the mm/dd/yy rather than the number? Ex. 07/10/38 = 12609. =LEFT(cell, 2) gives me 12 not 07.

    Thanks for any help you can give me.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Convert Date 'number' to mm/dd/yy (Excel 2000)

    Use =YEAR(cell) and =MONTH(cell) in the separate columns
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Convert Date 'number' to mm/dd/yy (Excel 2000)

    I may have missed the point. 7/10/1938 is day number 14071. Where does 12609 come from?
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    New Lounger
    Join Date
    Apr 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Date 'number' to mm/dd/yy (Excel 2000)

    Thanks. Your first response works great. On my computer 7/10/38 is coming up 12609. If I put in 14071, I get 7/11/42.

    Thanks for a quick response.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Convert Date 'number' to mm/dd/yy (Excel 2000)

    Under Tools, Options, Calculation, you have the box checked for "1904 date system"?
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    New Lounger
    Join Date
    Apr 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Date 'number' to mm/dd/yy (Excel 2000)

    I wondered if that was the difference. At this point, the current dates are correct and are associated with employees so will leave for now.

    Thanks again for your help.

Posting Permissions

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