Formula for Not In the Last (x) days

Hande Makam

Hello,

We are using the Status/Latest Comment field in our request log source sheet along with a Status/Latest Comment Date field. This date field records the current date when a comment has been made.

Using a formula, how do I find the number of Status/Latest Comment fields in my source sheet that have not been updated in the last 7 days? Thank you so much!

Regards,

Hande Makam

Paul Newcome

@Genevieve P. Sure thing! And good call on the blank date cells.

@Hande Makam To exclude blank date cells, this should work:

=COUNTIFS({Date Column}, AND(@cell <> "", @cell< TODAY(-7)))

Genevieve P.

Hi @Hande Makam

While you could use a Formula, what I would personally do is set up a Row Report to filter the rows and create my COUNT for me.

In a Row Report could you filter by "is not in the last X days"

Then you can apply a Summary to those rows to COUNT how many there are. The reason I personally would do it this way is so that you can work directly in that report, leaving comments/notes or adding new data to the row. Would that work for you?

Cheers,

Genevieve

Hande Makam

Hi Genevieve,

Thank you so much for your feedback! While I understand the reasoning behind using a report, current requirement is to display these numbers on a metrics sheet using a formula. I really appreciate if you could provide any further guidance.

Regards,

Hande Makam

Paul Newcome

The formula would be something along the lines of...

=COUNTIFS({Date Column}, @cell< TODAY(-7))

Genevieve P.

Hi @Hande Makam

Thanks for clarifying the end-goal! Paul is absolutely correct -let us know if his formula works for you 🙂 (Thanks for the help, @Paul Newcome!)

Just a note, keep in mind that blank date cells will be included as "in the past". You may want to add a second column and criteria into the COUNTIFS to narrow down the rows you're counting, like a filter.

Cheers,

Genevieve

Paul Newcome

@Genevieve P. Sure thing! And good call on the blank date cells.

@Hande Makam To exclude blank date cells, this should work:

=COUNTIFS({Date Column}, AND(@cell <> "", @cell< TODAY(-7)))

Hande Makam

Thank you so much Genevieve and Paul! The formula works great, and I was able to complete my task.

Thank you so much again for your guidance and help.

Regards,

Hande Makam

Paul Newcome

Happy to help. 👍️