Conditional formatting for due dates?

brandonmcg

New Member
Joined
Jan 14, 2009
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
This is a project tracking spreadsheet.
There are hundreds of rows on this spreadsheet all starting row 2 of each column

Headers
Column F1 Actual Start Date
Column G1 Expected Completion Date
Column H1 Days Until Due
Column i1 Actual Completion Date

I am looking for a three tier color code combination
If i is not populated then in the i cell color code

If H1 15 Days or greater green
If H1 between 8 to 14 days yellow
If H1 is any negative number to 7 days red

If i1 is populated then no color. If this is too tricky then I can create a new column to house the color codes.

If i is populated with a date how do I not have a number in column H and no color?


1640717963942.png
 

Attachments

  • 1640717662804.png
    1640717662804.png
    9.8 KB · Views: 6
  • 1640717717861.png
    1640717717861.png
    10.4 KB · Views: 7

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi Brandonmcg,

I think your calculation for "Days Until Due" may be incorrect. Let me know if mine looks better.

You can use an IF to populate column H based on column I.

I am showing columns L, M and N because that's how I prepare for setting up a Conditional Format. It makes testing easier until you're happy then just paste the first formula into the CF.

Brandonmcg-3.xlsx
FGHIJKLMN
1Actual Start DateExpected Completion DateDays Until DueActual Completion DateTodayGreenYellowRed
201-Jan-2115-Jan-21-924-Jan-21FALSEFALSETRUE
301-Jan-2112-Feb-211924-Jan-21TRUEFALSEFALSE
402-Dec-2001-Jan-21 24-Dec-2024-Jan-21FALSEFALSEFALSE
528-Dec-2025-Feb-213224-Jan-21TRUEFALSEFALSE
628-Dec-2007-Feb-211424-Jan-21FALSETRUEFALSE
701-Jan-2127-Jan-21324-Jan-21FALSEFALSETRUE
8
Sheet1
Cell Formulas
RangeFormula
L2:L7L2=AND(H2<>"",H2>14)
M2:M7M2=AND(H2<>"",H2>=8,H2<=14)
N2:N7N2=AND(H2<>"",H2<8)
H2:H7H2=IF(I2>0,"",G2-J2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H2:H999Expression=AND(H2<>"",H2<8)textNO
H2:H999Expression=AND(H2<>"",H2>=8,H2<=14)textNO
H2:H999Expression=AND(H2<>"",H2>14)textNO
 
Upvote 0
Solution
Sorry for the delay with year-end and all it took me a few days to try out your recommendation and everything looks great and I really do appreciate your help!
 
Upvote 0
Sorry for the delay with year-end and all it took me a few days to try out your recommendation and everything looks great and I really do appreciate your help!
You're welcome and thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top