Forum Discussion

Copper Contributor

Dec 28, 2017

Solved

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 tod...

Formulas & Functions

Requests

SergeiBaklan
Dec 28, 2017
Please see attached - it counts correctly 18 days if today is still 28th.
CountDaysRoster.xlsx11 KB

Deleted

Dec 28, 2017

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..

w90900323

Copper Contributor

Dec 28, 2017

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