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

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

### The Best Office Productivity Tools

#### Kutools for Excel - Helps You To Stand Out From Crowd

 Popular Features: Find/Highlight/Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ... Super VLookup: Multiple Criteria  |  Multiple Value   |  Across Multi-Sheets  |  Fuzzy Lookup... Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Unhide Columns  |  Compare Columns to Select Same & Different Cells ... Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager  |  Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Supports Office/Excel 2007-2021 and 365   |   Available in 44 Languages   |   Easy to Uninstall Completely #### 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, Edge and Firefox. 