Invalid Operation Error with Date Range Conditions and Cross Sheet References

Michelle Rogers

I need this formula to return the pay date {pay date} if the hire date [Hire/QE Date]@row is between the date range for the beginning of the pay period {Begin Date} to the end of the pay period {End Date}.

This formula was generated by the Smartsheet AI formula generator and returns an invalid operation error.

=IF(AND([Hire/QE Date]@row <= {2025 Biweekly Pay End Date}, [Hire/QE Date]@row >= {2025 Biweekly Pay Begin Date}), {2025 Biweekly Pay Schedule Pay Date}, "")

Paul Newcome

You will need an INDEX/COLLECT instead.

=INDEX(COLLECT({Pay Date}, {Begin Date}, @cell <= [Hire/QE Date]@row, {End Date}, @cell >= [Hire/QE Date]@row), 1)

Paul Newcome

You will need an INDEX/COLLECT instead.

=INDEX(COLLECT({Pay Date}, {Begin Date}, @cell <= [Hire/QE Date]@row, {End Date}, @cell >= [Hire/QE Date]@row), 1)

Michelle Rogers

Thank you sir! Your advice worked like a charm! I really appreciate the help.

Paul Newcome

Happy to help. 👍️