Excel Formula: Convert Excel Time to Unix Timestamp or vice inverse

In this essay, it talks about the formulas that convert between Excel time and Unix timestamp in Excel sheet.

Read before formula:

What is Unix timestamp?

Unix timestamp records time by using seconds. In the Unix system, it counts time from January 1, 1970. In other words, the Unix timestamp is the total number of seconds between the given date and January 1, 1970.

If you want to follow along with this tutorial, please download the example spreadsheet.

Convert Excel time to Unix timestamp

Generic formula:

Syntaxt and Arguments

Return Value

The formula returns a series number. Usually the returned value is such big that it displays as #####, and you can format the returned value as General or Number to show it as a regular series number.

Remark

If the given Excel time is greater than or equal to Jan 1, 1900 but less than Jan 1, 1970, it returns a negative number; if the Excel time is equal to Jan 1, 1970, it returns zero; if the Excel time is greater than Jan 1, 1970, it returns a positive number.

Error

If the Excel time is less than Jan 1, 1900, the result returns an error value #VALUE!

How this formula work

Here to convert the Excel time in cell B3:B6 to Unix Time, please use below formula:

Press Enter key to get the first result, then drag auto fill handle down to cell C7.

Explanation

Date function: create a date with the year number, month number and day number.

The formula (B3-DATE(1970,1,1)) is used to calculate the difference between the given date time and the Unix epoch.

86400: As we known, one day = 24 hours, one hour = 60 minutes, and one minute = 60 seconds, so one day = 24*60*60=86400 seconds.

Convert Unix timestamp to Excel time

Generic formula:

Syntaxt and Arguments

Return Value

The formula returns a series number, then you need to format the series number to a datetime formatting. Usually the result is a positive series number, if the result is a negative number, it will return to a series of ##### after formatting it as datetime.

How this formula work

To convert the Unix time in cell B3:B6 to Excel time, please use below formula:

Press Enter key and drag auto fill handle down to cell C6.

Then keep the results selected, then press Ctrl + 1 to display the Format Cells dialog, under Number tab, select Date from the Category list, then in the right section of Type, choose one format you need.

Click OK then the formula results have been formatted as datetime.

Explanation

Date function: create a date with the year number, month number and day number.

The formula (B3/86400)+DATE(1970,1,1) gets the total number of days between the given date time and the Unix epoch.

86400: As we known, one day = 24 hours, one hour = 60 minutes, one minute = 60 seconds, so one day = 24*60*60=86400 seconds. And vice versa, a date/86400 will get the day number.

Relative Formulas

• Convert Decimal Minutes to Time Format
  This tutorial provides a formula to convert decimal minutes to Excel time.
• Convert Decimal Seconds To Time
  To convert decimal seconds to Excel time in Excel, a formula can help you.
• Convert Time to Decimal Hours
  Here provides a formula that converts time to decimal hours in Excel and explains how the formula works.
• Convert Time To Decimal Minutes
  In this tutorial, it provides a formula for converting time to decimal minutes in Excel.

Relative Functions

• Excel IF Function
  Test for specified conditions, then returns the corresponding values
• Excel VALUE Function
  Convert text to number.
• Excel MONTH Function
  The MONTH is used to get the month as integer number (1 to 12) from date.
• Excel DAY Function
  DAY function gets the day as a number (1 to 31) from a date
• Excel YEAR Function
  The YEAR function returns the year based on the given date in a 4-digit serial number format.