Forum Discussion

George_Weston's avatar
George_Weston
Copper Contributor
Mar 08, 2021
Solved

COUNTIF Containing Date Calculations

Hi Everyone! I hope someone can help me here.

I'm working in a Table where I am tracking maintenance on my boat. File attached.

I have a Date Due column and a Date Completed column.

I display the number of Items Past Due in a Summary section at the top of the sheet above the Table. 

I want to calculate how many items are past their due date, but I also want to make sure that if someone completes a maintenance action and enters the completion date into the Date Completed cell, that the number of items past their due date count is reduced to account for that.

I have tried the below formula but the second COUNTIF function doesn't seem to be subtracted from the first.  When I break it out it returns 0 rather than the number of cells with Date Completed entries later than the Date Due entries. 

I also tried the COUNTIFS function but no luck there either. 

Help please before I go crazy! Thanks!

 

=COUNTIF(Table1[Date Due],"<="&TODAY())-COUNTIF(Table1[Date Completed],">"&Table1[Date Due])

 

  • Hi George_Weston 

     

    You may use below formula in cell B9

    =COUNTIFS(Table110[Date Due],"<="&TODAY(),Table110[Date Completed],"")

     

    Thanks

    Tauqeer

4 Replies

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor

    Hi George_Weston 

     

    You may use below formula in cell B9

    =COUNTIFS(Table110[Date Due],"<="&TODAY(),Table110[Date Completed],"")

     

    Thanks

    Tauqeer

    • George_Weston's avatar
      George_Weston
      Copper Contributor
      Hi Tauqeer.
      It works great! Thank you. What does the "10" you placed after "Table1" do and where can I find a good reference on using it?
      Thanks again,
      George
      • tauqeeracma's avatar
        tauqeeracma
        Iron Contributor

        Hi George_Weston 

         

        You are welcome.

         

        I have not placed '10' rather its the part of table name in your file. Please see the below image. Same table name "Table110" is appearing in your formula as well.

         

         

         

        Thanks,

        Tauqeer

Resources