IF AND statements looking at two columns

Pamela Wagner

I think I need an AND statement but not sure exactly how to do it.

I have a R/Y/G column that I need to have check 2 other columns to determine if it should be Red or Green. I would like to be notified if an invoice has not been paid. Current columns: 1) date of when the invoice is sent to Finance; 2) take col 1 and add 21 days to give it time to get through the system; 3) final column to note Red or Green if the invoice is paid or not based on the date in #s1 and 2.

If the Invoice Paid column is checked then return Green in column 3; if the Invoice Paid column is NOT checked and the Check Date column is over 21 days based on TODAY then it turns Red.

Here are my original formulas:

=IF([Invoice Paid Check]@row = 1, "Green")

=IF(TODAY() > [Invoice Paid Date Helper1]@row, "Red")

Pretty sure that to combine these I need to restate that if the Invoice Paid Check column is "" AND TODAY() > [Invoice Paid Date Helper1]@row, "Red". I just don't know how to do that.

Is this close??: = IF([Invoice Paid Check]@row = 1, "Green", IF(AND([Invoice Paid Check]@row = “”, TODAY() > [Invoice Paid Date Helper1]@row, "Red")))

I'm sure I'm making this way more complicated so if there is an easier way than all my extra columns I wouldn't mind hearing that otherwise, figuring out how to combine the formula into one cell for the correct Red or Green dot would be lovely!

Frank B.

To combine the conditions using the AND statement, you're on the right track. However, there are a couple of adjustments needed in the formula. The AND statement should include both conditions for the Invoice Paid Check column being empty and TODAY() being greater than the Invoice Paid Date Helper1. Also, you should use an empty string "" instead of "1" to represent an empty cell in the Invoice Paid Check column. Here's the corrected formula:

=IF([Invoice Paid Check]@row = "", IF(TODAY() > [Invoice Paid Date Helper1]@row, "Red"), "Green")

In this formula, if the Invoice Paid Check column is empty and TODAY() is greater than the Invoice Paid Date Helper1, it returns "Red". Otherwise, it returns "Green". This formula combines the two conditions and provides the desired result in a single cell.

As for the approach using extra columns, it can provide clarity and simplify the formula if you prefer a step-by-step breakdown of the logic. However, if you don't need the intermediary columns for other purposes, combining the formula into a single cell is a more efficient solution.

HTH!

Pamela Wagner

@Frank B. - thanks so much! I don't see and AND in the formula though. When I applied this to my sheet, it's returning all Green even when I remove the check mark for the Invoice being paid and played with the dates. Note that the "1" in my formulas was to indicate that the Invoice paid column was checked. I thought 1 = checked and "" = empty.

Frank B.

Sorry @Pamela Wagner try this:

=IF([Invoice Paid Check]@row = 1, "Green", IF(AND([Invoice Paid Check]@row = "", TODAY() > [Invoice Paid Date Helper1]@row), "Red", ""))

Let me know!