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!

Kelly Pratt

Solution Consultant

Echo Consulting

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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)))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!