Platform discussions 3 days ago
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}))
What kind of columns are each of thsoe? ie are they all date columns?
Hi Desiree, They are all dates columns except Switch Date, which is a formula column.
It uses this formula: SWITCH({Rental Status},“Active”, Today(),“Complete”, {Rental End})
Hi Glenn, I’m making a few assumptions from the information here so shout if I’m wrong!
I’ve broken the formulae down into individual columns to make it easy to follow but the formulae could be combined.
First, your Switch Date is a formula column. Start and End dates are Date columns and always contain a value. If the rental is complete, it shows the rental end date. If it isn’t complete, it shows today:
SWITCH(
{Rental Status},
“Active”,FORMAT_DATE(TODAY(),“YYYY-MM-DD”),
“Complete”,{Rental End}
)
When I’m working with dates in formula columns I always convert to DATEVALUE so I don’t have errors caused by working with dates in different formats. The Start DateValue, End DateValue and Switch DateValue columns contain these formulae:
- DATEVALUE({Rental Start})
- DATEVALUE({Rental End})
- DATEVALUE({Switch Date})
Finally, if the rental is Active, the Days column calculates the number of days between the Rental Start date and today. If it’s Complete, it calculates the number of days between Rental Start and End dates:
IF(
{Rental Status}=“Active”,
{Switch DateValue}-{Start DateValue},
{End DateValue}-{Start DateValue}
)
You could combine the five formulae columns into one if you wanted to, but I’ve left it like this so we can follow what each step of the final formula is doing.
Hope that helps and achieves what you want!