Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Phoenix, Arizona, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2000 Date conversion (Excel 2000 SR-1)

    The displayed date 4/1/2002 (format mm/dd/yy) converts when the .xls file is converted to .txt as 37347.

    What is the formula used to convert the base date 37347 to the formatted date 4/1/2002 ?? I need to convert the base value to the mm/dd/yyyy format programatically on another system....

    Thanks!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel 2000 Date conversion (Excel 2000 SR-1)

    The date 4/1/2002 is 37,347 days past 12/31/1899. Day 1 is Jan 1, 1900.

    You need to know on the other system what it is counting and what is the origin. Some systems use Day 1 as Jan 1, 1904, I have seen others as Jan 1, 1965. Others count seconds NOT days. Once you know the 2 systems you can adjust accordingly.

    You should be able to take 2 date values from the 2 systems and using the slope do a regression with slope/intercept to calculated the relationship

    Steve

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel 2000 Date conversion (Excel 2000 SR-1)

    The integer part of the number that Excel stores as a date is the number of days since Jan 0, 1900, ie 1 is Jan 1, 1900. Depending on what system you are migrating to, you may be off by 1. Lotus 123 had a bug in that it said that 1900 was a leap-year -- it was not. Excel copied this bug by design to save millions of people asking why Lotus and Excel weren't compatabile. For more details, see Chip Pearson's Page on Dates and Times . HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    New Lounger
    Join Date
    Sep 2002
    Location
    Phoenix, Arizona, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Date conversion (Excel 2000 SR-1)

    I appreciate the assistance. Excel seems to use a base date of 1 for Jan 1, 1900, but on the IBM mainframe system I an reading that date value on (MVS operating system), the programming tool I am using converts Jan 1, 1900 to IT'S base value of 693595. So what I did was add 693595 to the base date value that came over from Excel, then I let the software convert the 'new' base date value to the date format I wanted. All is good now.

    Thanks!

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel 2000 Date conversion (Excel 2000 SR-1)

    That would be counting from 1/1/0001
    David Grugeon
    Brisbane Australia

Posting Permissions

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