Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Sum of Cells and Remainders

0

I have a timesheet that I would like to configure the overtime. 
The total hours worked in a day is 7.25 but any overtime is at regular time up to 8 hours for the day and the remainder is premium overtime. 
Is there a way to have the sum of the overtime fields plus the total work day needs to be less than or equal to 8, so the regular overtime hours that make the day less than or equal to 8 go into one cell, and the remainder of the overtime, any overtime that goes over 8 hours, into another cell?

Thanks,
Ren

excel
posted by: RWSM (rep: 10) on Mon Jun 26, 2023 at 2:13 pm
Answer
Discuss

Answers

0
Selected Answer

Hi Ren

I think simple If statements will do that. The If function has three arguments...  if the formula is

=If(a,b,c)
then a is the test, b is the value shown in the cell if that test is True and c is the value if it's False (not True!).

In the attached revised file, please put the Total Hours worked for that day in cell F11 (shaded yellow), say 9.25.

The formula in cell D11 (Regular hours) is:

=IF(F11>=8,8,F11)
so F11 but up to a maximum of 8.

Cell E9 calculates the premium overtime (beyond 8 hours) as:

=IF(F11>8,F11-8,"")
so it's nothing unless F11 is over 8- in which case it becomes the Total Hours (F11) less 8.

Hope this is what you wanted, If so, you just need to copy each formula to other cells (and you might want to protect those calculation cells). Also please remember to mark this Answer as Selected. 

posted by: John_Ru (rep: 6142) on Mon Jun 26, 2023 at 4:53 pm
Discuss

Discussion

Glad that worked for you, Ren. Thanks for selecting my Answer 
John_Ru (rep: 6142) Jun 26, '23 at 6:30 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login