Dec 27 2017
06:55 PM
- last edited on
Jul 25 2018
10:37 AM
by
TechCommunityAP
Dec 27 2017
06:55 PM
- last edited on
Jul 25 2018
10:37 AM
by
TechCommunityAP
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)?
Dec 27 2017 08:27 PM
This function is sufficient for you:
=COUNTIF($C$6:$AG$6,"O")
But I recommend you to learn and use NETWORKDAYS function.
Dec 27 2017 10:09 PM
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.
Dec 27 2017 11:56 PM
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.
Dec 28 2017 12:13 AM
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..
Dec 28 2017 01:10 AM
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.
Dec 28 2017 08:27 AM
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
Dec 28 2017 08:36 AM
Dec 28 2017 08:38 AM
SolutionPlease see attached - it counts correctly 18 days if today is still 28th.
Dec 28 2017 08:38 AM
SolutionPlease see attached - it counts correctly 18 days if today is still 28th.