Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Countifs with Today Function
Hi!
My team and I are having a hard time with some of our formulas. We need a count for how many projects that are ending in 30 days and as well as 90 days. Our currently formulas are not providing accurate numbers based off our what our filters are showing.
Complete next 30 days formula: =COUNTIFS({HR Projects - Complete Date}, >=TODAY(-1), {HR Projects - Complete Date}, <=TODAY(30))
Complete next 90 days formula: =COUNTIFS({HR Projects - Complete Date}, <=TODAY(1), {HR Projects - Complete Date},
Any help would be greatly appreciated!
Answers
-
Can you share screenshots of the filter criteria as well as what counts you are getting for the filters vs the formulas?
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff,
Here you go.
There's 65 for 90 days and 37 for 30 days.
-
@Krista Coffman The 30-day formula is counting everything from yesterday, today, and then 30 days past today. >= TODAY(-1) means greater than or equal to yesterday. If you want this to be comparable to your filter, you need to change it not to include today either, but only the next 30 days AFTER today:
=COUNTIFS({HR Projects - Complete Date}, >TODAY(0), {HR Projects - Complete Date}, <=TODAY(30))
The exact same formula is used for 90 days, just change the 30 to 90:
=COUNTIFS({HR Projects - Complete Date}, >TODAY(0), {HR Projects - Complete Date}, <=TODAY(90))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you Jeff!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!