• 409K Members
• 11.5K Online
• 466K Conversations

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

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
12 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies