Hello there ~
I am trying to create a formula that takes a specific Date, and registers if it is within 180 days from today, and if it is, it returns the value of another field and if it isn’t it returns 0.
Formula I have tried:
IF({Date} <= ADD_DAYS(TODAY(),180), {Value}, 0)
This did not work. Any suggestions?
Hi Sara,
If you create the following formula:
ADD_DAYS(TODAY(),180)
you’ll see that the result looks like this: Sat Aug 24 2024 20:11:22 GMT-0500 (Eastern Standard Time).
So comparing it to a date that looks like 2024-03-01 doesn’t make much sense.
You need to compare dates that use the same format. Here is how to change the format of the date in 180 days:
FORMAT_DATE(ADD_DAYS(TODAY(),180),"YYYY-MM-DD")
Now you may compare it with your {Date}:
IF({Date}<=FORMAT_DATE(ADD_DAYS(TODAY(),180),"YYYY-MM-DD"),{Value},0)
Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.
This worked! Thank you so much for the help, Gilles. 