Nested IF using Dates and RGY symbols

vnjeffery96856

Good Morning Smartsheet Community,

I have having a couple of issues regarding the nested If logic against Date data and having the RGY ball symbol output. Here is my request:

1. First column of dates 

• If the Date is over Today(-365), make the ball red. Meaning, if the row date is over one year from today, make that ball red;
• If that Date is under 1 year but is TODAY(-275), make the ball yellow. Meaning, that if the date is 90 days before it hits the one year mark, make that ball yellow;
• If the date is under 1 year and also under Today(-275), make the ball green. Meaning, if that data is under Today(-274) - it is in the green. 

2. Second column of dates

• If the first column of dates exist but there is a date in this new column, do the same logic as above. 

So here is my issue. The code seems to work when the logic reviews the first column of dates. However, I wanted to logic to 'restart" if you will if there is a second column of dates. For instance, if the first column makes that ball red but then the person enters a date in the second column that would make the ball green, then the ball would be green. 

I tried to do the logic of the second column first, then write out the logic for the first column check, and would get unparseable. If I write the logic like below, It doesn't recognize the second date check. 

=IF([Material ReqDate]178 > TODAY(-365), "Red", IF([Material ReqDate]178 > TODAY(-275), "Yellow", IF([Material ReqDate]178 > TODAY(-276), "Green", IF([MR Paid Date]178 < TODAY(-365), "Red", IF([MR Paid Date]178 < TODAY(-275), "Yellow", IF([MR Paid Date]178 < TODAY(-276), "Green"))))))

Can someone please help me re-write this code and explain how I could better write this I attached a screenshot of what I would like to test. 

Last issue - I will sometimes drag the code down and it works in the row above and then returns nothing but blank in the next row, even though the data is exactly the same. Is there a reason why this is?

Thank you,

Veronica

 

Paul Newcome

If the formula you have posted is the one that half works but does not override when the second date is entered, then try working it this way...

 

Write out the nested IF statements for each date separately (by priority). Note bold vs italics vs underlined.

 

=IF([First Date]@row....................., "Red", IF([First Date]@row......................, "Yellow", IF([First Date]@row.............................., "Green")))

 

=IF([Second Date]@row....................., "Red", IF([Second Date]@row......................, "Yellow", IF([Second Date]@row.............................., "Green")))

.

Now write out another IF statement:

 

=IF(this_is_true, do_this, else)

.

So we know that we want to say that IF the first date is a date, then use the first date, else use the second date.

.

IF(ISDATE([First Date]@row), IF([First Date]@row....................., "Red", IF([First Date]@row......................, "Yellow", IF([First Date]@row.............................., "Green"))), IF([Second Date]@row....................., "Red", IF([Second Date]@row......................, "Yellow", IF([Second Date]@row.............................., "Green"))))

vnjeffery96856

Hi Paul,

So I did what you suggested and it kinda works. ISBLANK looks first at [SECOND DATE] and returns the associated symbol based on the logic. However, if [SECOND DATE] is blank and but [FIRST DATE] is not blank, the return is still nothing(blank) when it should apply the logic to now the [FIRST DATE] column and return the appropriate symbol. I closed the nested IF for the isblank part one and got unparseable:

 

=IF(ISDATE([Material ReqDate]178), IF([Material ReqDate]178 < TODAY(-365), "Red", IF(AND([Material ReqDate]178 > TODAY(-365), [Material ReqDate]178 < TODAY(-275)), "Yellow", IF([Material ReqDate]178 > TODAY(-275), "Green")))), IF([MR Paid Date]178 < TODAY(-365), "Red", IF(AND([MR Paid Date]178 > TODAY(-365), [MR Paid Date]178 < TODAY(-275)), "Yellow", IF([MR Paid Date]178 > TODAY(-275), "Green")))

 

However, if I put the nested if all together, it works when returning the first isblank condition but again, still only applies the logic to that [SECOND DATE] :

 

=IF(ISDATE([Material ReqDate]178), IF([Material ReqDate]178 < TODAY(-365), "Red", IF(AND([Material ReqDate]178 > TODAY(-365), [Material ReqDate]178 < TODAY(-275)), "Yellow", IF([Material ReqDate]178 > TODAY(-275), "Green", IF([MR Paid Date]178 < TODAY(-365), "Red", IF(AND([MR Paid Date]178 > TODAY(-365), [MR Paid Date]178 < TODAY(-275)), "Yellow", IF([MR Paid Date]178 > TODAY(-275), "Green")))))))

How do I force the formula to say the "else" part by applying the same logic to another column of dates?

Thanks,

Veronica

 

Paul Newcome

Going off of your first formula, you would want to move one of the closing parenthesis in the middle to the end of the formula. I will try to break it down so that the exact one to move is clear...

 

=IF(ISDATE([Material ReqDate]178), IF([Material ReqDate]178 < TODAY(-365), "Red", IF(AND([Material ReqDate]178 > TODAY(-365), [Material ReqDate]178 < TODAY(-275)), "Yellow", IF([Material ReqDate]178 > TODAY(-275), "Green")))), <--- Remove one from here

 

IF([MR Paid Date]178 < TODAY(-365), "Red", IF(AND([MR Paid Date]178 > TODAY(-365), [MR Paid Date]178 < TODAY(-275)), "Yellow", IF([MR Paid Date]178 > TODAY(-275), "Green"))) <--- And put it here

vnjeffery96856

Paul,

Awesome. I did what you suggested and it worked for my second formula:

=IF(ISDATE([Material ReqDate]4), IF([Material ReqDate]4 < TODAY(-365), "Red", IF(AND([Material ReqDate]4 > TODAY(-365), [Material ReqDate]4 < TODAY(-275)), "Yellow", IF([Material ReqDate]4 > TODAY(-275), "Green"))), IF([MR Paid Date]4 < TODAY(-365), "Red", IF(AND([MR Paid Date]4 > TODAY(-365), [MR Paid Date]4 < TODAY(-275)), "Yellow", IF([MR Paid Date]4 > TODAY(-275), "Green"))))

Thank you for your breakdown. So the first IF IS DATE has the last parenthesis, then the first set of three parenthesis for first condition, followed by the second set of three parenthesis in the else condition. I definitely missed that in the second pass attempt. It definitely works now. Thank you so much. Dates and nested if's give me the hardest time in Smartsheet. 

-Veronica

Paul Newcome

Happy to help! 

 

A few pointers for nested IF's...

 

1. Write everything out before trying to build.

2. Sort it.

3. Consolidate.

4. Write each one out individually.

5. Keep syntax in mind and start dropping things in.

.

One thing I have found with keeping syntax and dropping things is to write each formula out in it's own cell and use cell references as I work down the line. Then you can take the formula that is in the cell being referenced and replace the cell reference with the formula itself (excluding the = of course).

.

Another little tip... Write your IF's out in checkboxes and use a 1 for the if_true portion and 0 for the else.

.

So in [Column Name]1 I would have:

=IF(this is true, 1, 0)

.

In [Column Name]2:

=IF(this other thing is true, 1, 0)

.

[Column Name]3:

=IF(this third thing is true, 1, 0)

.

Then plug in test data to make sure each box is being checked when it should be. From there, it is literally as simple as copying the second formula without the =, highlight the 0 in the first formula, the paste the second in place of the first 0.

 

Do the same thing to put the third inside of the second, then replace the third 0 with whatever you want that last little bit to produce, and there you have it.

.

Using some sort of placeholder whether it be an actual value or checked/unchecked box or whatever else within a formula while you build out each part definitely makes life A LOT easier.