OMG sum hours/minutes on report > 24hrs!

[mcamp]

ok so im gunna seriously scream here. I cannot figure out how to calculate the sum of hours and minutes in this report.

everything is good until the sum is greater than 23:59:00 (seconds never used)

(this is a date/time field hh:nn:ss)

I have tried multiple formulas trying to get around it but none of them are working.

sum(Left([totalhours],2)*60 + right([totalhours],2)

ive tried to wrap my head around converting the total time to a number but its like, if the hours are greater than 24 the correct hours arent even calculated, so how can it accurately convert it to a number

also, in the detail footer of the report its rare that the hours would be over 24, so it would need to be an Iif situation, where as the report footer will always be over 24 hours.


Print screen, as youll see the total hours say 17:00, but the corret total should be 41:30


anyone have any ideas, im stressin on this one

http://lordkazeoneink.com/thc/ps1.jpg

 

[boblarson]

You should be able to use:

Sum([totalhours])*24

 

[mcamp]

its in hh:nn:ss format though? only the first two digits would need to be *24?

that expression results in 00:00:00

any other ideas?? im desperate

 

[boblarson]

its in hh:nn:ss format though? only the first two digits would need to be *24?

that expression results in 00:00:00

any other ideas?? im desperate

Is totalhours a field or a calculation?

 

[mcamp]

TotalHours is a field. i can create a sample db with the data if needed.

i just cannot figure out why this is so difficult lol

 

[mcamp]

***DB Attached***

attached is print screen + db.

as you can see it calculates the sum correctly if the total is less than 24 hours, but if you look at child 00291, it calculates incorrectly. and of course the same for the grand total being that it is > 24

please help if u can !!

http://lordkazeoneink.com/thc/ps2.jpg

http://lordkazeoneink.com/thc/CCC_Frontend.mdb

 

[mcamp]

ope got it! your words triggered it! needed to be its own calculation.

leave the txtSumHours:=Sum([TotalHours]) hidden, then have a seperate textbox calculating it *24


Text83: =[txtSumHours]*24 - Visible

txtSumHours: =Sum([TotalHours]) - not visible

http://lordkazeoneink.com/thc/ps3.jpg
http://lordkazeoneink.com/thc/ps4.jpg

thanks boblarson!