CountIf's with a date

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!!

Find more posts tagged with

Formulas

Comments

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])

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…