Formula for calculating regular and overtime hours in a timesheet

Question

Level 1

8 points

Formula for calculating regular and overtime hours in a timesheet

I have a timesheet that in columns H1 - H7 is the number of hours I work each of those days and I enter it manually, there are no cells before them that have time in or time out so I normally would just enter 8 or 10.

in column I1 - I7 I need it to add the amount of regular hours which would be 8. In column J1 - J7 I need it to add any hours over 8 that it finds in any of the H column cells. How can I do this? Somebody please layout the formula for me? Thank you for any help

[Re-Titled by Moderator]

Original Title: I need help creating a timesheet that will calculate the regular hours and overtime hours based by the number of hours I enter in a cell

Posted on Jul 31, 2025 5:37 PM

Reply

Answer 1

Top-ranking reply

6x6

Level 6

19,251 points

Aug 1, 2025 5:09 AM in response to uh1hhuey

In cell I1, use the formula:

MIN(H1,8)

This will guarantee that if you work less the 8 hours that day, you get the right number of hours.

In cell J1, use the formula:

MAX(H1-8,0)

So that if you work 8 or less hours, the overtime hours will be zero. If you work more than 8 hours, it will be the difference between the total hours (10 hours in your example) minus 8.

Reply

Formula for calculating regular and overtime hours in a timesheet

Similar questions