SOLVED

# How do you convert from standard time to unix time in Excel?

Copper Contributor

# 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

8 Replies

# Re: How do you convert from standard time to unix time in Excel?

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.

# Re: How do you convert from standard time to unix time in Excel?

@PReaganThank you for responding. So is there actually no way to convert from standard to unix time?

# Re: How do you convert from standard time to unix time in Excel?

What do you mean under standard time here, datetime value (2020-03-06 00:58:34) or only time value (00:58:34)?

# Re: How do you convert from standard time to unix time in Excel?

@SergeiBaklanOnly the time. Military time to be exact.

best response confirmed by Alexis_Dougherty (Copper Contributor)
Solution

# Re: How do you convert from standard time to unix time in Excel?

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

# Re: How do you convert from standard time to unix time in Excel?

@SergeiBaklanThat works! Thank you so much!!!

# Re: How do you convert from standard time to unix time in Excel?

@Alexis_Dougherty , great, glad to help. Now I know bit more about UNIX time...

# Re: How do you convert from standard time to unix time in Excel?

@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.

1 best response

Accepted Solutions
best response confirmed by Alexis_Dougherty (Copper Contributor)
Solution

# Re: How do you convert from standard time to unix time in Excel?

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