calculating hours for time sheets

[K0ZMIK]

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!

 

[Aladin Akyurek]

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]

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]

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.

 

[Aladin Akyurek]

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]

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]

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]

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

 

[Aladin Akyurek]

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.