autocrib
asked on
Covert 10 digit number into date and time in Excel
I'm trying to convert a ten digit code into a date and time but more importantly time. These numbers are being extracted from an older Access Database.
Starttime - 1300371878 | Endtime - 1300371920
Starttime - 1300386867 | Endtime - 1300386893
If this helps any. I know that for a fact that:
1300371878 represents 7:24:55 AM
Starttime - 1300371878 | Endtime - 1300371920
Starttime - 1300386867 | Endtime - 1300386893
If this helps any. I know that for a fact that:
1300371878 represents 7:24:55 AM
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It looks like a Unix date value, if so then time zones will come into play. This should help you
https://www.experts-exchange.com/questions/25093826/Convert-Epoch-number-value-to-date-and-time-string.html
Michael
https://www.experts-exchange.com/questions/25093826/Convert-Epoch-number-value-to-date-and-time-string.html
Michael
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help!! It took both to resolve.
What is a UNIX Timestamp?
"If you import data you might encounter time values stored as Unix timestamps. Unix time is defined as the number of seconds since midnight (GMT time) on January 1, 1970 -- also known as the Unix epoch."
How to convert?
If your numeric value was in cell A1, the formula to convert it would be:
Open in new window
Divide it by 60 to get # of minutes since 1/1/1970.
Divide it by 60 again to get # of hours since 1/1/1970.
Divide it by 24 to get # of days since 1/1/1970.
Add the date 1/1/1970 to the value that remains to get today's date.
Make sure you format as a date/time field so the value makes sense (otherwise it reads similar to 39476.48).