Converting normal time to Epoch time format using excel

[Colombo]

I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix epoch time format.

Is there a function or vba code that I can use in excel to convert the normal time format to epoch time?

I've got a thousand or so entries, so it would be nice to find a way to do this on a large scale.

Thanks

Josh

 

[barry houdini]

Can you give some details? This thread refers to conversion in both directions, which way are you going? What does your base date look like, what formula are you using to convert?

 

[maxdog0099]

I have an application which supplies epoch timestamps. I need them converted to human readable date/time in excel 2007. For example, an epoch timestamp of 1268940547353 should convert to GMT: Thu, 18 Mar 2010 19:29:07 UTC. The formula I'm using in excel is: =(((A2-(6*3600))/86400)+25569) which also accounts for Central Time. The resulting cell gives me all ### signs no matter how I format the cell for date/time.

 

[barry houdini]

A figure like 1268940547353 must be milliseconds from 1/1/1970 not seconds so you need to use 86400000 as the divisor (milliseconds in a day) and also change the timezone adjustment accordingly....try this formula and format to show date and time

=25569+(A2-6*3600000)/86400000

 

[maxdog0099]

Thanks! That did the trick and worked.

 

[mrbubs]

This is insanely helpful, thanks.

@maxdog0099 you need to change the data type of your cells -- to a date format for the excel timestamps, and to a number or text format for the Epoch timestamps. Otherwise Excel will try to make a date from the epoch ts, which causes the # error.

 

[aliceh]

This is enormously helpful, I do have on question though. when I convert from normal time to epoch time I can't then drag the box to get the formula to repeat, it simply copy's the same date all the way down whether I am holding the control key or not. I have to actually click up in the formula box and hit enter. Not a huge deal just a little more time consuming than I would like. Any ideas?

 

[barry houdini]

Sounds like you have calculation set to "manual" - in "Formulas" tab on right-hand side of the ribbon click on "calculation options" and set to "automatic"

 

[aliceh]

Thank you, I just checked that out and I do have it set to automatic. Any other suggestions?

 

[barry houdini]

OK, can you post the exact formula?

 

[aliceh]

=(A1-25569)*86400
this is the formula from this thread, it works very well, the problem is getting xcell to repeat the fromula. drag comand does not work