Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Nested group of IFs not refreshing/rerunning when a value is updated

Topic Labels: Formulas
1385 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Casey_Unrein
5 - Automation Enthusiast
5 - Automation Enthusiast

I am having trouble, where when I update one of the variables that should change the results of an IF statement, the formula as a whole doesn’t seem to be “rerunning” and I get stuck with the last result.

Example, I start with a {fabric release date/dates} of 05/16/2019 and I get a result from farther down this IF statement. I then change the release date to 05/16/2050 and the result remains the same. If I start fresh, it will recognize that the date is later than today and return “Fabric Not Released”, but it doesn’t seem to consistently refresh/rerun from the beginning of the formula.

What can I do? Is the a “hard reload” type action? Do I need to simplify the IFs?

IF({Retired?}=1,“Retired”,
IF({Model Completed Date}=0,“Unmade”,
IF(DATETIME_FORMAT({Fabric Release Date/Dates},‘MM/DD/YYYY’)>DATETIME_FORMAT(TODAY(),‘MM/DD/YYYY’),“Fabric Not Released”,
IF(DATETIME_FORMAT({Pattern Planned Release Date},‘MM/DD/YY’)>DATETIME_FORMAT(TODAY(),‘MM/DD/YYYY’),“Pattern Not Released”,
IF({SumOfStatus#}=0,“Available”,
IF({SumOfStatus#}=1,“Unavailable”,
IF({SumOfStatus#}>1,“Double Booked”)))))))

2 Replies 2
Casey_Unrein
5 - Automation Enthusiast
5 - Automation Enthusiast

I get the feeling it has a lot to do with the dates actually. Is there a way to convert dates to just numbers like in Excel?

Casey_Unrein
5 - Automation Enthusiast
5 - Automation Enthusiast

Found cleaner code. This seems like it should work better. I was actually having all kinds of troubles with it not knowing how to compare two dates via the greater than/less than signs.

IF({Retired?}=1,“Retired”,
IF({Model Completed Date}=0,“Unmade”,
IF(DATETIME_DIFF({Fabric Release Date/Dates},TODAY())>0,“Fabric Not Released”,
IF(DATETIME_DIFF({Pattern Planned Release Date},TODAY())>0,“Pattern Not Released”,
IF({SumOfStatus#}=0,“Available”,
IF({SumOfStatus#}=1,“Unavailable”,
IF({SumOfStatus#}>1,“Double Booked”)))))))