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!