CountIf's with a date

asuyama

Hello-

I am trying to get a formula to calculate if the task is still open and the date is before today's date then the task is overdue. This is what I have for the cell and it keeps returning unparseable:

=COUNTIFs({NITD Action Log Range 2}, "Open", [{NITD Action Log Range 3}, <=TODAY() ]

 

Range 2 is a column stating if the task is Open or Closed and Range 3 is a due date column.

Can someone help with this formula?

 

Thank you!!

Chris McKay

Hi asuyama,

You're almost there. You just needed to get rid of the square brackets:

=COUNTIFS({NITD Action Log Range 2}, "Open", {NITD Action Log Range 3}, <=TODAY())

I'm assuming that the 2 NITD Action Log ranges are on another sheet?

asuyama

Yes it was reference to another sheet. Thank you so much for your help! I do have another one:

I am trying to figure out the average time a task is open, I have a task assigned date column and a due date column and a task open/closed column. What would the formula for that look like? 

Thanks! 

Chris McKay

Hi asuyama,

You are more than welcome.

To work out the average, I'd suggest making your life easier and provide more granularity by adding 3 hidden columns, then placing the following formulas in the first data row of the appropriate column and finally dragging it down as far as needed:

• Assigned to Open
  
  =IF(AND(LEN([Task Assigned Date]1)>0, LEN([Task Open Date]1)>0), NETDAYS([Task Assigned Date]1, [Task Open Date]1),"")
• Open to Close
  
  =IF(AND(LEN([Task Open Date]1)>0, LEN([Task Closed Date]1)>0), NETDAYS([Task Open Date]1, [Task Closed Date]1),"")
• Due to Closed
  
  =IF(AND(LEN([Task Closed Date]1)>0, LEN([Task Due Date]1)>0), NETDAYS([Task Closed Date]1, [Task Due Date]1),"")

Once you've done this, you can place the following formulas in the first row of the Task Open Date, Task Closed Date and Task Due Date columns (not the new hidden columns you created above):

• =AVG([Assigned to Open]:[Assigned to Open])
• =AVG([Open to Closed]:[Open to Closed])
• =AVG([Due to Closed]:[Due to Closed])