Hours display format 1

[crumpm]

I have a report summarising time entries, but I need to display the total number of hours as

146:06 (for 146 hours 6 minutes)

I keep getting the error message
"Hour must be between 0 and 23"

Is there any way to get around this, other than decimalising the total?

Thanks

 

[briangriffin]

How are you summarizing the time? The sum will have to be displayed as text or a number, does it matter which?

 

[crumpm]

It needs to be displayed as a number. (It does matter as it may then be exported to Excel, by users who aren't familiar with changing formats)

28-Jun-10 LI LI Letter In 749 250.00 0:06 25.00 Unbilled
28-Jun-10 TE TE Telephone 749 250.00 0:06 25.00 Unbilled
29-Jun-10 TE TE Telephone 749 250.00 0:06 25.00 Unbilled
29-Jun-10 LO LO Letter Out 749 250.00 0:24 100.00 Unbilled

749 94.83% 6:30 1,375.00 96.53%
881 5.17% 0:18 49.50 3.47%
6:48 1,424.50

 

[briangriffin]

Sorry, I'm not clear where your totals are coming from - for instance, the 6:30 and the 1375.00. Please explain what each of the columns represents.

 

[crumpm]

Sorry

I only put in a few of the data lines, so those shown don't add up to the total.
The summary at the bottom isn't meant to line up, as it's shown in the report footer, brought in by a subreport.
BTW - I'm using CR XI

Date Activity FE Rate Hours Value Status
28-Jun-10 LI LI Letter In 749 250.00 0:06 25.00 Unbilled
28-Jun-10 TE TE Telephone 749 250.00 0:06 25.00 Unbilled
29-Jun-10 TE TE Telephone 749 250.00 0:06 25.00 Unbilled
29-Jun-10 LO LO Letter Out 749 250.00 0:24 100.00 Unbilled

Thanks

 

[IanWaterman]

What is the data type for hours

eg 0:06

Not sure how your original question relates to the data you have shown.

When are you getting the error you describe?

Ian

 

[crumpm]

The original data is a number format, total number of minutes e.g. 252 which displays in the data lines as 4:12
so each line is fine, (it is rare but not impossible to record a single data line greater then 24 hours).

The problem arises in my totals.

Fred 4:06
Jim 6:30
Bob: 12:06
Mick 6:12

Total 28:54

but I get the "Hour must be in 0 to 23" message at that point, when I try to format the total to display as hours.

 

[briangriffin]

Give this a try:

Details formula:

whileprintingrecords;
numbervar v_seconds;
numbervar v_minutes;

v_seconds := v_seconds + tonumber(right({Sheet1_.Time},2));

if instr({Sheet1_.Time},":") = 1 then v_minutes
else
v_minutes := v_minutes + tonumber(left({Sheet1_.Time},instr({Sheet1_.Time},":") - 1));



Summary formula:

whileprintingrecords;
numbervar v_seconds;
numbervar v_minutes;
numbervar v_hours;

numbervar v_minutes := v_minutes + floor(v_seconds/60);
numbervar v_hours := floor(v_minutes/60);

tonumber(totext(v_hours,"#",0) + "." + totext(v_minutes mod 60,"#",0))

In CR you can format the number to display a colon instead of the decimal to 'fake' a time, but once it's in Excel I don't think you'll be able to do that, and you definitely won't be able to sum the values accurately.

 

[briangriffin]

Just noticed your most recent post, sorry - and I think you mean the data is stored as seconds (252 = 4:12).

So the conversion from text is not needed, basically just set v_seconds := your database field and I think the rest of the formulas will work correctly.