CDR "dateTimeOrigination" conversion formula in Excel?

[d.gavin]

Can someone point me to some documentation or an answer for converting the "dateTimeOrigination" entry in the CDR database into a real Date/Time format. The timestamps are in UTC format and we would like to make the conversion within an Excel spreadsheet.

Thanks

[jbond_c24]

from

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

Understanding the Date Format in the CDR Database

To select all calls after a certain date, you need to convert the date you want into a value in universal time and in seconds since January 1st, 1970.

For example, 973995954 translates to 11/12/00 2:25 AM. Use the following procedure to decipher the time stamp.

Go to Microsoft Excel.

In cell A1 type the number that is found in the last record for dateTimeOrigination.

In cell A2, paste the formula =A1/86400+DATE(1970,1,1).

Right-click on cell A2 and select format cells.

Under the Number tab select Time where the format is 3/14/98 130 PM.

The result is the actual time in readable format.

Use the following procedure to convert the day you want into the date.

Enter the date in cell B2 in the format MM/DD/YYYY 00:00 AM.

In Cell A enter the formula =(B2-DATE(1970,1,1))*86400.

Right-click on cell B1 and select format cells.

Under the Number tab, select Number.

This number is the universal time in seconds that is used in the formula.

[gshonting]

These fomulas don't work.

Here is an example-

My perl script output-

C:\perlprogs>perl tconvert.pl 1074732166

Wed Jan 21 19:42:46 2004

The excel formula result for 1074732166-

1/22/04 12:42 AM

Date and time from a CDR search matches the perl script.

Here is the perl script I use-

#! c:\perl\bin\perl

# convert single integer on command line (assumed to be standard

# unix "seconds since 1/1/70" time) to local date.

require "ctime.pl";

if ($#ARGV >= 0) {

$time = shift(@ARGV);

}

else {

die "Usage: num2date number\n";

}

print &ctime($time);

[jose.ibanez]

In ISO format:

use POSIX qw( strftime );

use File::stat;

$fecha = strftime('%Y-%m-%d %H:%M:%S', localtime( $PRE_fecha ) );

[lgutierrezg]

Thanks a lot, this was very helpful for me.

[PRABAHARAN C]

Example Epoch Time: 1406610231

Description:

Microsoft Excel =(A1 / 86400) + 25569 Format the result cell for date/time, the result will be in GMT time (A1 is the cell with the epoch number). For other time zones: =((A1 +/- time zone adjustment) / 86400) + 25569.


Formula:
 

=(A1/86400)+25569

It gives you GMT time. If you would like to convert MST (in our case) time please use the below formula in excel.

=A2-TIME(7,0,0)

[Geeth]

Dear Prabaharan,

I need to convert it to IST. Could you please tell me the formula for this

[Roger Kallberg]

Isn’t that just to subtract 5.5 hours? You have the formula in @PRABAHARAN C answer, you’ll just need to make the needed adjustments to it.