How do I use the TODAY() function in a formula based on a specific date?

I have a field that calculates a fee (this fee is part of the formula INDEX({IF - Parks & Rec Range}, MATCH([Land Use]@row, {IF - Land User Range}, 0)) * [For Each]@row). For the time period of 7/1/2023 - 6/30/2024, I want to calculate the fee at 50%. For 7/1/2024 - 6/30/2025, 75%. After 7/1/2025, 100%.

This formula is not working, it's getting #INVALID OPERATION. I want to note that the issue is probably in the TODAY() call because I get the same result with the following formula:

=IF(TODAY() < {Impact Fee Range 1}, "Past Due") -- {Impact Fee Range 1} is a date field on another sheet holding 7/1/2024

My Formula:

=IF(TODAY() <= "07/01/24", INDEX({IF - Parks & Rec Range}, MATCH([Land Use]@row, {IF - Land User Range}, 0)) * [For Each]@row * 0.5, IF(TODAY() <= "07/01/25", INDEX({IF - Parks & Rec Range}, MATCH([Land Use]@row, {IF - Land User Range}, 0)) * [For Each]@row * 0.75,INDEX({IF - Parks & Rec Range}, MATCH([Land Use]@row, {IF - Land User Range}, 0)) * [For Each]@row)

NOTE: In my formula, {IF - Parks & Rec Range}, the IF stands for Impact Fees and is not an IF statement.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!