SOLVED

How to count number of days in roster depending today's date

Copper Contributor

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)?  Screen Shot 2017-12-28 at 10.19.31 AM.png

8 Replies

This function is sufficient for you:

=COUNTIF($C$6:$AG$6,"O")

But I recommend you to learn and use NETWORKDAYS 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.

 

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

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.

Thanks, but i found your formula will still count the workday as 19days if today is 28-Dec-2017.

So I 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

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
best response confirmed by w90900323 (Copper Contributor)
Solution

Please see attached - it counts correctly 18 days if today is still 28th.

1 best response

Accepted Solutions
best response confirmed by w90900323 (Copper Contributor)
Solution

Please see attached - it counts correctly 18 days if today is still 28th.

View solution in original post