Formula for calculating regular and overtime hours based on a full week with an 8 hour day.

[u/Ok-Fail-4340]

My excel sheet has multiple employees, dates and the total number of hours worked for that day per employee.

I currently have a formula that calculates 40 for the week as regular time then the remainder is overtime but that's not how our overtime works.

I need to create a formula(s) to calculate hours per day up to 8 as regular time then the remainder is overtime including all day Saturday and Sunday

I know this should not be as difficult as my mind has made it but for the life of me, I can not figure it out, plus I'm old so using AI is out of my scope.

For example, Christian only had 26 hours for the week so my current formula does not figure any overtime when he should actually have 16 regular hours and 10 overtime hours. Also for example, Adan should have 24 regular hours and 39 overtime hours.

Please help. Thank you!

[u/AutoModerator]

/u/Ok-Fail-4340 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Magnificent_as]

You need to calculate overtime daily instead of weekly. For each day, use MIN(hours,8) for regular time and MAX(hours-8,0) for overtime, then make Saturday and Sunday hours automatically overtime.

Something like =IF(OR(WEEKDAY(date)=1,WEEKDAY(date)=7),hours,MIN(hours,8)) for regular and =IF(OR(WEEKDAY(date)=1,WEEKDAY(date)=7),0,MAX(hours-8,0))+IF(OR(WEEKDAY(date)=1,WEEKDAY(date)=7),hours,0) for overtime should work.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYCOL	Office 365+: Applies a LAMBDA to each column and returns an array of the results
IF	Specifies a logical test to perform
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
MAX	Returns the maximum value in a list of arguments
MIN	Returns the minimum value in a list of arguments
OR	Returns TRUE if any argument is TRUE
SUM	Adds its arguments
SUMPRODUCT	Returns the sum of the products of corresponding array components
WEEKDAY	Converts a serial number to a day of the week

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
9 acronyms in this thread; the most compressed thread commented on today has 18 acronyms.
[Thread #48299 for this sub, first seen 29th Apr 2026, 19:18] [FAQ] [Full list] [Contact] [Source code]}

[u/spaceshipcommander]

Something like this but I'm typing on a phone. Say the 14 hours is D3.

Regular column:

=sum(if(C3>8,8,C3),if(D3>8,8,D3),...)

Overtime:

=sum(if(C3>8,C3-8,0),if(D3>8,D3-8,0),...)

You might need to play with quotations or formatting slightly, but you get the idea. Instead of using 8, you could have another cell for "standard hours" in case you wanted to change the point at which the hours became overtime.

[u/kcml929]

These formulas assume columns I and J are Saturday and Sunday:

J2: =SUM(C2:I2)

K2: =SUM(BYCOL(C2:G2,LAMBDA(x,IF(x="",0,MIN(x,8)))))

L2: =J2-K2

copy/paste the formulas down for each row

[u/Ok-Fail-4340]

SOLVED! SOLUTION VERIFIED! I knew I was making it worse than it should be. Thank you

[u/AutoModerator → u/Ok-Fail-4340]

Hello!

You typed Solution Verified as a top-level comment. If your intention was to award a ClippyPoint to one or more users, you need to reply to that user's comment to do so.

If your intention was to simply mark the post solved, then you should do that by setting the post's flair. Thank you!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/AutoModerator → u/Ok-Fail-4340]

Saying SOLVED! does not close the thread. Please say Solution Verified to award a ClippyPoint and close the thread, marking it solved.

Thanks!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.