# Thread: Converting elapsed time to a date (2003)

1. ## 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!

2. ## 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. ## 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.

4. ## 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>.

5. ## 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. ## 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. ## 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
•