Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question