[Solved] Overtime formula

[Fred]

The formula in L works correct and up to a point, except when the hours change between Shift Length D8 to 10:00, and in Hours Worked G8 shows 11:00, but in Scheduled overtime L8 it shows 01:12 instead of 02:12

=MAX(0, IF(D3>=TIME(10,0,0), IF(D3>H1-TIME(8,48,0)>=TIME(1,0,0),G3-TIME(8,48,0),0), IF(G3>H1-TIME(8,48,0)>=TIME(1,0,0),G3-TIME(8,48,0),0)))

There is times I work longer than my Shift length

I work different Shift length 08:48, 10:00, 11:00 and 12:00.

Overtime is after 8:48

The formula I try to get in Column L: is about which hours is more between Shift Length D and Hours Worked G in Scheduled overtime L

If Shift length is more than Hours worked, Shift length D is 12:00 and Hours worked G is 09:00, If G is less than D = Scheduled overtime L must show 03:12

If Shift length is less than Hours worked, it must show Shift length D is 10:00 and Hours worked G is 11:00. Scheduled overtime L

must show 02:12

Shift length D20 is 10:00 and Hours worked G20 is 13:00. If G is more than D = Scheduled overtime L20

must show 04:12 and not 01:12

Shift length D22 is 12:00 and Hours worked G22 is 13:00. If G is more than D = Scheduled overtime L22

must show 4:12 and not 03:12

 

[Yousuf Shovon]

The formula in L works correct and up to a point, except when the hours change between Shift Length D8 to 10:00, and in Hours Worked G8 shows 11:00, but in Scheduled overtime L8 it shows 01:12 instead of 02:12

=MAX(0, IF(D3>=TIME(10,0,0), IF(D3>H1-TIME(8,48,0)>=TIME(1,0,0),G3-TIME(8,48,0),0), IF(G3>H1-TIME(8,48,0)>=TIME(1,0,0),G3-TIME(8,48,0),0)))

Dear Fred,

Welcome to ExcelDemy Forum! Thank you for your post. I understand your existing formula returns an incorrect output with the changing shifts, for instance in cell L8. Today, I will provide a modified formula that will meet your existing criteria and fix the issues given in your Excel file.

After going through your file, I understand you are considering the value that is greater in the IF calculations based on the values in column H. So, why don't we just compare the formula values with column H values? So, the formula you should use in L3 is:

=IF(H3>MAX(0, IF(D3>=TIME(10,0,0), IF(D3>$H$1-TIME(8,48,0)>=TIME(1,0,0),D3-TIME(8,48,0),0), IF(G3>$H$1-TIME(8,48,0)>=TIME(1,0,0),G3-TIME(8,48,0),0))), H3, MAX(0, IF(D3>=TIME(10,0,0), IF(D3>$H$1-TIME(8,48,0)>=TIME(1,0,0),D3-TIME(8,48,0),0), IF(G3>$H$1-TIME(8,48,0)>=TIME(1,0,0),G3-TIME(8,48,0),0))))

See the screenshot for an example.



As a result, you obtain the desired scheduled overtime in column N.

I have attached the solved workbook here. Please, let me know whether it works for you or not.

You can also learn more about the Nested IF function with examples in the below article.

How to Use Nested IF Function in Excel​

Regards,
Yousuf Shovon

 

[Fred]

Thank you. Working perfect.

 

[Yousuf Shovon]

Thank you. Working perfect.

Hello Fred,
I'm glad to hear that our solution worked perfectly for you!

If you ever have more questions about Excel or need help in the future, you can always ask our ExcelDemy community. We're here to assist you with anything related to Excel.

You can also find helpful Excel tips, tutorials, and discussions on our forum. Please look at our overtime formula, calculator, or template articles for more detailed info.

Excel Timesheet Formula with Lunch Break and Overtime

Excel Formula for Overtime over 8 Hours

How to Calculate Hours Worked and Overtime Using Excel Formula​

Thank you for being a part of ExcelDemy. We're excited to help you with any future Excel challenges you may face!