Custom CDR Reports - Date/Time Formula

jkjackson · ‎03-28-2007

I am trying to do some custom reporting on the CDR database with Crystal Reports 11. The problem i am having is that any time field is a long number that needs to be calculated in some way to derive the proper date/time from it. Does any one know what that formula should be?

Thanks,

JJ

jbarcena · ‎03-28-2007

You can use the following link to change the time:

http://www.esqsoft.com/javascript_examples/date-to-epoch.htm

But if you are looking for the fomula it is:

=A1/86400+DATE(1970,1,1) which can be used on Excel.

In cell A1 type the number that is found in the last record for DateTimeOrigination. Then in A2 paste the following formula. =A1/86400+DATE(1970,1,1). There are 86400 seconds in a day. Then right click on cell A2 and select format cells. Under the Number tab select "Time" where type is "3/14/01 1:30 PM". The result will be the actual time in readable format of the last call that was logged into the CDR.

HTH

//Jorge

jkjackson · ‎03-28-2007

Thanks Jorge,

I am going to try to convert this to Crystal. You wouldn't happen to know the crystal formula would you?

jbarcena · ‎03-28-2007

No, sorry about that. But I hope that the previous post was useful.