COUNTIF with dates pulled from cells?

David Acord

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)

Genevieve P.

Hi @David Acord

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

Genevieve P.

Hi @David Acord

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)

Then your COUNTIF would be:

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

Cheers!

Genevieve

David Acord

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?

Genevieve P.

Hi @David Acord

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

David Acord

That did it! Thanks so much!

Genevieve P.

Wonderful! 🙂 No problem at all.