Brooke Bryan – Make Life Easy, Automate Everything

Today, I had the problem of converting a timestamp to a readable time format inside excel.  Not quite as easy as I would have hoped for, but the solution is fairly painless.

In the excel column you wish to display the date, you need to place the following formula.

=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1)

You would then need to replace COLUMN_ID_HERE with the cell that holds the timestamp. e.g. A1

The above formula can be simplified, but I think the above explains a little more what is actually being done to the data

You would then need to format the cell to be dd/mm/yyyy hh:mm and you are done

1. Vijay says:

   October 3, 2011 at 6:09 am

   worked perfectly