UTC Date/Time conversion

[m1c]

Is there a documented formula for converting UTC date/time stamps in CallDetailRecords (e.g., 1074902387) to a recognizable time/date?

[mcnerney]

Here is the Csico doc:

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a00801d71c5.shtml

However, there was a post by jbond_c24 on Mar 3, 2004 titled "These formulas don't work. Here is a" in his post he shows a Perl script that he says will work.

Good luck.

Good

[j.bien]

USE MICROSOFT EXCEL

1074902387/86400+DATE(1970,1,1)

RIGHTCLICK THEN FORMAT CELLS. SELECT NUMBER AND YOUR PREFERECE FORMAT THEN BAM!THAT'S IT

[oksiazek]

You can also use a little tool :

Cisco CDRTime Converter ...

you can go to this link to download the file :

http://www.ciscopress.com/content/images/1587050757/downloads/CiscoCDRTimeConverter-0.1.0.0.zip

[rikardkrvaric]

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 day
• 25569 = Excel hack because excel counts epoch from 1/1/1904 and most others start at 1/1/1970.

Found this formula on the following webpage:

http://ciscovoiceguru.com/473/convert-epoch-utc-time-in-excel/