Forum Discussion
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?
What do you mean under standard time here, datetime value (2020-03-06 00:58:34) or only time value (00:58:34)?
