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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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)
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top