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

Eric Horn

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.

Lucas Rayala

@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:

DATE Function | Smartsheet Learning Center

Lucas Rayala

@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:

DATE Function | Smartsheet Learning Center

Eric Horn

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

Lucas Rayala

@Eric Horn, glad to hear that you got it working!