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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!