Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    date time convertion (xl2000)

    I have an .xml file with many dates listed as follows
    <start>1128196800000</start>
    <end>1128628800000</end>
    When opened the xml file in excel the date including time are imported as numbers
    How do I reconvert back to date/time. Something with 1900 format seconds from 1/1/1900 maybe?
    Thanx
    Smbs

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date time convertion (xl2000)

    My guess is that the numbers represent the number of milliseconds since midnight on 1/1/1970. The first date would correspond to 9/30/2005, the second to 10/5/2005. Does that sound reasonable? If so, with one of those numbers in A1, the formula

    =A1/86400000+25568

    will convert to a number that Excel can interpret as a date. Use Format | Cells... to format the result as a date.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date time conversion (xl2000)

    Thanx HansV,
    Its nearly correct the correct dates are Oct-01-05 13:00:00 PDT and Oct-06-05 13:00:00 PDT (PDT PACIFIC DAY TIME)
    On my computer (GMT+2) I receive the following in excel 9/30/05 8:00 PM and 10/5/05 8:00 PM
    Difference seems to be in the wrong direction mayby 1 day is missing in your formula?
    Regards and many thanks
    Smbs

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date time conversion (xl2000)

    If you already knew which dates/times corresponded to those numbers, you could have told us instead of letting us try to work them out.
    Just change 25568 to 25569 in the formula, and you'll get the date/time in UTC. Subtract 7/24 to get the time in PDT.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date time conversion (xl2000)

    My apologies -- Should have given u the dates.
    Thanx once again
    Smbs

Posting Permissions

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