Forum Discussion
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])
You may use below formula in cell B9
=COUNTIFS(Table110[Date Due],"<="&TODAY(),Table110[Date Completed],"")
Thanks
Tauqeer
4 Replies
- tauqeeracmaIron Contributor
You may use below formula in cell B9
=COUNTIFS(Table110[Date Due],"<="&TODAY(),Table110[Date Completed],"")
Thanks
Tauqeer
- George_WestonCopper ContributorHi 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- tauqeeracmaIron Contributor
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