# IF Formula for Upcoming Date

Options
✭✭✭✭✭

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?

• ✭✭✭✭✭✭
Options

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)))

• ✭✭✭✭✭✭
Options

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)))

• ✭✭✭✭✭
Options

Thank you, @Kelly Moore ! Worked perfect.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!