Formula help: IF statements and dates 3 years in the future

tracywalpole

Hello all,

I'm really struggling with this formula for a few days now in my training matrix sheet and appreciate any help you can give before I go nuts LOL.

I want it to take today's date and compare it to the date in cell "basic explosive safety" showing a color symbol: the date the training was taken + 1095 days, + 1065 days, + 1035 days = a different color symbol.

I can get the grey to show if the date cell is blank, but I can't figure out how to get it to show nothing if it meets none of those other requirements. I tried adding , " " after all of the IF arguments but that's not right - I get an #invalid operation. So I'm guessing it is something minor I'm missing. Or I've just completely missed the mark here in my formula.

Thank you for any help you can give!

Tracy

=IF([Basic Explosive Safety]@row = 0, "Gray", IF(TODAY() = [Basic Explosive Safety]@row + 1035, "Green", IF(TODAY() = [Basic Explosive Safety]@row + 1065, "Yellow", IF(TODAY() = [Basic Explosive Safety]@row, +1095, "Red"))))

tracywalpole

@KPH WHEW! I think combining your suggestions and changing a few things on how I have the sheet laid out we got there. I really appreciate you taking the time to help me step through all of this.

I knew the = vs the <>= was needing to be changed. I don't know why the ISBLANK initially didn't work for me, but did eventually... sometimes formulas can really trip you up huh?

I ended up thinking it was asking the formula to do too much to calculate not only the expiration vs the day you look at the sheet and compare so I added another column to calculate the expiration date and then base the "is it expired/red, is it 30 days from expiration/yellow, and is it 60 days from expiration/green" on the expiration date column instead. I did a lot of testing and it seems to work now and shows the right colors when it should and blank when it's meeting none of the requirements! WOOO!!

Anyway, this is the formula and layout I landed on in case anyone else wants to set something up similar! Thank you again! This has been a true labor of love LOL

Formula:

=IF(ISBLANK([Basic Explosive Safety]@row), "Gray", (IF(TODAY() >= [Basic Explosive Safety Expiration]@row, "Red", (IF(TODAY() >= [Basic Explosive Safety Expiration]@row - 30, "Yellow", (IF(TODAY() >= [Basic Explosive Safety Expiration]@row - 60, "Green", "")))))))

KPH

I think you have a teeny tiny comma in the wrong place which means the value you get if none of the logic is true is "Red" and where you think you are going to get "Red" you actually get "+1095". It's the comma before +1095:

This is your formula

=IF([Basic Explosive Safety]@row = 0, "Gray", IF(TODAY() = [Basic Explosive Safety]@row + 1035, "Green", IF(TODAY() = [Basic Explosive Safety]@row + 1065, "Yellow", IF(TODAY() = [Basic Explosive Safety]@row, +1095, "Red"))))

If you remove that comma, you can then put one after "Red" and put "" to return a blank. Like this:

=IF([Basic Explosive Safety]@row = 0, "Gray", IF(TODAY() = [Basic Explosive Safety]@row + 1035, "Green", IF(TODAY() = [Basic Explosive Safety]@row + 1065, "Yellow", IF(TODAY() = [Basic Explosive Safety]@row + 1095, "Red", ""))))

tracywalpole

@KPH hmmm, I am still getting the #invalid operations error. this has been a tough one LOL

KPH

That could be an issue with the other parts of the formula. I'm not sure what you are doing with the dates, but this looks odd to me, although it sounded like it worked for you, so I thought it must be something clever and didn't mention it in case it confused the reply.

TODAY() = [Basic Explosive Safety]@row + 1035

Do you mean today's date equals the date in the Basic Explosive Safety cell plus 1035?

If so, the formula would be:

TODAY(-1035) = [Basic Explosive Safety]@row

(move the number of days to add to the other side and subtract them)

Your full formula is then:

=IF([Basic Explosive Safety]@row = 0, "Gray", IF(TODAY(-1035) = [Basic Explosive Safety]@row, "Green", IF(TODAY(-1065) = [Basic Explosive Safety]@row, "Yellow", IF(TODAY(1095) = [Basic Explosive Safety]@row, "Red", ""))))

However, you will have lots of blanks as this is = and I think you might need to use more than or less than.

tracywalpole

@KPH I'll need to give it more thought then.

I'm trying to make a helper column formula that I can build some conditional formatting on, so when the date in the Basic Explosive Safety cell is 3 years later from when they took it (the date in that cell +1095 days), it would show the red symbol. The yellow is when the date they took it is 3 years - 60 days ( getting closer to being expired, the +1065 days to the date in the basic explosive safety cell). And the green is 3 years -30 days (+1035 days).

I want the formula to work based on what day it is when I look at the sheet, which is where the TODAY() comes in. I want it to take today's date and if it's = to what date is in the basic explosive safety cell + 1095 days then it's expired and to turn it green, yellow, red. But maybe you're right and that's not the right way to go about this. I appreciate you looking at it so far!

Does that make sense? I tried your update and it still gives me an error.

KPH

I think there are 4 problems

1.

I think you want to use greater than and equal or less than and equal rather than equal. If you ..

mark in red when the date in that cell +1095 is equal to today - the cell will only be red on one single day - the day 1095 days after the date.

