Adding formulas together / Invalid Data Type

Pamela Wagner

While I can get the formulas to work on their own I can never figure out how to pull them together in one string. This time, I think it might be close but it's returning an Invalid Data Type which makes no sense as I know that the column that the formula is in is RYGG which is what I'm asking it to return.

My use case is:

Check the Expired column first, if that’s checked then you don’t need to go on, return null

Check the Renewal Process column next, if that is checked then you don’t need to go on, return null

Then check the dates for the criteria on RYGG

I only want a return of RYGG if the contract is either not expired or the renewal process has not completed.

The formula that I'm using is: =IF([Renewal Process Complete]@row = 1, "", IF(AND(Expired@row = 1, ""), IF(AND([Expire Date]@row >= TODAY(+91), [Expire Date]@row < TODAY(+120)), "Gray")))

I don't understand why I'm getting an Invalid Data Type since the column is RYGG and each of these formulas work independently.

Paul Newcome

I see the problem. I accidentally used "Expired Date" instead of "Expire Date". My apologies. The third cell reference just needs the column name fixed, and it should be working for you.

Sorry about that.

Paul Newcome

You have some extra parenthesis and you don't need that first AND statement. Let's start by cleaning some of that up and seeing what happens...

=IF([Renewal Process Complete]@row = 1, "", IF(Expired@row = 1, "", IF(AND([Expire Date]@row >= TODAY(+91), [Expire Date]@row < TODAY(+120)), "Gray")))

Pamela Wagner

Paul, looks like that did it. It was really confusing since it gave me, what I feel, was the wrong error of Invalid Data Type. I did think it was wrong but by not giving me the right error, it sent me down the wrong rabbit hole. I tried all sorts of iterations with the parentheses and the AND but apparently didn't hit the right combo. I think I'm pretty close to having my project set and once I can look up from this will do some training on formulas and how to combine them. That seems to be my sticking point. While I had basic knowledge going into this project, I feel like a freshman in a senior level course! I very much appreciate your help!

Perhaps it's a matter of understanding how to read them in addition to rote creating them. Am I correct in this interpretation:

=IF([Renewal Process Complete]@row= 1, "", IF(Expired@row = 1, "", IF(AND([Expire Date]@row >= TODAY(+91), [Expire Date]@row < TODAY(+120)), "Gray")))

If Renew Process is checked, return null, if not then check if Expired is checked, return null, if not then check the dates of Over 91 and under 120. (I used italics for reading emphasis since they can also be part of the formula).

Paul Newcome

The Invalid Data Type error was coming from this portion...

AND(Expired@row = 1, "")

The AND function requires logical statements such as your first argument of "Expired@row = 1". "" is not a logical statement, so it is throwing the error as a result.

Your interpretation of the formula is correct. The basics of nesting IF statements is that the next one goes in the previous one's "else" portion.

Pamela Wagner

Thanks! One last question (hah! right!) - when I copied the formula down through my sheet, I'm getting gray balls appearing with this particular formula when there is no data in those rows:

=IF([Renewal Process Complete]@row = 1, "", IF(Expired@row = 1, "", IF([Expire Date]@row < TODAY(), "Gray")))

Do I need to add something at the end to tell it not to return something? I am not getting this with the other formulas that are set up the same way.

Paul Newcome

It is because a blank in a date column is considered less than TODAY(). Try this...

=IF([Renewal Process Complete]@row = 1, "", IF(Expired@row = 1, "", IF(AND([Expired Date]@row <> "", [Expire Date]@row < TODAY()), "Gray")))

Pamela Wagner

Well that's just silly! Unfortunately it returned an Unparseable error (my nemesis).

Paul Newcome

Can you copy/paste the exact formula from the cell to here.

Pamela Wagner

Here is the one that is returning the gray:

=IF([Renewal Process Complete]@row = 1, "", IF(Expired@row = 1, "", IF([Expire Date]@row < TODAY(), "Gray")))

and yours:

=IF([Renewal Process Complete]@row = 1, "", IF(Expired@row = 1, "", IF(AND([Expired Date]@row <> "", [Expire Date]@row < TODAY()), "Gray")))

Paul Newcome

I meant are you able to copy/paste from the sheet itself? Or possibly take a screenshot showing the formula in the sheet like below?

Pamela Wagner

Paul Newcome

I see the problem. I accidentally used "Expired Date" instead of "Expire Date". My apologies. The third cell reference just needs the column name fixed, and it should be working for you.

Sorry about that.

Pamela Wagner

Yup, that did it. I know I was not consistent in my terminology. Would have loved to use Closed, but finance liked Expired instead.

Paul Newcome

Glad we got it working! 👍️