Converting normal time to Epoch time format using excel

Colombo

Board Regular
Joined
Apr 21, 2003
Messages
54
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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......
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top