# Find Sum of overtime worked between networkdays and holidays in a month

1. ## Find Sum of overtime worked between networkdays and holidays in a month

HI All,
I am trying to create a formula to sum overtime worked between a network days in a month and holidays in a month.

Requesting your support on this. Attached is the format for example.

2. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

How do you imagine you will do this?

The attached document has a 31 day spread or seemingly so... with Sundays highlighted and a single date reference in the records row 4, A Column. This date represent 1/18/2011. This is the only date reference that you have in the entire document you have shared.

Networkdays requires a START and END date. To consider HOLIDAYS you need a list of dates that your company, organization, spreadsheet... considers or honors as a holiday. This list of dates would be referenced within the NETWORKDAYS formula...

But until you give us a realistic spread to evaluate such days this is an impossible task.

NETWORKDAYS(Start_Date,End_Date,[Holidays])

Start_Date - As it states, from which date are you checking
End_Date - As it states, To which date are you checking
[Holidays] - an array of dates or a reference to a list contained within the sheet

Good luch

3. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

Hi ELeGault,

I am trying to find total overtime worked in week days (Normal Overtime - NOT) and Holidays (Holiday overtime (All sunday) - HOT).
I tried by working with the below formula but its not working.

=SUMIFS(\$H4:\$AL4,\$H\$2#,NETWORKDAYS.INTL(EOMONTH(DATE(\$B\$1,MONTH(\$D\$1&0),1),0),DATE(\$B\$1,MONTH(\$D\$1&0),1),11))

Manual Calculation :

NOT : 50
SOT : 40

Its an automated attendance sheet, Once year and month changes, weekdays and Holiday (Sunday only) will change accordingly.

So discovering a formula according to that.

Pls refer to the attachment.

4. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

try these:

for NOT HOURS:
``Please Login or Register  to view this content.``
for SOT HOURS:
``Please Login or Register  to view this content.``
with format as number

5. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

Hi janmorris,

Got the solution.

Thank you so much for the formula, I really appreciate your guidance.

Any chance to make array as dynamic..?

Array : \$H\$2:\$AL\$2 and \$H\$4:\$AL\$4

So it will sum only the overtime mentioned in calendar days. Data entry errors can be avoided.

6. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

This correction for NOT HOURS will ensure that when there are blank dates, if any hours are underneath then they will not be counted:
``Please Login or Register  to view this content.``
For SOT HOURS, because it is looking for Sunday (7), if the day or date is blank then there is no inclusion of hours under blank cells.

February 2025 is good example to prove the formulas

for both formulas, you can change the row 4 range as follows:
``Please Login or Register  to view this content.``
this will allow you to drag down for other rows.

7. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

Hi janmorris,

Many thanks to you.... Formula works perfect

8. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

What if we want to add more criteria like we want to add public holidays also.

Attached for reference.

9. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

I have removed the SOLVED tag for now.

10. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

because you are classifying the time as either Normal, Sunday, or Holiday, you need to specify how holiday will be handled.. for example, if holiday is on sunday, then which OT should it be?

11. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

it should be considered as Holiday Overtime not Sunday Overtime...

12. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

Perhaps the following as applied to the file attached to post #8:
1. For cell F4: =SUM(I4:AM4)-SUM(G4:H4)
2. For cell G4: =SUMPRODUCT((\$I\$2:\$AM\$2<>"")*(WEEKDAY(\$I\$2:\$AM\$2,2)=7)*\$I\$4:\$AM\$4)-SUMPRODUCT((\$I\$2:\$AM\$2<>"")*(WEEKDAY(\$I\$2:\$AM\$2,2)=7)*(COUNTIF(PUBLICHOLIDAY_LIST,\$I\$2:\$AM\$2))*\$I\$4:\$AM\$4)
3. The formula in cell H4 seems to be yielding correct results already.
Let us know if you have any questions.

13. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

Hi JeteMc,

Thank you so much for the support, the formula works.

I concluded with the below formula. Attachment for reference.

NOT
Formula:
`Please Login or Register  to view this content.`

SOT
Formula:
`Please Login or Register  to view this content.`

HOT
Formula:
`Please Login or Register  to view this content.`

14. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

15. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

Thanks for your notes, Have completed both the process.

16. ## Re: Find Sum of overtime worked between networkdays and holidays in a month

RE: Post #13, You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1