calculating hours for time sheets

K0ZMIK

New Member
Joined
Dec 23, 2008
Messages
4
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!
 

Some videos you may like

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)

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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
Joined
Dec 23, 2008
Messages
4
Thank you so much!!!! :biggrin:
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
Joined
Dec 23, 2008
Messages
7
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
Thank you so much!!!! :biggrin:
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
Joined
Dec 23, 2008
Messages
4
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
Joined
Dec 23, 2008
Messages
4
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,978
Messages
5,447,670
Members
405,462
Latest member
est1989

This Week's Hot Topics

Top