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

@Eric Horn, you need to format the date so the formula can read it. You can't use "07/01/24" because Smartsheet thinks anything inside quotes is a word. To create a date, use the DATE function. The first IF will look like this:
=IF(TODAY() <= DATE(2024, 7, 1)
Here's the link to the Date function:
Answers

@Eric Horn, you need to format the date so the formula can read it. You can't use "07/01/24" because Smartsheet thinks anything inside quotes is a word. To create a date, use the DATE function. The first IF will look like this:
=IF(TODAY() <= DATE(2024, 7, 1)
Here's the link to the Date function:

Thank you, that fixed it. I had tried the DATE() formula without success, but this time it worked.

@Eric Horn, glad to hear that you got it working!
Help Article Resources
Categories
Check out the Formula Handbook template!