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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives