Converting Unix timestamp to date and time in Excel

Home / How to / Converting Unix timestamp to date and time in Excel

I had a weird issue today, at work, where I was given a Microsoft Excel file. I had unix timestamp in one column and I was required to change them all into normal date and time


Using excel, its pretty easy. Suppose that the unix timestamp is in column B, do this

=(((B2/60)/60)/24)+DATE(1970,1,1)+(-5/24)

Where B is the column name, 2 is the row number. Diving by 60/60/24 gives the days. Adding DATE because unix timestamp runs from 1970. “-5” is the GMT/UTC difference that you want to apply to the conversion

Now that you have a floating value as DAYS, right click on the cell, choose FORMAT CELL and choose DATE to display date and/or time.

However, If you only want to display time without the date part, choose TIME instead

One Comment

Leave a Reply