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.
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.
Last edited by SHIBUVARGHESE; 05-03-2022 at 04:23 PM.
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
-If you think you are done, Start over - ELeGault
Hi ELeGault,
Thanks for your response,
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.
Your reply is highly appreciated.
Last edited by SHIBUVARGHESE; 05-03-2022 at 02:03 PM.
try these:
for NOT HOURS:
for SOT HOURS:Please Login or Register to view this content.
with format as numberPlease Login or Register to view this content.
As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.
And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]
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.
This correction for NOT HOURS will ensure that when there are blank dates, if any hours are underneath then they will not be counted:
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.Please Login or Register to view this content.
February 2025 is good example to prove the formulas
for both formulas, you can change the row 4 range as follows:
this will allow you to drag down for other rows.Please Login or Register to view this content.
Last edited by janmorris; 05-03-2022 at 04:05 PM.
Hi janmorris,
Many thanks to you.... Formula works perfect
What if we want to add more criteria like we want to add public holidays also.
Attached for reference.
I have removed the SOLVED tag for now.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
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?
it should be considered as Holiday Overtime not Sunday Overtime...
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.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi JeteMc,
Thank you so much for the support, the formula works.
I concluded with the below formula. Attachment for reference.
NOTFormula:Please Login or Register to view this content.
SOTFormula:Please Login or Register to view this content.
HOTFormula:Please Login or Register to view this content.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
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.
Thanks for your notes, Have completed both the process.
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)
Bookmarks