Calculate current date from fixed time?

Is there a formula that I can use to calculate a date/time from a number based on the fixed date of January 1, 1601?

If this sounds familiar to you, you'll know that I'm trying to convert a large integer into a date/time format. In this case, I'm exporting users from a domain controller and trying to view the LastLogonTime variable that's only recorded as a large integer. If you're really nerdy, here's the gist of the topic.

http://www.microsoft.com/technet/scr...lastlogon.mspx

Anyway, I've done enough of the calculations in Excel to convert the variable into days. Now I just need to finish it by converting this number into a date/time.

Say, for example: I have one instance calculated to 147851.966 days. Now I need to determine what date/time this is based off of a January 01, 1601 start date.

Confusing enough?

No, not confusing: pretty easy, actually.

Subtract an constant value from your Long value: calc the value by using one example: if you know
what date time 147851.966 actually is, then it is easy.

For example

=147851.966 - 112345

formatted for date should show the correct date time - of course, change the 112345 to get the
correct date time. (if you had told us what the date time was, I would have calc'd it for you.....)

HTH,
Bernie
MS Excel MVP


"mandg" <[email protected]> wrote in message
news:[email protected]...
>
> Is there a formula that I can use to calculate a date/time from a number
> based on the fixed date of January 1, 1601?
>
> If this sounds familiar to you, you'll know that I'm trying to convert
> a large integer into a date/time format. In this case, I'm exporting
> users from a domain controller and trying to view the LastLogonTime
> variable that's only recorded as a large integer. If you're really
> nerdy, here's the gist of the topic.
>
> http://www.microsoft.com/technet/scr...lastlogon.mspx
>
> Anyway, I've done enough of the calculations in Excel to convert the
> variable into days. Now I just need to finish it by converting this
> number into a date/time.
>
> Say, for example: I have one instance calculated to 147851.966 days.
> Now I need to determine what date/time this is based off of a January
> 01, 1601 start date.
>
> Confusing enough?
>
>
> --
> mandg
> ------------------------------------------------------------------------
> mandg's Profile: http://www.excelforum.com/member.php...o&userid=34986
> View this thread: http://www.excelforum.com/showthread...hreadid=547264
>

"mandg" <[email protected]> wrote in
message news:[email protected]...
>
> Is there a formula that I can use to calculate a date/time from a number
> based on the fixed date of January 1, 1601?
>
> If this sounds familiar to you, you'll know that I'm trying to convert
> a large integer into a date/time format. In this case, I'm exporting
> users from a domain controller and trying to view the LastLogonTime
> variable that's only recorded as a large integer. If you're really
> nerdy, here's the gist of the topic.
>
> http://www.microsoft.com/technet/scr...lastlogon.mspx
>
> Anyway, I've done enough of the calculations in Excel to convert the
> variable into days. Now I just need to finish it by converting this
> number into a date/time.
>
> Say, for example: I have one instance calculated to 147851.966 days.
> Now I need to determine what date/time this is based off of a January
> 01, 1601 start date.
>
> Confusing enough?

According to http://www.decimaltime.hynes.net/dates.html the offsets of Unix
time & Excel time differ by 109205 days. If you subtract that from your
147851.966 value you should get a date time in Excel terms (23:11:02 on
21/10/05).
--
David Biddulph