I need a formula that will format cells based on a number of criteria. This is for the loss control department of an insurance company. Column N has each policy's expiration date, O has premium, Q is the last date the customer was inspected, and S is loss ratio. If the customer has never been inspected, Q shows "00/00/00"
I have one formula working perfectly. It formats rows where the loss ratio (S) is over 100 and the client either has never been inspected or has not been inspected since the policy renewed:
=AND(OR($Q1="00/00/00",$Q1<($N1-365)),$S1>100)
I had a formula that formatted rows where the customer's premium payment (O) is over 25,000 and they have not been inspected in a year:
=AND((TODAY()-$Q1)>365,$O1>25000)
For some reason, when I add the OR operator to also include customers that have never been inspected, the formula does not seem to work:
=AND(OR($Q1="00/00/00",(TODAY()-$Q1)>365),$O1>25000)
Any suggestions on how to get this formula working?
Thanks in advance!
Kelsey
Bookmarks