Home

Similar but different conditional formatting question

%3CLINGO-SUB%20id%3D%22lingo-sub-805279%22%20slang%3D%22en-US%22%3ESimilar%20but%20different%20conditional%20formatting%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805279%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20expiration%20dates%20that%20I%20would%20like%20to%20highlight%20either%20yellow%20if%20the%20due%20date%20is%20between%2031-60%20days%20out%20from%20the%20due%20date%20and%20red%20if%20it%20is%200-30%20days%20out%20from%20the%20due%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEverything%20I%20have%20seen%20for%20resolutions%20involves%20a%20TODAY()%20function%20and%20today's%20date%20doesn't%20make%20any%20difference%20in%20this%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20please%3F%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EBuffy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-805279%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805522%22%20slang%3D%22en-US%22%3ERe%3A%20Similar%20but%20different%20conditional%20formatting%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805522%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392389%22%20target%3D%22_blank%22%3E%40BuffyBriggs_1187%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20apply%20the%20following%20conditional%20formats%20as%20formulas%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EAssuming%20your%20date%20is%20in%20cell%20A2%20and%20your%20due%20date%20is%20in%20cell%20B2%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3EFor%20dates%20between%200%20and%2030%20from%20the%20due%20date%20(RED)%3A%3C%2FP%3E%3CP%3E%3DAND(B2-A2%26lt%3B%3D30%2CB2-A2%26gt%3B%3D0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20dates%20between%2031%20and%2060%20from%20the%20due%20date%20(YELLOW)%3A%3C%2FP%3E%3CP%3E%3DAND(B2-A2%26gt%3B%3D31%2CB2-A2%26lt%3B%3D60)%3C%2FP%3E%3CP%3E%3CEM%3EYou%20may%20then%20apply%20this%20conditional%20format%20to%20your%20range%20of%20cells.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3CBR%20%2F%3EPReagan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
BuffyBriggs_1187
Occasional Visitor

Hello all,

I have a spreadsheet with expiration dates that I would like to highlight either yellow if the due date is between 31-60 days out from the due date and red if it is 0-30 days out from the due date.

 

Everything I have seen for resolutions involves a TODAY() function and today's date doesn't make any difference in this spreadsheet.

 

Can anyone help please?

Thank you,

Buffy

1 Reply
Highlighted

Hello @BuffyBriggs_1187,

 

You could apply the following conditional formats as formulas:

 

Assuming your date is in cell A2 and your due date is in cell B2:

For dates between 0 and 30 from the due date (RED):

=AND(B2-A2<=30,B2-A2>=0)

 

For dates between 31 and 60 from the due date (YELLOW):

=AND(B2-A2>=31,B2-A2<=60)

You may then apply this conditional format to your range of cells.

 

Hope this helps!
PReagan