I ve AI to assit me to create this formula, but it is not working: Can anyone spot the error:
IF({Rental Status} = “Active”, ROUND(DAYS({Switch Date (Hide)}, {Rental Start}), 0), DAYS({Rental End}, {Rental Start}))
Hi Glenn,
What’s not working exactly?
What if we could break free of the Formula column? and write formulas that update any type of columns? What if a formula could update multiple columns at once? This is possible with the Advanced Formula Booster app.
The switch date column is a formula column:
SWITCH({Rental Status},“Active”, Today(),“Completed”, {Rental End})
I would prefer if this date used the {Rental End} date for ANY status other than Active but not sure how to write the formula..
Hi Gillies, If the status is “Active” the formula works. But if the status is anything else it gives the invalid formula triangle. Even though there is a date in both the rental start and end columns
That’s probably because when the status is active, {Switch Date (Hide)} is empty.
When {Switch Date (Hide)} is empty, then:
ROUND(DAYS({Switch Date (Hide)}, {Rental Start}), 0)
will return an error. Hence the triangle.
You may think, but this part of the formula shouldn’t be taken into account because {Rental Status} is NOT “Active”, but this is not the case. In an IF Statement, monday calculates both outcomes before evaluating the condition. If you want to know more on this, see A Simple Way to Handle Empty Columns and Avoid Errors in your Formulas. This method of evaluating an IF statement is called the Eager approach vs the Lazy approach (2nd paragraph of the article).
Anyway, here is the solution to your problem
Replace {Switch Date (Hide)} by:
IF(Switch Date (Hide)="","2025-01-01",Switch Date (Hide))
As in:
IF({Rental Status} = "Active", ROUND(DAYS(IF({Switch Date (Hide)}="", "2025-01-01",{Switch Date (Hide)}), {Rental Start}), 0), DAYS({Rental End}, {Rental Start}))
That way, if {Switch Date (Hide)} is empty, monday will calculate the first outcome of your IF statement using a date of 2025-01-01 (it could be any other date: you don’t care since this result will never be displayed anyway).
Depending on your scenarios, you may want to do the same with the other dates.
Just because I am the developer of the Advanced Formula Booster and have put a lot of thought on how to provide a more intuitive solution than the formula column, here is how you could handle it with the Advanced Formula Booster:
Note that:
- You can use multiple lines for your formulas
- You can use SKIP() / RESUME() to jump over certain lines
- The COUNTDAYS function in AFB, actually counts the number of days, so there is no need to round the result.