google sheets
I'm using Google Sheets to keep track of time with the bimonthly timesheet template. What would be useful is if the spreadsheet could start adding hours to another column when the total hours worked per day exceeds 10 hours.
What would be the best way to do this?
It may be a little late, but here is how I do it:
=HOUR(B2-A2)+(MINUTE(B2-A2)/60)
Basically, being B2 the end time and A2 the start time, you subtract B2-A2 and get the hour from the subtraction. Then you do the same and get the amount of minutes. After you have both, you sum the amount of hours with the amount of minutes divided by 60 (to get it in hours).
Don't forget to use the 24-hour format (i.e. 10:00 or 22:00).
Adding a column for date and calculating the C values with:
=if(istext(D1),C2,D1+C2)
in D2 copied down a chart might be plotted of this Data Sheet1!A1:A4, Sheet1!D1:D4 with Use row 1 as headers:
Best Answer
You can do this by modifying the current formulas and adding one of your own.
Based on the template you mentioned, you need to amend the formula for the Total hours per day: column to:
=IF(24*(C2-B2) > 10, 10, 24*(C2-B2))Then in Column E add a new formula:
=IF(D2=10, ABS(10.00 - (24*(C2-B2))),0)Then the Formula for Total Hours For This Pay Period needs to be amended to include any hours over the 10 hour threshold.
=SUM(D2:D16)+SUM(E2:E16)This will give you an output similar to the this: