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?


--
mandg
------------------------------------------------------------------------
mandg's Profile: http://www.excelforum.com/member.php...o&userid=34986
View this thread: http://www.excelforum.com/showthread...hreadid=547264

Calculate current date from fixed time?
 

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" wrote in message
...

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

Calculate current date from fixed time?
 

"mandg" wrote in
message ...

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