Subtraction of H:MM

**sbossio** · 06-21-2011, 05:09 PM

I am trying to set up a timesheet that will automatically add up the hours worked for each day (stop time - start time), then sum up the week and convert it to a decimal (need 4.5 hours vs. 4:30). I have the conversion from H:MM to a decimal, but for some reason the hours are not summing up properly once they go over 24 for a given week. If they are under 24, then the formulas work fine. What am I doing incorrectly?

**Paul** · 06-21-2011, 05:23 PM

Hi Sbossio, welcome to the forum.

If you're trying to total hours greater than 24, format the cell(s) as:

[h]:mm instead of h:mm

Hope that helps, I can't confirm since the spreadsheet you uploaded is protected with a password.

**shg** · 06-21-2011, 05:32 PM

Welcome to the forum.

Please don't post workbooks with protected worksheets.

Change the formula in Q13 to

=24*SUMPRODUCT(-1^(COLUMN(C13:P13)- COLUMN(C13) + 1), C13:P13)

**daddylonglegs** · 06-21-2011, 06:20 PM

Another alternative....

=SUM(SUMIF(C$12:P$12,{"Stop","Start"},C13:P13)*{24,-24})

...or this one...

=(SUM(C13:P13)-2*SUMIF(C$12:P$12,"Start",C13:P13))*24

**sbossio** · 06-21-2011, 06:44 PM

That worked like a charm. Thank you all for your help.

Sorry about the protected worksheet. I had two copies saved and uploaded the wrong one. The lesson as always, I'm an idiot. My apologies and thanks again.