[Answered] Calculation of Holiday hours

[Maps]

Good day.
I have a table that adds total hours worked by employees. I however wish to have a formula that will tell me the number of hours worked on holidays. I have a list of holidays for the year and I wish to create a formula that count the number of hours in one particular month e.g. August 2023. if it is a different month, the formula must be able to holidays for that month.
The hours are calculated as follows:
1. The shift starts at 07.00 and ends at 16.00 which is 9 hours
2. The next shift stars at 16.00 and end at 07.00 the following day.

You will note that the day ends at 00.00. Therefore, if an employee works in the evening shift of a holiday he starts at 16.00 and his holiday hours end at 00.00 and that is equivalent to 8 hours. and the following hours are treated as normal.
If the holiday is on the following day his holiday hours start at 00.00 and end at 07.00 and that is equivalent to 7 hours.
On the attached spreadsheet, the schedule shows that:
Employee 1 works a total of 240 hours and 33 hours are for holidays.
Employee 2 works a total of 260.52 hours and 24 hours are holidays.
Can you please help.

 

[Lutfor Rahman Shimanto]

Good day.
I have a table that adds total hours worked by employees. I however wish to have a formula that will tell me the number of hours worked on holidays. I have a list of holidays for the year and I wish to create a formula that count the number of hours in one particular month e.g. August 2023. if it is a different month, the formula must be able to holidays for that month.
The hours are calculated as follows:
1. The shift starts at 07.00 and ends at 16.00 which is 9 hours
2. The next shift stars at 16.00 and end at 07.00 the following day.

You will note that the day ends at 00.00. Therefore, if an employee works in the evening shift of a holiday he starts at 16.00 and his holiday hours end at 00.00 and that is equivalent to 8 hours. and the following hours are treated as normal.
If the holiday is on the following day his holiday hours start at 00.00 and end at 07.00 and that is equivalent to 7 hours.
On the attached spreadsheet, the schedule shows that:
Employee 1 works a total of 240 hours and 33 hours are for holidays.
Employee 2 works a total of 260.52 hours and 24 hours are holidays.
Can you please help.

Hello Maps

It is great to see you again. Thank you for posting your queries with such clarity. I went through your attached workbook and analyzed your requirements. What I have found is that it is technically impossible to calculate the Holiday Working Hours only using an Excel Formula.

In this case, we have to develop an Excel VBA User-defined function.
My previous experience working with you reminds me that you do not work with Excel VBA.

However, I have solved the described issues with three Helper Columns. Hopefully, the idea will achieve your goal.

Select cell K4 >> insert the following formula >> hit Enter.

=SUMIFS(D4:D34,OFFSET(A4:A34,1,0),I4:I18)

​

Now, choose cell L4 >> apply the below formula >> hit Enter.

=IF(SUMIFS(D4:D34,A4:A34,I4:I18)<15,SUMIFS(D4:D34,A4:A34,I4:I18),SUM(8))

​

Lastly, choose cell M4 >> apply the following formula >> drag the Fill Handle icon to M18.

=IF(AND(L4=0,K4<>0),7,L4)

​

Now, keep the K, L and M columns hidden.

​

Finally, select the D43 cell >> apply the following formula >> hit Enter.

=SUM(M4:M18)

​

Apply the same procedures for Employee 2. Next, choose the G43 cell >> apply the below formula >> hit Enter.

=SUM(R4:R18)

​

I am attaching the solution workbook to help you understand better. Good luck!

Regards
Lutfor Rahman Shimanto

 

[Maps]

Hello Maps

It is great to see you again. Thank you for posting your queries with such clarity. I went through your attached workbook and analyzed your requirements. What I have found is that it is technically impossible to calculate the Holiday Working Hours only using an Excel Formula.

In this case, we have to develop an Excel VBA User-defined function.
My previous experience working with you reminds me that you do not work with Excel VBA.

However, I have solved the described issues with three Helper Columns. Hopefully, the idea will achieve your goal.

Select cell K4 >> insert the following formula >> hit Enter.

