Convert EPOCH (UTC) time in excel
Posted by Josh on Tue 24 Oct 2006Categories: Cisco , Misc - [19] Comments
Click Image to play tutorial:
In Call Manager, the CDR exports are in EPOCH time. Here is the formula to convert epoch time in a Call Manager CDR to a standard format in excel. After using the formula, you will need to format the cell for date and time.
=(((E2-(6*3600))/86400)+25569)
- E2 = cell reference
- 6 = Timezone Offset (this is Central Standard time)
- 3600 = Number of seconds in an hour
- 86400 = Number of seconds in a year
- 25569 = Excel hack because excel counts epoch from 1/1/1900 and most others start at 1/1/1970.
References:
http://www.mrexcel.com/archive2/44100/51255.htm
http://www.peterabowen.com/?p=8
September 6th, 2007 at 8:29 am
Thank you!
November 21st, 2007 at 9:32 pm
Awesome…. thanks heaps
April 18th, 2008 at 10:25 am
This is really helpful.
Thank you very much!!!!!!!
May 15th, 2008 at 12:05 pm
Thank you. This is exactly what I needed.
June 16th, 2008 at 12:10 pm
Thank you SOOOOO much. I’ve been making myself crazy trying to figure this out.
June 30th, 2008 at 2:39 pm
Thank you so much for this formula. You’ve done a great service for a lot of people out there.
July 29th, 2008 at 6:27 am
Thank alot Buddy……………….
u r too Good……………..
October 3rd, 2008 at 2:12 am
Thanks! and how do you convert “real” normal date to Epoch time?
October 10th, 2008 at 12:11 am
WOW! This is awesome. Thank you so much
January 20th, 2009 at 3:54 pm
Thanks
February 26th, 2009 at 3:05 am
Thank you so much bro, you relieved my headache with this.
March 5th, 2009 at 3:54 pm
HUGE HELP!! THANKS SO MUCH!
March 5th, 2009 at 9:42 pm
Valerie,
You are welcome.
Josh
May 9th, 2009 at 3:08 am
Sorry, 86400 is the number of seconds in a 24-hour day (=24*60*60). The number of seconds in a week and a 52-week year are 604800 and 31449600, respectively.
May 15th, 2009 at 8:59 am
When comparing the output of the formula originally posted on this site with epoch to GMT time converted by http://www.epochconverter.com and http://www.onlineconversion.com/unix_time.htm it appeared that the outputs did not match.
I had better luck with formula documented at the following site…
http://untangible.com/blog/2009/01/covert-unix-epoch-dates-in-microsoft-excel-including-timezone-examples.html
Converting epoch time (1242277022) to GMT time (5/14/09 4:57:02 AM).
=(H2/86400)+25569
where H2 is a cell in the CUCM CDR column for dateTimeOrigination.
May 20th, 2009 at 8:52 am
Thanks SO much!!!!
July 28th, 2009 at 9:28 am
Thanks a ton! I have been looking for this for over a month!
August 14th, 2009 at 11:16 am
Awesome tutorial! Helped me out a ton.
January 14th, 2010 at 4:55 am
Thanks a million tons for this