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

 

[dk]

Hello,

I think I'm correct in saying that UNIX time is the number of seconds that has elapsed since 1/1/1970. Excel calculates dates by using the number of days that has elapsed since 1/1/1900. Therefore you should be able to convert from one to the other by converting from seconds to days, and then adding on the 70 odd years difference.

The formula :- =(A1/86400)+25569 where A1 contains the UNIX time should convert to Excel date/time. Make sure you format the cell as the required date/time format.

 

[Colombo]

That does seem to work to go from epoch to normal time, but what I really need is to go from normal time to epoch time.

Like you said, since the excel time function goes from 1/1/1900 I assume you'd be able to rig something......

 

[dk]

Just do the opposite mate:-

=(A1-25569)*86400

 

[Colombo]

That seems to work

Thanks

Josh

 

[sweetlb]

How can I use this formula (=(A1/86400)+25569) but account for my time zone?
IE: I enter decimal time of 1213636585 and the formula gives me "6/16/2008 17:16:25" which is GMT, but my server is in EST, so I want it to read 6/16/2008 13:16:25".

I also need to account for all four US time zones.

Thanks
Brian

 

[dvenus]

Hi!

I found this site that had info on accounting for time zones:

http://untangible.com/blog/2009/01/...rosoft-excel-including-timezone-examples.html

DVENUS

 

[maxdog0099]

I'm attempting the same epoch conversion in Excel 2007 but the result is nothng but tons of # signs in the cell. For the cell format I've tried mm/dd/yyyy hh:mm along with just about all others and the same result happens. What am I doing wrong here?

 

[dvenus]

Hi!

Not sure this is your problem but make sure your Excel column is wide enough. You can either double click on the column seperator or drag it.

Dave Venus

 

[maxdog0099]

Thanks. I've tried adjusting the column width which makes no difference. I can make it as wide as I want and still filled with all ## symbols.