Formula tricky

[vaibhav6666]

I have 2 Columns

1, Stage
2, Renewal Date ranging 120 days period
Stage column has below values
1.Not Engaged
2. Engaged
3. Negotiation
4. Proposal Sent
5, Order received
6. Proposal sent
7. Renewal Lost
8. Renewal Won

How do i build this rule in excel? Any help ,greatly appreciated .

• Dark green: closed early
  • Stage = (renewal won or renewal lost), and
  • Renewal date = future 90 days
• Light green: closed
  • Stage = (renewal won or renewal lost), and
  • Renewal date = past 30 days
• Yellow: late stage
  • (Stage = (order received or Negotiation), and
  • Renewal date = future 90 days)
  • or
  • (Stage = proposal sent, and
  • Renewal date = 45-90 days in the future)
  • or
  • 
    
  • (Stage = engaged, and
  • Renewal date = 60-90 days in the future)
• Orange: early stage
  • (Stage = proposal sent and
  • Renewal date = 15-30 days in the future)
  • or
  • (Stage = engaged, and
  • Renewal date = 30-45 days in the future)
  • 
    
  • or
  • (Stage = not engaged, and
  • Renewal date = 45-90 days in the future)
  • 
    
  • or
  • (Stage = (negotiation or order received), and
  • Renewal date = 0-15 days in the past)
• Light red: overdue
  • 
    
  • (Stage = not engaged and
  • Renewal date = 0-30 days in the future)
  • or
  • (Stage = engaged, and
  • Renewal date = 0-15 days in the future)
  • or
  • (Stage = proposal sent, and
  • Renewal date = 45-90 days in the future)
  • 
    
  • or
  • (Stage = (negotiation or order received), and
  • Renewal date = 15-30 days in the past)
• Dark red: overdue
  • Stage = (not engaged or engaged or proposal sent), and
  • Renewal date = 0-30 days in the past

 

[Candyman8019]

It sounds like you want to set up a bunch of conditional formatting rules based on a formula similar to the following:
=AND($A$2="Won", $B$2>=TODAY()+90)

 

[vaibhav6666]

What if we ignore colour coding for now and just want values returned as
" Closed early" or "Overdue" How would a formula look like then?

 

[Fluff]

Cross posted stuck in If Formula nested

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.