Hi all,
1) Am looking for an excel formula in which its calculate the total number of hours working per week excluding saturdays, sundays and public holidays. It should be a cumulative of hours per week.
Note that I have different working hours for different countries.
2) Am also looking for a formula in which it tell whether my staff is active or not active base on my START and RESIGNED Date.
Pls see attached for better understanding.
Thanking you in advance for your help.
Regards,
Ridwan
Hi Ridwan,
Welcome to our forum!
You can use NETWORKDAYS.INTL for this, but you need to list your holiday dates. See file attached.
Mynda
Thank you very much Mynda for your prompt answer which is really helpful in designing my report.
Just one additional question.
Now suppose instead of giving me the the total number of working hours since they joined the company. I just want to know let say the total number of hours per week excluding satudays, sundays and public holidays for only let say 01/08/2022 to 10/08/2022. And the next day when I update my date manually to the 11/08/2022 or 12/08/2022 the hour automatically update base on the number of days till the closure of the month 31/08/2022.
And for the new month of September it start calculating automatically the total number of hours per week excluding satudays, sundays and public holidays for only the month of September without adding that of August.
Pls see attached and thanking you for your help and support.
Note: Am using office 2016
Ridwan
Hi Ridwan,
The NETWORKDAYS.INTL function's first two arguments are the start date and end date. If you want a different range calculated, then change the inputs for those arguments accordingly. e.g.
=NETWORKDAYS.INTL(MAX(D4,EOMONTH($A$1,-1)+1),IF(E4="",$A$1,E4),1,'Month & Public Holidays'!$L$3:$L$11)*VLOOKUP(C4,$I$4:$J$6,2,0)
Mynda
Thanks you Mynda.
Indeed you are among one of the best coach I have met and that found a solution to my problem.
Highly recommanded.
Once again thanks.
