IF Formula for Upcoming Date

Kelly Pratt

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 Moore

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

Kelly Moore

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

Kelly Pratt

Thank you, @Kelly Moore ! Worked perfect.