Formula for Not In the Last (x) days
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
Best Answer
-
@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)))
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
The formula would be something along the lines of...
=COUNTIFS({Date Column}, @cell< TODAY(-7))
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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)))
-
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
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!