# calculating hours for time sheets

#### K0ZMIK

##### New Member
Hi, my apoligies from the start if this question has been asked a million times before.. I did search the archieves for quite some time.. but no result, althought the format [h]:mm is going to be helpful. I am trying to put together a time sheet.. first adding hours per day, then totaling.. I am entering times in the 24hour clock format, so when a shift starts at 18:00 and ends at 06:00 my result is negative.. at one point I was using an if.. but if I did that then I was unable to sum the weeks total.. actually I have not ever had the weekly total work no matter what I try. Any help would be very much appreciatted. Thank you in advance!

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

##### MrExcel MVP
Hi, my apoligies from the start if this question has been asked a million times before.. I did search the archieves for quite some time.. but no result, althought the format [h]:mm is going to be helpful. I am trying to put together a time sheet.. first adding hours per day, then totaling.. I am entering times in the 24hour clock format, so when a shift starts at 18:00 and ends at 06:00 my result is negative.. at one point I was using an if.. but if I did that then I was unable to sum the weeks total.. actually I have not ever had the weekly total work no matter what I try. Any help would be very much appreciatted. Thank you in advance!
Custom format the formula cell as [h]:mm...

=B2-A2+(B2 < A2)

#### K0ZMIK

##### New Member
Thank you so much!!!! One more request, if you have a spare moment, would you mind explaining the last part of that formula to me.. how does that translate...

#### polyptoton

##### New Member
I found that my weekly total still did not calculate properly until I multiplied the total by 24. . . ie "=SUM(A3:A10)*24" and used general format for that cell.

##### MrExcel MVP
Thank you so much!!!! One more request, if you have a spare moment, would you mind explaining the last part of that formula to me.. how does that translate...
If

(B2 < A2)

is TRUE (which is 1 when coerced into a number), 24 hours are added to B2...

#### K0ZMIK

##### New Member
I found that my weekly total still did not calculate properly until I multiplied the total by 24. . . ie "=SUM(A3:A10)*24" and used general format for that cell.
I had tried that, with no luck, the problem was with the negative totals.. but anyway the previous formula worked like a charm.. I dont understand how the > makes a neg into a pos.. but it did. But Thanks!

#### K0ZMIK

##### New Member
If

(B2 < A2)

is TRUE (which is 1 when coerced into a number), 24 hours are added to B2...
I understand what you have translated.. but dont see it in the formula, is it just a default that when something is true.. it adds 24? I being a novice LOL assumed that to use a if.. I had to actually type out a formulat with the word if, and then the criteria and the action based on the true/false result. but I see nothing in this.. yet it works! Sorry to be a bug, but if I learn as I go, less bugging in the future LOL

#### HippoCampus

##### Board Regular
Morning

Whenever I (often) have trouble with time calculations I always pop over the Chip pearsons page, here, http://www.cpearson.com/excel/overtime.htm, there are several explainations and examples.

Hippo

##### MrExcel MVP
I understand what you have translated.. but dont see it in the formula, is it just a default that when something is true.. it adds 24? I being a novice LOL assumed that to use a if.. I had to actually type out a formulat with the word if, and then the criteria and the action based on the true/false result. but I see nothing in this.. yet it works! Sorry to be a bug, but if I learn as I go, less bugging in the future LOL
3+TRUE

is 4, because + in between forces TRUE to its numeric value in Excel, which is 1.

Enter 1, say, in X2. Select X2, custom format as [h]:mm

This has to do how time is represented in Excel, which is described in its Help.