Forum Discussion

w90900323's avatar
w90900323
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
w90900323's avatar
w90900323
Copper Contributor
Dec 28, 2017

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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 28, 2017

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.

 

  • w90900323's avatar
    w90900323
    Copper Contributor
    Dec 28, 2017

    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

Resources