# COUNTIF with dates pulled from cells?

Options
edited 10/29/21

Hello!

I'm trying to run a count of the number of entries on a sheet, where the "needs analysis assigned date" is greater than 5 days prior to the "date requestor contacted" date for that same row of data.

Example:

Row 1:

Needs analysis assigned date: 11/1/2022

Date Requestor contacted: 11/2/2022

The date difference only being 1 day, this should not be counted.

However, for:

Row 2:

Needs analysis assigned date: 11/1/2022

Date Requestor contacted: 11/7/2022

This should be counted as it's been 6 days.

My formula is below, and it's #UNPARSEABLE. can anyone help?

=COUNTIF([Date Requestor Contacted]:[Date Requestor Contacted], <=[Needs analysis assigned date](-5)@cell)

• Employee
Options

I'm glad this is on the right track for you! Good point about blank dates. Yes, lets add in another statement to make sure this doesn't happen:

=IF(OR([Date Requestor Contacted]@row = "", [Needs analysis assigned date]@row = ""), 0, IF([Date Requestor Contacted]@row >= [Needs analysis assigned date]@row + 5, 1, 0))

Cheers!

Genevieve

October 8 - 10, Seattle, WA | Register now

• Employee
Options

The way I would do this is to have a helper checkbox column hidden on the sheet with a Column Formula applied that would check each row for these conditions. If it's true, it would check the box. Then your COUNTIF can simply count how many checkboxes there are!

Try something like this as a column formula:

=IF([Date Requestor Contacted]@row >= [Needs analysis assigned date]@row + 5, 1, 0)

=COUNTIF([Helper Column]:[Helper Column], 1)

Cheers!

Genevieve

October 8 - 10, Seattle, WA | Register now

• Options

Fantastic! Thanks @Genevieve P. - this is definitely working. I've noticed a hiccup (at least for me) - this will also check the box if there's no date entered in either of those date columns, such as if the row has incomplete data. Do you have any suggestions on how to not count those/not check the box until those two dates are filled in?

• Employee
Options

I'm glad this is on the right track for you! Good point about blank dates. Yes, lets add in another statement to make sure this doesn't happen:

=IF(OR([Date Requestor Contacted]@row = "", [Needs analysis assigned date]@row = ""), 0, IF([Date Requestor Contacted]@row >= [Needs analysis assigned date]@row + 5, 1, 0))

Cheers!

Genevieve

October 8 - 10, Seattle, WA | Register now

• Options

That did it! Thanks so much!

• Employee
Options

Wonderful! 🙂 No problem at all.