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:
| (Excel_time-DATE(1970,1,1))*86400 |
Syntaxt and Arguments
| Excel_time: the datetime that you want to convert to Unix time. |
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:
| =(B3-DATE(1970,1,1))*86400 |
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:
| (Unix_time/86400)+DATE(1970,1,1) |
Syntaxt and Arguments
| Unix_time: the Unix time (a series number) that you want to convert to standard Excel time. |
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:
| =(B3/86400)+DATE(1970,1,1) |
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.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.
- Designed for 1500 work scenarios, helps you solve 80% Excel problems.
- Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
- Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
- 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
