SOLVED

Nested IF with a number of days

%3CLINGO-SUB%20id%3D%22lingo-sub-2258089%22%20slang%3D%22en-US%22%3ENested%20IF%20with%20a%20number%20of%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2258089%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20have%20cells%20in%20one%20column%20turn%20a%20color%20with%20a%20message%20when%20the%20date%20is%20within%20the%20below%20parameters%20(I%20just%20need%20help%20with%20the%20formula%2C%20not%20the%20colors).%20I%20know%20I%20am%20missing%20a%20piece%20of%20the%20formula%2C%20but%20cannot%20figure%20out%20how%20to%20nest%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20publication%20date%20(column%20G)%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EBetween%200%20and%20304%20days%2C%20show%20%3CEM%3EIn%20Compliance%3C%2FEM%3E%20(I%20was%20able%20to%20do%20this)%3C%2FLI%3E%3CLI%3E%3CSTRONG%3E%3CEM%3EBetween%20305%20and%20365%20days%2C%20show%20%3C%2FEM%3E%3C%2FSTRONG%3E%3CSTRONG%3EWarning!%3CEM%3E%20(this%20is%20what%20I%20can%E2%80%99t%20figure%20out)%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3EMore%20than%20365%20days%2C%20show%20%3CEM%3EReview%20Required%3C%2FEM%3E%20(I%20was%20able%20to%20do%20this)%3C%2FLI%3E%3CLI%3EIf%20it%20is%20a%20new%20document%20that%20hasn%E2%80%99t%20yet%20been%20published%2C%20show%20TBD%20(I%20was%20able%20to%20do%20this)%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2258089%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2258180%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20IF%20with%20a%20number%20of%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2258180%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1018922%22%20target%3D%22_blank%22%3E%40Rnajar%3C%2FA%3E%26nbsp%3BTry%20it%20this%20way%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLOOKUP(DAYS360(G6%2CTODAY())%2C%7B0%2C305%2C366%7D%2C%7B%22In%20Compliance%22%2C%22Warning%22%2C%22Review%20Required%22%7D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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.