Forum Discussion
How to count number of days in roster depending today's date
I've a roster for staff (see attachment), cell C3 - AG3 is in date format(eg.1-Dec-2017), C6 - AG6 is the working day of the month, how to set a formula to count the numbers of working days(O) if today is 28-Dec-2017 (the answer should be 18days)?
Please see attached - it counts correctly 18 days if today is still 28th.
8 Replies
Hello,
Below could be another solution:
=NETWORKDAYS.INTL(C3,G3,1,{"25-Dec-2017","26-Dec-2017"})
But you need to explicitly specify the holidays in the 5th parameter of the formula.
For elegancy, You can even have these holidays listed out in a different range of cells and then define a dynamic range name or format such range as table and reference the dynamic range name or table name in the 5th parameter.
Thanks,
Bala..
- Thanks, this formula doesn't fit for my case, because besides of the public hoilday 25 & 26 Dec, I have another category like AL (annal leave) SL (sick leave)...need to be calculate for other staff.
And I try to add a column at C5:AG5 to check C3:AG3 is equal or less than today's date, and use countifs function to count the workday between C6:AG6.
Thanks for your help Bala, as a comment
For elegancy it's better to pick-up array with holidays form row 6, they are already defined here. To keep the same data in several places (separate list of holidays) is the overhead in maintenance and potential source of errors.
This function is sufficient for you:
=COUNTIF($C$6:$AG$6,"O")
But I recommend you to learn and use https://support.office.com/en-us/article/NETWORKDAYS-function-48e717bf-a7a3-495f-969e-5005e3eb18e7 function.
Thanks for your solution, but I want to count the workday for today if today is 28-Dec-2017, the result should be 18, but the formula you suggested the result is 19.
That could be like
=SUMPRODUCT((MONTH($3:$3)=12)*($6:$6="o")*($3:$3<=TODAY()))
to calculate for TODAY or use given date instead. MONTH could be removed if only Dec is in roster.