=SUMIFS(D4:D34,OFFSET(A4:A34,1,0),I4:I18)

View attachment 737
​

Now, choose cell L4 >> apply the below formula >> hit Enter.

=IF(SUMIFS(D4:D34,A4:A34,I4:I18)<15,SUMIFS(D4:D34,A4:A34,I4:I18),SUM(8))

View attachment 738
​

Lastly, choose cell M4 >> apply the following formula >> drag the Fill Handle icon to M18.

View attachment 739
​

Now, keep the K, L and M columns hidden.

View attachment 740
​

Finally, select the D43 cell >> apply the following formula >> hit Enter.

View attachment 741
​

Apply the same procedures for Employee 2. Next, choose the G43 cell >> apply the below formula >> hit Enter.

View attachment 742​

I am attaching the solution workbook to help you understand better. Good luck!

Regards
Lutfor Rahman Shimanto

Thank you for your quick response. Your formula works fine but it misses the minutes when the employee's holiday has minutes outside the hours worked. Like employees who reported for duty on 3 August in the evening shift had to be paid holiday hours from 00.00 to 07.15 on the 4th which is = 7hours 15 minutes.
How do we accommodate that?

 

[Maps]

Further to the above, sometimes the employees do work for less than 7 hours after 00.00. Like instead of knocking off at 07.00 the employees not off at 06.00 or 07.30.
How can we accommodate those variations?

 

[Lutfor Rahman Shimanto]

Thank you for your quick response. Your formula works fine but it misses the minutes when the employee's holiday has minutes outside the hours worked. Like employees who reported for duty on 3 August in the evening shift had to be paid holiday hours from 00.00 to 07.15 on the 4th which is = 7hours 15 minutes.
How do we accommodate that?

Hello Maps

Thanks for explaining your problem with such clarity. You are right about missing minute values when using the previous formula.

You have shown that employees who reported for duty on 3 August in the evening shift had to be paid holiday hours from 00.00 to 07.15 on the 4th, which is 7 hours 15 minutes. Besides, the employees do work for less than 7 hours after 00.00. Instead of knocking off at 07.00, the employees are not off at 06.00 or 07.30.

To overcome the situation, I have used another two helper columns.

Select cell S4 >> insert the following formula >> hit Enter.

=IF((IF((F4:F34-E4:E34)<0,(24+24*(F4:F34-E4:E34)),VALUE(F4:F34-E4:E34)*24)-8)<0,"",(IF((F4:F34-E4:E34)<0,(24+24*(F4:F34-E4:E34)),VALUE(F4:F34-E4:E34)*24)-8))

​

Select cell T4 >> input the following formula >> hit Enter.

=TEXT((IF(SUMIFS(G4:G34,A4:A34,I4:I18)<15,SUMIFS(G4:G34,A4:A34,I4:I18),SUMIFS(OFFSET(S4:S34,-1,0),A4:A34,I4:I18)))/24,"hh:mm")

​

Lastly, choose G43 >> insert the following equation >> hit Enter.

=(SUM(R4:R18)+24*SUM(VALUE(T4:T18)))

​

I am attaching the solution workbook this time as well. Good luck!

Regards
Lutfor Rahman Shimamnto

 

[Maps]

Good morning Lutfor.
Thank you for your assistance to my query. It seems that this could be a complex exercise if you have to apply this formula to about 100 employees every month. If there is no simpler formula to use, then I will have to calculate the holidays manually.
Thanks once again for your diligence in helping me out.

 

[Lutfor Rahman Shimanto]

Good morning Lutfor.
Thank you for your assistance to my query. It seems that this could be a complex exercise if you have to apply this formula to about 100 employees every month. If there is no simpler formula to use, then I will have to calculate the holidays manually.
Thanks once again for your diligence in helping me out.

Dear Maps

You're very welcome! I appreciate your kind words, and I'm glad I tried to assist you with your query. Calculating holidays for a large number of employees can indeed be a complex task, especially if there isn't a simpler formula readily available.

If anything even comes to my mind, I will definitely share the idea in this thread.

Best Regards
Lutfor Rahman Shimanto