Creating human readable dates from SBS database timestamps in Excel

Version 1

    Have you ever wanted to export data from your database to Excel, but the time is in epoch time and it looks something like "1232056941623" ?

     

    Here's the step by step for creating a new column with readable dates:

     

    Start by importing the raw data into Excel

    It will look something like this: http://content.screencast.com/users/klassikstile/folders/Jing/media/c0ee08c0-c217-48c3-9bf5-683f2d278dc2/epochCreationDates.png then add a new column, like  this: http://content.screencast.com/users/klassikstile/folders/Jing/media/67cccc19-c1cd-482e-8b40-885b78be641c/new_Column.png and then enter the following fomula: =(((<CELL>-(6*360000))/86400000)+25569) and drag it to the rest of the cells! The explaination for this formula can be found here: http://www.blindhog.net/convert-epoch-time-in-excel/ . Though it is slightly different as we are tracking milliseconds, not just seconds.

     

     

    You'll then end up with a bunch of decimals, like:http://content.screencast.com/users/klassikstile/folders/Jing/media/2bc93f95-6e89-478b-bf89-0a2552aef78c/decimals.png and you then need to format that column. Do that by selecting the column -> format cells, and then choose: http://content.screencast.com/users/klassikstile/folders/Jing/media/b31f33c8-8f88-4c5f-b78f-f735d1f25c59/format_date.png

     

     

    which will produce the final result: http://content.screencast.com/users/klassikstile/folders/Jing/media/96e191a9-28ea-42b3-8fbc-41587e57088b/dates!.png and you're good to go.

     

    Hope this helps out! If not, it'll be a good resource for me in the future