Convert EPOCH (UTC) Time in Excel
I’m getting off the CCIE Voice bandwagon for a brief moment. I ran into a real-world scenario today where I needed to pull CDR reports for an end-user’s call activity. The problem with a generic CDR user report is that it will be truncated if there are more than 100 calls in the output.
The best way around is to do an export of the entire CDR database for the time period in question. The file will be a .txt file. Rename it to <filename>.csv and open it in Excel.
The next problem you will encounter is that the dateTimeOrigination column is in epoch time instead of standard time legible by normal human beings. To convert this, you can use a handy formula that my Twitter-buddy Josh posted on his blog, BlindHog.net.
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:
that’s cool idea, though I used ‘CiscoCDRTimeConverter-0.1.0.0′ downloaded from Cisco…but thanks for the calculations behind
Mijanur Rahman
12 Aug 10 at 2:13 am