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
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Dec 28, 2017

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.

  • 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