Time Sheet Calculating Daily Hours Up To 40 Hours

amelchor

New Member
Joined
Apr 23, 2018
Messages
3
Hi I have been trying to figure this out for weeks. I need my total daily hours in my regular hours column to only calculate up to 40 hours then anything over 40 hours to total in in the overtime cell. Right now it just totals the total work hours worked for a day in the regular hours, but I only want it to do up to 40 hours that way it only reflects regular hours then anything over that totaled in the overtime hours cell.


BCDEFGHI
11DayDateInOutInOutRegularOvertime
12Monday
04/23/188:00 AM12:00 PM1:00 PM4:30 PM7.50
13Tuesday04/24/188:00 AM12:00 PM1:00 PM5:30 PM8.50
14Wednesday04/25/188:00 AM12:00 PM1:00 PM5:00 PM8.00
15Thursday04/26/188:00 AM12:00 PM1:00 PM5:00 PM8.00
16Friday04/27/188:00 AM12:00 PM1:00 PM5:15 PM8.25
17Saturday04/28/18
18Sunday04/29/18
19Total40.000.25

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,202
In cell H19
=MIN(SUM(H12:H18),40)

In cell I19
=MAX(0,SUM(H12:H18)-40)
 
Last edited:

amelchor

New Member
Joined
Apr 23, 2018
Messages
3
Thank you for your response Tom! What I am looking for is I want my daily regular hours in each cell to only calculate until all of those cells hit 40 hours. By the time the regular hours gets to H16 the 0.25 over 8 hours is considered overtime, so I have the 0.25 populating in the overtime cell, but that 0.25 still reflects in my daily regular hours. I would want that H16 to state 8.00 hours since the week exceeded 40.00 hours.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,202
It would have helped if your graphic showed that objective instead of all cells in column I except cell I19 being blank. Your question is solve-able, but please clarify 2 points:

(1)
That the workweek begins on Monday and if someone in a given week does not work on Monday, Tuesday, Wednesday, Thursday, or Friday, but in that calendar week they work 15 hours on Saturday and 15 hours on Sunday, and then the following calendar week (which is the next day) they work 15 hours on Monday, then at that moment there is no overtime involved.

(2)
That there is no consideration for daily overtime of more than (for example) 8 hours of work in a single day.
 

amelchor

New Member
Joined
Apr 23, 2018
Messages
3
The work week starts is from Monday - Sunday. There is only overtime once someone has worked over 40 hours for the work week. Even if someone works over 8 hours a day it is not considered overtime. I would like for the regular daily hours to only populate up to 40 hours then after that anything over 40 hours populate in I19
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,202
Just to clarify further because something seems odd; your reply will determine the necessary formula(s), you wrote:
"...anything over 40 hours populate in I19"
The second formula in my first reply does that already.

Your original image shows 8.25 in cell H16 but you show nothing in cells I12 to I18 (the Overtime column).
Is that really what you want -- nothing in cells I12:I18 and only a conditional total of overtime beyond 40 if the sum in range H12:H18 is greater than 40?
If so, that would mean you really want 8.25 in cell H16 and not 8.00 in H16 and .25 in cell I16?
It struck me as odd that you show, and seem to reinforce by saying, you only want the total overtime cell (I19) to be possibly occupied, and all the worked time to be in H12:H18.

Either way is fine by me, it's your worksheet, just making sure I get what your final result shall be, visually as well as mathematically.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,486
Messages
5,468,885
Members
406,618
Latest member
SAMEERS

This Week's Hot Topics

Top