COUNTIFS formula for any future date?

Jenna2424 · 05/03/24

Hi all,

I am trying to create a COUNTIFS formula that counts if the due date column on another sheet is today or any future date.

I tried =COUNTIFS({due date column's range}, >=TODAY()), but it is not working.

Any advice would be appreciated! Thank you.

Samuel Mueller · 05/03/24

try this =COUNTIFS({due date column's range}, @cell>=TODAY())

Also, keep in mind that the today formula does not update unless the sheet is opened and saved everyday. There are some workarounds to this issue.

Samuel Mueller · 05/03/24

try this =COUNTIFS({due date column's range}, @cell>=TODAY())

Also, keep in mind that the today formula does not update unless the sheet is opened and saved everyday. There are some workarounds to this issue.

Jenna2424 · 05/03/24

@Samuel Mueller Wow, thank you so much for the quick response! It looks like that formula worked!

That is a good tip to know. What are those workarounds? The sheet is usually used and saved daily, but in case of holidays, would the formula stop working?

Thank you again :).

Samuel Mueller · 05/03/24

@Jenna2424 take a look at this post, has a good explanation how to get around it.

NEW Help Article! Automatically update the TODAY function in formulas

Genevieve P.

Jan 12, 2023

Hi Community,
Do you use the TODAY Function in any of your formulas? If so, there are a few different ways you can make sure this function is updated daily to reflect the current date without needing to open the sheet.
Check out this NEW Help Article titled Automatically update the TODAY function in formulas that details four different solutions:
https://help.smartsheet.com/articles/2482767-automatically-update-today-in-formulas
Please feel free to leave your feedback on this article by using the "Was this article helpful" form to the right of the documentation:
https://us.v-cdn.net/6031209/uploads/DHNK1L0TAQWI/screenshot-2023-01-12-at-15-37-49.png
🎉 Shout-out to @Andrée Starå who shared how he updated TODAY via this post in the Best Practice topic and provided guidance to others before the article existed!
Don't forget you can nominate any great solution in the Community to be a Best Practice post by following the steps here.
Cheers,
Genevieve

Formulas and Functions

Formulas and Functions

COUNTIFS formula for any future date?

Best Answer

Answers

NEW Help Article! Automatically update the TODAY function in formulas

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of a string in a row

Rounding to nearest Thursday in the past or current day if it's Thursday

JOIN function in a Sheet Summary