how to get accumulated time field not actual time

[Former Member]

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 · Accepted solution]

One possibility is to change the format of the datetime field in Crystal to dd:hh:mm:ss using Format Field Custom Style (but that would show days:hours:minutes:seconds...).

Or, you can build the string manually (basic syntax):

dim sec as number
dim hr as number
dim min as number

sec = DateDiff("s", CDateTime(1900,1,1,0,0,0), {timeField})
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")

The above formula could also be placed in the Display String formula for the field, if you replace with CurrentFieldValue.

HTH,

Carl

[Former Member · Accepted solution]

Carl-

Since this is more advanced than i am used to with formulas, I am not sure what else needs to be done with this formula. I tried this by adding my time field in the formula but not sure what else needs to be done to get it to work? The hour field can also be 2 characters/numbers, most look like this:

HH:MM:SS

sec = DateDiff("s", CDateTime(1900,1,1,0,0,0), csr_phones.duration)

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")

[Former Member · Accepted solution]

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

[Former Member]

Jennifer,

You would have actually been hard pressed to find a worse way to enter that type of data. Excel seems to hate storing numbers as text... which is actually what you want.

Start by creating a new "Time" column in Excel. Before entering any data, set the format for the entire column to Text. You should then be able to enter your values and have them remain the same as you entered them. If the data aligns to the left of the cell (like text) you are in good shape. If the data aligns to the right (like a number)... try again

From there... If you are importing it into a separate database, make sure the field type is also set to a string or text data type.

HTH,

Jason

[Former Member]

Ok I tried this and it did work in excel as a text field. It was a VARCHAR in the table (string) and the values are coming out as expected in crystal. My next question is, if you know, I now need to be able to get some totals though, so I have to be able to add up these fields and since they are strings, I can not add them. I see there are some functions for time but I am not sure how to use these conversions very well to convert them to time and if that would give me totals by call type?

[Former Member]

CTime() will take a string and convert it to a time value, but you will run into an issue if the hours is greater than 23.

You could always add a global variable to my formula above to accumulate the 'sec' value (immediately after its initial assignment of DateDiff()), then use the same conversion to make it a string.

Or, you could parse the string and convert it to seconds and accumulate...

HTH,

Carl