SOLVED

Nested IF with a number of days

Copper Contributor

I am trying to have cells in one column turn a color with a message when the date is within the below parameters (I just need help with the formula, not the colors). I know I am missing a piece of the formula, but cannot figure out how to nest it.

 

If the publication date (column G) is:

 

  • Between 0 and 304 days, show In Compliance (I was able to do this)
  • Between 305 and 365 days, show Warning! (this is what I can’t figure out)
  • More than 365 days, show Review Required (I was able to do this)
  • If it is a new document that hasn’t yet been published, show TBD (I was able to do this)
6 Replies

@Rnajar Try it this way:

=LOOKUP(DAYS360(G6,TODAY()),{0,305,366},{"In Compliance","Warning","Review Required"})

 

best response confirmed by Rnajar (Copper Contributor)
Solution
Try this:
=IF(G6="TBD","TBD",LOOKUP(TODAY()-G6,{0,305,366},{"In Compliance","Warning","Review Required"}))

@JMB17 Minds alike, though I forgot about the "TBD" part of the formula.

I just caught it myself before posting.
IT WORKED!!!! THANK YOU SO MUCH. I have spent hours on this.
You are welcome - that is good to hear.
1 best response

Accepted Solutions
best response confirmed by Rnajar (Copper Contributor)
Solution
Try this:
=IF(G6="TBD","TBD",LOOKUP(TODAY()-G6,{0,305,366},{"In Compliance","Warning","Review Required"}))

View solution in original post