Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
IF AND Formulas - Where am I going wrong
Morning All,
I have been trying to work out a formula for a calculation, i am not very good at formulas so i cant see where i have gone wrong, can any one help please?
I need the below:
If today > actual start date & today is > actual end date & % complete < 100% = Red
If today > actual start date & today is < actual end date & % complete > 0% = Green
If today < actual start date & today is < actual end date & % complete <= 100% = Green
I have the below formula
=IF(AND(TODAY() > [Actual Start Date]64, TODAY() > [Actual End Date]64, <0.99 [%Complete]64), "Red", IF(AND(TODAY() > [Actual Start Date]64, Today() < [Actual End Date]64, <= 0 [%Complete]64), "Green", IF(AND(TODAY() < [Actual Start Date]64, TODAY() < [Actual End Date]64, <=0.1 [% Complete]64), "Green")
Any help would be greatly appreciated
Comments
-
The following formula definitely provides the results expected to the three above conditions:
=IF(AND(TODAY() < Start1), "Green", IF(AND(TODAY() > Start1, TODAY() < End1, NOT(Complete1 = 0)), "Green", IF(AND(TODAY() > Start1, TODAY() > End1, Complete1 < 1), "Red", "Red")))
I didn't put much thought into whether or not OTHER conditions would be a problem, but I wanted to give you these thoughts:
1. In the final condition, basically for things in the future, I only look at whether or not the start is in the future. If it is, the end date cannot possibly be before the start, and the % complete is irrelevant because no one is expected to start it yet. (And, if they have, either way, it would be Green).
2. See the screen shot for the sheet I made. Anytime there are nested IFs I find it to be much easier to create the separate conditions like you did, "in English", then make those formulas one at a time. This ensures that the formula is correct before combining them into one. If you have problems with combining them, then you know it's with parenthesis and not the "original" formulas.
