IF Formula for Upcoming Date
I am attempting to pull in the next upcoming date, based on a specific clients name. At first I was thinking I would have a reference sheet for each client and use a MIN(COLLECT formula to pull in the next upcoming payroll date, which worked.
Example of formula that worked:
=MIN(COLLECT({Due Date}, {Due Date}, @cell > [Prep Steps Complete Date]@row))
But would like to have one sheet where I have all of the important dates for every client, so I am attempting to combine this with an IF formula, but I can't get it quite right
=IF({Calendar Range 1}, "Example Client", (MIN(COLLECT({Payroll Due Date}, {Payroll Due Date}, @cell > [Steps Complete Date]@row))), "")
What am I missing?
Appreciate the help in advance!
Best Answer
-
Hey @Kelly Pratt
When using cross sheet ranges, IF statements must be framed differently.
Using the Countifs function to drive the query, this will check if your criteria is found. If it is found, then the Count will be greater than zero.
=IF(COUNTIFS({Calendar Range 1}, "Example Client")>0, MIN(COLLECT({Payroll Due Date}, {Payroll Due Date}, @cell > [Steps Complete Date]@row)))
Answers
-
Hey @Kelly Pratt
When using cross sheet ranges, IF statements must be framed differently.
Using the Countifs function to drive the query, this will check if your criteria is found. If it is found, then the Count will be greater than zero.
=IF(COUNTIFS({Calendar Range 1}, "Example Client")>0, MIN(COLLECT({Payroll Due Date}, {Payroll Due Date}, @cell > [Steps Complete Date]@row)))
-
Thank you, @Kelly Moore ! Worked perfect.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!