Excel - conditional formatting help

%3CLINGO-SUB%20id%3D%22lingo-sub-1711330%22%20slang%3D%22en-US%22%3EExcel%20-%20conditional%20formatting%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1711330%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Please%20help%2C%3C%2FP%3E%3CP%3EI%20have%20created%20a%20spreadsheet%20and%20have%20formulas%20when%20a%20date%20is%20enter%20it%20projects%2030%20day%20and%2090%20days%20into%20their%20respective%20columns.%20I%20would%20like%20to%20conditional%20format%20these%20columns%20to%20reflect%20when%20the%20projected%20dates%20are%20coming%20up%20to%20being%20due%20e.g.%205%20days%2C%20and%20over%20due.%20I%20have%20been%20using%20the%20%22today%22%20formula%20and%20have%20now%20realised%20this%20is%20not%20correct.%20PLEASE%20HELP!%3C%2FP%3E%3CP%3EThanks%20heaps%3C%2FP%3E%3CP%3ERosemarie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1711330%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-1711737%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20conditional%20formatting%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1711737%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F159495%22%20target%3D%22_blank%22%3E%40salway42%3C%2FA%3E%26nbsp%3BDifficult%20to%20be%20sure%20without%20seeing%20your%20file%2C%20but%20a%20CF%20rule%20highlighting%20cells%20where%20the%20value%20is%20less%20than%20TODAY(%20)%2B5%20should%20give%20you%20the%20desired%20outcome.%20It%20marks%20all%20passed%20dates%20and%20the%20ones%20for%20the%20coming%205%20days.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-09-25%20at%2008.14.37.png%22%20style%3D%22width%3A%20951px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F222026iB79899540F7F7D34%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot%202020-09-25%20at%2008.14.37.png%22%20alt%3D%22Screenshot%202020-09-25%20at%2008.14.37.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E(Picture%20taken%20on%20a%20Mac%2C%20but%20similar%20on%20PC)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1711766%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20conditional%20formatting%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1711766%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20I%20have%20snipped%20a%20copy%20of%20my%20spreadsheet%20to%20provide%20further%20context.%3C%2FP%3E%3CP%3EAre%20you%20saying%20I%20can%20use%20the%20%22%3Dtoday()%22%20formula%20even%20though%20the%20dates%20do%20not%20have%20anything%20to%20do%20with%20today%2C%20but%20the%20actual%20date%20in%20the%20column%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi Please help,

I have created a spreadsheet and have formulas when a date is enter it projects 30 day and 90 days into their respective columns. I would like to conditional format these columns to reflect when the projected dates are coming up to being due e.g. 5 days, and over due. I have been using the "today" formula and have now realised this is not correct. PLEASE HELP!

 

Here is a snip of the sheet to provide further context;

salway42_0-1601015472207.png

Column C's date is plus ninety days in Column F

Column E is +30 days from column D

I need column's E & F  to show green 5 days prior to the projected date in these columns and red once the projected date passes (overdue).

Thanks heaps

Rosemarie

4 Replies
Highlighted

@salway42 Difficult to be sure without seeing your file, but a CF rule highlighting cells where the value is less than TODAY( )+5 should give you the desired outcome. It marks all passed dates and the ones for the coming 5 days.

Screenshot 2020-09-25 at 08.14.37.png

(Picture taken on a Mac, but similar on PC)

Highlighted

@Riny_van_Eekelen 

 

Thank you, I have snipped a copy of my spreadsheet to provide further context.

Are you saying I can use the "=today()" formula even though the dates do not have anything to do with today, but the actual date in the column?

Highlighted

@salway42 Sorry, but I don't follow your dates here. You mention that column F = C plus 90 days, but I guess you mean column G = C plus 90 days? You are using a date format d/mm/yyyy aren't you?

Similarly, you say that E = D plus 30 days. I guess, you mean F = E plus 30 days. And where do I find the "projected date"? And is it E and F you want to conditionally format, or perhaps F and G or G and H?

 

Any how, you should create two rules. One that checks if the cell value is between the "projected date" minus 5 days and the "projected date" (green). Another rule needs to check if the cell value is equal or greater than the "projected date" (red).

Highlighted
Ok thanks a bundle