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

Vikki Cooper
edited 12/09/19 in Archived 2017 Posts

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


  • MattH
    edited 08/09/17

    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. 


This discussion has been closed.