Sum of DateTime field

[ericb1]

I've looked through these posts and can't quite find the formula I need.

I have a simple column of time (formatted in DateTime format) that's in the time format: 0:00:00 and since this is just a duration of a phone call, I just want to be able to add this column and total it.

But Crystal (v.10) won't let me just SUM this field, so I assume I need to use some type of formula, but I don't know where to begin since I don't really want to compare these times with a DateDiff or anything.

Any help is gretly appreciated, thanks!

 

[beanbrain]

You can right click on the date field and use a custom format on it.

Right click on the date, select the "Customize" button. Click on the "Date" tab and set the month, day, and year pulldowns to "None."

Click the "Time" tab and set the 12 or 24 hour options and set the appropriate formats for hours, minutes, and seconds from the associated pulldowns. Milli-seconds are not supported.

This field type might serve you better as Number representing seconds or minutes rather than date. You could then do time appropriate math on the number to show hours, minutes, etc.

Finally, you can do math on date fields in Crystal 10, but you need two or more dates (or CurrentDate) to do this. e.g. The results of math on date fields returns number.

Example:

CurrentDate - {table.startDate}

would return the todays date less the value of startDate in the number of days.

 

[synapsevampire]

Convert the time to seconds in a formula, and then sum it, then you can display it as HH:MM:SS using my FAQ:

faq767-3543

You can convert the time to seconds using the following formula:

(hour(currentdatetime)*3600)+((minute(currentdatetime)*60))+(second(currentdatetime))

Replacing the currentdatetime with your datetime field.

-k

 

[ericb1]

Yes, thanks. I just found the same article on the BO website:

http://support.businessobjects.com/library/kbase/articles/c2008378.asp

I was just thinking there would be an easier way to do this since the field is the same type, but after thinking about it, I realized there wouldn't be, so I did it the long way.

Thanks again!