I think you need to use less than and greater than, rather than equal so it will be red on all days after day 1095.

2.

The TODAY function is usually used the other way around so rather than TODAY being greater than the cell date plus 1095 you would use TODAY minus 1095 is before the date in the cell. Basically, the opposite logic. I've not seen it used as you used it, although it seemed to work for you originally. I can only suggest switching it to the way I am used to using it. So...

TODAY() = [Basic Explosive Safety]@row + 1035

becomes

TODAY(-1035) = [Basic Explosive Safety]@row

or better still, taking point 1 into account

TODAY(-1035) <= [Basic Explosive Safety]@row

3.

You don't have enough parentheses in the formula to nest your IF statements. You need one before each IF.

4.

I'm not sure how your gray was working. I'd suggest using ISBLANK to detect a blank date rather than =0.

=IF(ISBLANK([Basic Explosive Safety]@row), "Gray", "")

Solution

Taking all 4 issues into account, I think the formula you need is.

=IF(ISBLANK([Basic Explosive Safety]@row), "Gray", (IF(TODAY(-1035) <= [Basic Explosive Safety]@row, "Green", (IF(TODAY(-1065) <= [Basic Explosive Safety]@row, "Yellow", (IF(TODAY(-1065) >= [Basic Explosive Safety]@row, "Red", "")))))))

Here that is broken down into the parts which helps explain what is going on and will enable you to troubleshoot it little by little if the big formula doesn't work.

Start with

=IF(TODAY(-30) <= [Basic Explosive Safety]@row, "Green", "false")

This will show green if the date in Basic Explosive Safety is on or before today's date minus 30 (you would change 30 to 1035 but I am using 30 in the example as it is easier to visualize). In other words

If today minus 30 (which is 10/12/23 right now) is before or the day of the date in Basic Explosive Safety the cell will have a green icon. If not it will display "false". We will remove the "false" later and replace it with "" but it is easier to explain if we can see something rather than nothing.

See how the first date is green as it is within 30 of today.

To turn the indicator yellow you'd use:

=IF(TODAY(-60) <= [Basic Explosive Safety]@row, "Yellow", "false")

(changing 60 to 1065 for your use case)

See how the first two dates are now yellow as both were within 60 days.

And for red I would use anything above the value used in amber (in my case 60, yours 1065).

=IF(TODAY(-60) > [Basic Explosive Safety]@row, "Red", "false")

Here the first two dates don't show red as they are before the 60 day but the rest, and the blank show red.

To nest these you put the yellow formula, wrapped in parenthesis, inside the green one instead of "false", like this:

=IF(TODAY(-30) <= [Basic Explosive Safety]@row, "Green", (IF(TODAY(-60) <= [Basic Explosive Safety]@row, "Yellow", "false")))

Then put the red (in parentheses) in to replace the "false" for amber like this:

=IF(TODAY(-30) <= [Basic Explosive Safety]@row, "Green", (IF(TODAY(-60) <= [Basic Explosive Safety]@row, "Yellow", (IF(TODAY(-60) >= [Basic Explosive Safety]@row, "Red", "false")))))

So now it is checking if the date is before the deadline to be green and putting the green dot if it is. If it is not green it checks whether it before the deadline to be yellow, and puts a yellow dot if true, if it is after the date for yellow it puts red. Blank is also included as being "after".

So, we need to put the gray part back in, at the start, like this, and I also removed the word "false" putting in "" instead - although I can't see a situation this would ever be used with this logic.

=IF(ISBLANK([Basic Explosive Safety]@row), "Gray", (IF(TODAY(-30) <= [Basic Explosive Safety]@row, "Green", (IF(TODAY(-60) <= [Basic Explosive Safety]@row, "Yellow", (IF(TODAY(-60) >= [Basic Explosive Safety]@row, "Red", "")))))))

And then we get:

There is a potential 5th issue that your date column is not set as a "Date" Column Type. That is the only reason I can think your gray might have worked. ❓️

Hope this helps!

tracywalpole

@KPH WHEW! I think combining your suggestions and changing a few things on how I have the sheet laid out we got there. I really appreciate you taking the time to help me step through all of this.

I knew the = vs the <>= was needing to be changed. I don't know why the ISBLANK initially didn't work for me, but did eventually... sometimes formulas can really trip you up huh?

I ended up thinking it was asking the formula to do too much to calculate not only the expiration vs the day you look at the sheet and compare so I added another column to calculate the expiration date and then base the "is it expired/red, is it 30 days from expiration/yellow, and is it 60 days from expiration/green" on the expiration date column instead. I did a lot of testing and it seems to work now and shows the right colors when it should and blank when it's meeting none of the requirements! WOOO!!

Anyway, this is the formula and layout I landed on in case anyone else wants to set something up similar! Thank you again! This has been a true labor of love LOL

Formula:

=IF(ISBLANK([Basic Explosive Safety]@row), "Gray", (IF(TODAY() >= [Basic Explosive Safety Expiration]@row, "Red", (IF(TODAY() >= [Basic Explosive Safety Expiration]@row - 30, "Yellow", (IF(TODAY() >= [Basic Explosive Safety Expiration]@row - 60, "Green", "")))))))