Excel conditional formula for due dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2104192%22%20slang%3D%22en-US%22%3EExcel%20conditional%20formula%20for%20due%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2104192%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20very%20new%20to%20formulas%20in%20excel%20and%20are%20trying%20to%20set%20up%20an%20excel%20sheet%20to%20help%20me%20with%20work.%20What%20I%20am%20trying%20to%20do%20is%20create%20a%20formula%20that%20will%20change%20the%20fill%20color%20of%20a%20cell%20if%20the%20value%20(date)%20of%20the%20cell%20is%20within%20X%20amount%20of%20days%20of%20the%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Justalex123_0-1611790924941.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F250098iCFC86A9C2B90A956%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Justalex123_0-1611790924941.png%22%20alt%3D%22Justalex123_0-1611790924941.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20example%2C%20for%20this%20particular%20column%2C%20these%20are%20dates%20that%20something%20is%20due.%20What%20I%20would%20like%20to%20happen%20is%20for%20excel%20to%20recognize%20that%20this%20date%20is%20within%2060%20days%20of%20its%20due%20date%20and%20turn%20the%20cell(s)%20that%20are%20within%20that%2060%20day%20window%20red.%20If%20they%20are%20within%2060%20and%2090%20days%2C%20turn%20the%20cell(s)%20yellow%2C%20if%20they%20are%20over%2090%20days%20till%20due%20date%2C%20turn%20them%20blue.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20pretty%20sure%20this%20is%20possible%2C%20but%20I%20am%20not%20sure%20how%20to%20do%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20much%20appreciate.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2104192%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-2104445%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20conditional%20formula%20for%20due%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2104445%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F945356%22%20target%3D%22_blank%22%3E%40Justalex123%3C%2FA%3E%26nbsp%3BWhat%20you%20are%20looking%20for%20is%20Conditional%20Formatting.%20The%20first%20step%20is%20to%20create%20a%20new%20column%20next%20to%20your%20RFI%20Due%20column%20that%20calculates%20the%20remaining%20number%20of%20days.%20The%20formula%20to%20track%20real-time%20daily%20updates%20would%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DH2-TODAY()%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20there%2C%20you%20select%20the%20new%20column%20and%20apply%20a%20Conditional%20Format%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adversi_1-1611797890700.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F250128i242102BF9ECFCD7B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22adversi_1-1611797890700.png%22%20alt%3D%22adversi_1-1611797890700.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFrom%20there%20you%20can%20control%20the%20control%20the%20cell%20format%20based%20on%20the%20condition%20you%20select%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adversi_2-1611797946725.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F250129iE9C52312B23B9C4D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22adversi_2-1611797946725.png%22%20alt%3D%22adversi_2-1611797946725.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERinse%20%26amp%3B%20Repeat%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adversi_3-1611797991112.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F250130iD21F8AAA30BF4487%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22adversi_3-1611797991112.png%22%20alt%3D%22adversi_3-1611797991112.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

 

I am a very new to formulas in excel and are trying to set up an excel sheet to help me with work. What I am trying to do is create a formula that will change the fill color of a cell if the value (date) of the cell is within X amount of days of the value. 

 

Justalex123_0-1611790924941.png

 

So for example, for this particular column, these are dates that something is due. What I would like to happen is for excel to recognize that this date is within 60 days of its due date and turn the cell(s) that are within that 60 day window red. If they are within 60 and 90 days, turn the cell(s) yellow, if they are over 90 days till due date, turn them blue. 

 

I am pretty sure this is possible, but I am not sure how to do it.

 

Any help is much appreciate.

2 Replies

@Justalex123 What you are looking for is Conditional Formatting. The first step is to create a new column next to your RFI Due column that calculates the remaining number of days. The formula to track real-time daily updates would be:

=H2-TODAY()

 

From there, you select the new column and apply a Conditional Format

adversi_1-1611797890700.png

From there you can control the control the cell format based on the condition you select:

adversi_2-1611797946725.png

 

Rinse & Repeat

adversi_3-1611797991112.png

 

 

 

@Justalex123 

Here is a file that you could set according to your needs.

We always recommend uploading a file (without sensitive data) to your example.

So anyone who would like to help can submit a proposed solution to you much faster.

 

If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.

 

Wish you a nice day / night with lots of health, joy and love.

 

Nikolino

I know I don't know anything (Socrates)