Forum Discussion
Solved
How do you convert from standard time to unix time in Excel?
I am trying to convert standard time into unix for a project, except I don't know the formula to use.
I tried this one, which I found online, but it did not convert it correctly:
(STANDARDTIMECELL-DATE(1970,1,1))*86400
I guess military time is only the matter of formatting - if you have 01:34 time in Excel, applying hhmm it'll be shown as 0134.
Actually in Excel date is integer sequential number starting from 1 which equal to Jan 01, 1900. Time is decimal part of the number, 12:00 will be 12/24 = 0.5
Thus, if you use time only it is assumed Jan 01, 1900 as the date, e.g. 1900-01-01 01:34 for above time. Above formula compares it with 1970-01-01 00:00 and returns wrong result.
In brief, year is to be adjusted, e.g with TODAY(). I'm not sure what is the "time only" in UNIX. But for absolute time it could be like
Hello Alexis_Dougherty,
This conversion simply calculates the number of days since January 1, 1970 and multiplies by the number of seconds in a day (86400). It does not take into account leap seconds which may be where you are experiencing an error.
PReaganThank you for responding. So is there actually no way to convert from standard to unix time?
Alexis_Dougherty be aware that it may or may not be simple for you needs. time and date values are simply numbers based on a known reference point. a unix CTIME time, for example is the number of seconds that have elapsed since 01-January-1970 00:00:00 (midnight). they do take in to account simple math issues, such as leap years, but not complex ones, such as leap seconds. they generally go off "UTC" timezones (which is generally same as GMT) and are not *internally* adjusted for any other timezone or daylight savings, etc. the *display* of such may be adjusted when displayed.
