Conditional Formatting - Multiple Formulas Please Help

Copper 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

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).

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.

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

 

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")

The initial part makes me uncomfortable:

=IF(3,

check it out, I think was the same as the other part, like:

=IF(AND($J2=3

then fix brackets ;)