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
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

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

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies