• 549K Members
• 6,745 Online
• 657K Conversations

Highlighted
Occasional Contributor

Hello everyone,

I am trying to create several conditions for an Excel I am working on but I am struggling to make them work.

• If column J is "3" and column H has a date that is 2 years old, then put "Past Due" in column I
• If column J is "2" and column H has a date that is 1 years old, then put "Past Due" in column I
• If column J is "3" and column H has a date that is less than 2 years old, then put "Current" in column I
• If column J is "2" and column H has a date that is less than 1 years old, then put "Current" in column I
• If Column I is "Past Due" highlight column I, H and A

Could anyone assist me with this? Thank you

5 Replies
Highlighted

Try what I did, in attachement.

Tell me if that's what you need and if so I'll explain something you need (in case you need).

Highlighted

I believe that this will work but is there a way to program it into the conditional formatting instead of setting it up as a function tied to a cell? I am creating a template that will be used monthly with different data.

Highlighted

I messed around with the formula a bit because I wasn't able to make it work at first for my document. However now when I try to use it, it will only work for items that are tier 3. Do you see something that I did wrong?

=IF(3,IF(AND(\$J2=3,\$H2<TODAY()-730),"Past Due","Current"),IF(AND(\$J2=2,\$H2<TODAY()-360),"Past Due","Current") IF(AND(\$J2=1,\$H2<TODAY()-360),"Past Due","Current"))

Thank you

Highlighted

Hello Courtney,
Enter this formula in Column I, starting in I2:
=IF(OR(AND(TODAY()>=EDATE(H2,24),J2=3),
AND(TODAY()>=EDATE(H2,12),J2=2)),
"Past Due","Current")
Highlighted