Convert Unix Timestamp to Excel Date

October 19, 2021 0 Door Bjorn Meijer

Table of contents

What is a Unix Timestamp?

A Unix timestamp is the number of seconds that have passed since January 1, 1970 midnight. This does not include the leap seconds.

How Excel handles date and time.

To convert a Unix timestamp to a readable date, let's first look at how Excel handles dates. As you read above, the Unix timestamp starts on January 1, 1970.
Excel started counting dates on January 1, 1900. Excel reads the date as a numerical number. In the table below you see the date in the left column as we read it, in the right column as Excel reads a date.

wdt_ID Date Number
1 January 1, 1900 1
2 Dec 15, 1995 35048
3 June 24, 2001 37066
4 July 13, 2003 3:00 PM 37832,63

On the last line of the table above you see that a time has been added to the date. Because in Excel, a 24-hour day equals 1, an hour equals \frac{\text{1}}{\text{24}} =0.042. Time can be represented in Excel as a fractional value of 1 (see table below). To display the value as a time format after a date, the time format must be added in the cell properties.

wdt_ID Hours Time fracture Where the
1 1 01:00 1/24 0,042
2 2 02:00 2/24 0,083
3 3 03:00 3/24 0,125
4 4 04:00 4/24 0,167
5 5 05:00 5/24 0,208
6 6 06:00 6/24 0,250
7 7 07:00 7/24 0,292
8 8 08:00 8/24 0,333
9 9 09:00 9/24 0,375
10 10 10:00 10/24 0,417
11 11 11:00 11/24 0,458
12 12 12:00 12/24 0,500
13 13 13:00 13/24 0,542
14 14 14:00 14/24 0,583
15 15 15:00 15/24 0,625
16 16 16:00 16/24 0,667
17 17 17:00 17/24 0,708
18 18 18:00 18/24 0,750
19 19 19:00 19/24 0,792
20 20 20:00 20/24 0,833
21 21 21:00 21/24 0,875
22 22 22:00 22/24 0,917
23 23 23:00 23/24 0,958

From Unix Timestamp to Excel Date

A Unix timestamp is like up here describes the number of seconds that have elapsed since January 1, 1970. A full day therefore consists of 60*60*24=86.400 seconds (60 seconds x 60 minutes x 24 hours). If we divide the Unix timestamp by 86,400, we have the number of days from January 1, 1970. If we add the date value of January 1, 1970 (in Excel this is the number 25,569), we have the Excel date. We can do this using the formula:

=A2/86400+DATE(1970;1;1)
Unix Timestamp to Excel Date
Unix Timestamp to Excel Date