Results 1 to 7 of 7
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Converting elapsed time to a date (2003)

    Assume that I know the number of milliseconds that have elapsed since January 1, 1970. I want to convert that information to a date and time. Is this possible? I'll assume that I enter the number of milliseconds in A1, a formula in B1, and format B1 to a datetime format. The formula must add the number of milliseconds to "something" that equates to January 1, 1970, and come with a date and time. Hope this makes sense. Thanks!
    JimmyW
    Helena, MT

  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: Converting elapsed time to a date (2003)

    Thanks, (I got it correct in the formula and missed it in the explanation). I corrected the post.

    Steve

  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Converting elapsed time to a date (2003)

    Thanks very much, Hans and Steve! The formula will work perfectly, but I think the 86,400,000,000 should be 86,400,000 in Steve's formula , as a millisecond is 1/1000 second, and the 1,000,000 in Hans' should be 1,000. Please correct me is I misunderstood.
    JimmyW
    Helena, MT

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

    Re: Converting elapsed time to a date (2003)

    Ah yes, of course. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>.

    I will add a note to my previous reply.

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

    Re: Converting elapsed time to a date (2003)

    If you want the formula to be slightly more transparent (and slower, but you won't notice that if the number of cells with such a formula is limited)
    <code>
    =DATE(1970,1,1)+A1/(24*60*60*1000000)
    </code>

    (Steve left a zero off the large number in his explanation, but his formula is correct)

    Added later: 1 second = 1000 ms, so the 1000000 above should be 1000, of course. Sorry for the confusion.

  6. #6
    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: Converting elapsed time to a date (2003)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> also. I was thinking microseconds for some odd reason....

    [I will correct my post]
    Steve

  7. #7
    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: Converting elapsed time to a date (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 31-Oct-06 18:13. Corrected since I confused micro with millisecs)</P>If the milliseconds are in A1
    =25569+A1/86400000

    Format cells something like (or as desired)
    mmm d, yyyy hh:mm:ss

    25569 is the serial date of 1/1/1970

    86400000 is the number of milliseconds in a day (=1000 msec/sec*60sec/min*60min/hr*24hr/day)

    Steve

Posting Permissions

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