All Collections
Tutorials
How to convert between UNIX timestamp to readable date
How to convert between UNIX timestamp to readable date

This tutorial covers the conversion between Unix timestamp and date in Excel.

J
Written by John Young
Updated over a week ago

The unix time stamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970 at UTC. Therefore, the unix time stamp is merely the number of seconds between a particular date and the Unix Epoch. 

This point in time technically does not change no matter where you are located on the globe. This is very useful to computer systems for tracking and sorting dated information in dynamic and distributed applications both online and client side.

This format is also used extensively in cryptocurrency APIs, so you see it pop up quite frequently.

But for us, it is not very friendly for a human-readable format. Therefore, this article shows you how to convert from UNIX timestamp to human-readable format. 

What does Unix timestamp look like?

Here is a sample pull of Bitfinex candles OHLCV (Open/High/Low/Close/Volume). 

You can see that, instead of date, the API call returns UNIX timestamp.

How do we convert it?

Convert Timestamp to Date

To easily convert UNIX timestamp to date, do the following:

  • In a blank cell next to your timestamp list and type this formula
    =(((A1/60)/60)/24)+DATE(1970,1,1)
    , press Enter key

  • Then right click the cells used the formula, and select Format Cells from the context menu, then in the popping Format Cells dialog, under Number tab, click Date in the Category list, then select the date type in the right section.

  • Click OK, and just like magic the Unix timestamps have been converted to proper, human-readable dates.

Did this answer your question?