# how to get accumulated time field not actual time

Hello-

I have a field in a table as "time" however the time data is actually accumulated time over a period of a month not an actual time. So for example. We re reporting on Inbound and Outbound phone call duration per employee so we have:

Inbound Duration: 71:45:52 (this is accumlated time, meaning 71 hrs, 45 min, 52 seconds) but when it is displayed in excel or crystal it shows as:

1/2/1900 11:45:52 PM in excel. I

f you look at the format in excel it should be correct set in the number tab like: custom [h]:mm:ss

In the actual cell itself it shows the 71:45:52 but in the display that is where it shows the other 1/2/1900 11:45:52 PM

11:45:52 in crystal since it's just a time field in the table

Does anyone know how to correct this on either excel side or crystal? We have our data in excel which we import into a table to use crystal reports on.

thanks

##### Former Member replied

My formulas were assuming the data was coming in as a datetime value, as it was in your original post. (Jason was typing his reply while I was typing mine...) So, the formula with accumulation is (BASIC SYNTAX - make sure the formula editor has this selected, not Crystal Syntax):

global secAccum as number dim sec as number dim min as number dim hr as number sec = DateDiff("s", CDateTime(1900,1,1,0,0,0), {csr_phones.duration}) secAccum = secAccum + sec min = int(sec / 60) hr = int(min / 60) sec = sec - (min * 60) min = min - (hr * 60) formula = cstr(hr, "0") + ":" + cstr(min, "00") + ":" + cstr(sec,"00")

And to show the accumulated seconds:

WhilePrintingRecords global secAccum as number dim sec as number dim min as number dim hr as number sec = secAccum min = int(sec / 60) hr = int(min / 60) sec = sec - (min * 60) min = min - (hr * 60) formula = cstr(hr, "0") + ":" + cstr(min, "00") + ":" + cstr(sec,"00")

HTH,

Carl