Download the example workbook
This tutorial will demonstrate how to convert a time in Excel’s format to Unix time in Excel and Google Sheets.
What is Unix Time?
Unix time is also known as Epoch Time or POSIX time or Unix timestamp. It’s a system that counts the number of seconds that have elapsed since the Unix Epoch, i.e. January 1st, 1970. To put in simple words, Unix time is the total number of seconds between a date and Unix Epoch.
Convert Excel Time to Unix Time
To calculate Unix time, first, we need to find the total number of days between the Excel time and Epoch time. Then multiply the calculated days by 86,400, because a day has 86,400 seconds (24 hours × 60 minutes ×60 seconds = 86,400 seconds).
Step by Step Explanation
The first step in the conversion of Excel time to Unix time is to calculate the numeric value of the Epoch date. This can be done by using the DATE function.
It gives us this value:
In the same way, the calculation of the numeric value of Epoch date is subtracted from the given Excel time’s numeric value
After that, the difference calculated from above is multiplied by 86400 to get the resultant value in seconds
This is the total number of seconds, we get, between two dates
Convert Unix Time to Excel Time
Inversely, we can convert the Unix time to Excel time with the help of the following formula:
Step By Step Explanation
First, the Unix timestamp is divided by the total number of seconds in a day, i.e. 86400.
Now, we calculate the numeric value of the Epoch date through DATE function
Once we have the numeric value of the Epoch date, we’ll add these two values
= 14853 + 25569
The resultant value, we got after the calculation, is in the serial date number format. To view it in date format, simply change the format of the cell to Date or your required custom format from the Number Format by accessing it through the Home Tab
or by pressing CTRL + 1
After changing the format of the numeric values of the Excel date & time, we get the following Excel date and time
Convert Excel Time to Unix Time in Google Sheets
The conversion formula for time to Unix time works exactly the same in Google Sheets as in Excel.