Calculate current date from fixed time?

[mandg]

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

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

http://www.microsoft.com/technet/scriptcenter/topics/win2003/lastlogon.mspx

Anyway, I've done enough of the calculations in Excel to convert th
variable into days. Now I just need to finish it by converting thi
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 Januar
01, 1601 start date.

Confusing enough

 

[Bernie Deitrick]

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

 

[David Biddulph]

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/scriptcenter/topics/win2003/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).