Creating human readable dates from SBS database timestamps in Excel

    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