Convert UTC field into a 01/01/2003 12:00 AM (date time format)

[howdidigethere]

I think I've gotten to the point where my mind refuses to work anymore... I've got a date/time field (imported from Cisco's CallManager) that is in a UTC format (1009843200). I would like to create a formula that converts UTC format into a date and time format: 01/01/2002 12:00 AM. I have no problem doing this with a simple calculation in Excel but I know there's got to be a way of creating a similiar formula in Crystal. Any help would be VERY appreciated!!

 

[dgillz]

How does this number translate into the datetime that you mentioned? What would you excel formula be?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[howdidigethere]

In excel I've done it so much it's just become routine. I import the UTC values into the A column, in the B column I put in this formula: (assuming A1 is where my UTC value is)

=A1/86400+DATE(1970,1,1)

This has always made me a happy camper... I just divide the UTC value by the number of seconds in a day and add that value to the "value" that represents Jan. 1, 1970. After changing the display format to ensure the time pops up it always comes out looking good:

01/01/2002 12:00 AM

It's so frustrating...I go back and look at it and I KNOW it's got to be a simple fix but I'm tapped out. (just what I want to fixate on for the holiday weekend! NOT)

 

[dgillz]

Ok, now I got it. Try this:

Dateadd("d",int({YourField}/86400),Date(1970,1,1))

The above will return the date only, not the time. Will this work?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[howdidigethere]

Thats getting close but I do need the time to pull as well. If you have any suggestions I'd appreciate. At least this has jump started my brain! I'm gonna go mess around with the formula some more. Thanks!!

 

[dgillz]

int(Remainder({YourField}/86400)/3600) should give you the extra hours. Perform another dateadd() function with the first argument being "h" for hours, and then a third dateadd() function for the minutes and a fourth for the seconds.

It'll be a long formula but it should work.



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[howdidigethere]

Before I read your last post I went in and created a timevalue function:

@mytime
TimeValue({UTC}/86400)

This gave me the time. I then used your suggested DateAdd formula to come up with the date:

@mydate
Dateadd("d",int({UTC}/86400),Date(1970,1,1))

Then I created a concatenated function:

@mydatetime
@Mydate &" "&@myTime

Cosmetically it looks good. I can always use selection options if I need to narrow down my information, I just always second guess my own solutions.