# Convert Unix Timestamp to Excel Date

October 19, 2021 Door Bjorn Meijer

## 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_IDDateNumber
1January 1, 19001
2Dec 15, 199535048
3June 24, 200137066
4July 13, 2003 3:00 PM37832,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_IDHoursTimefractureWhere the
1101:001/240,042
2202:002/240,083
3303:003/240,125
4404:004/240,167
5505:005/240,208
6606:006/240,250
7707:007/240,292
8808:008/240,333
9909:009/240,375
101010:0010/240,417
111111:0011/240,458
121212:0012/240,500
131313:0013/240,542
141414:0014/240,583
151515:0015/240,625
161616:0016/240,667
171717:0017/240,708
181818:0018/240,750
191919:0019/240,792
202020:0020/240,833
212121:0021/240,875
222222:0022/240,917
232323:0023/240,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)