Forum Discussion
Conditional Formatting between months
SimonP2000It sounds like you need a way to highlight the Forecast dates based on how far they are from the Milestone dates. Since the Milestone dates won’t change, we can set up some conditional formatting to show whether the Forecast is slipping.
If the Forecast date is 1 month later than the Milestone date, the cell should turn green (low risk).
If it's 2 months later, it should turn amber (medium risk).
If it's 3 months or more later, it should turn red (high risk).
Select the Forecast column (e.g., B5:B19 or expand if needed).- Go to Home > Conditional Formatting > New Rule.
- Choose "Use a formula to determine which cells to format".
- Enter these formulas one at a time, setting the colours as you go:
Green (1 month later)
=DATEDIF(C5,B5,"M")=1
Amber (2 months later)=DATEDIF(C5,B5,"M")=2
Red (3 months or more later)=DATEDIF(C5,B5,"M")>=34. Click OK, and you're done!
SimonP2000It sounds like you need a way to highlight the Forecast dates based on how far they are from the Milestone dates. Since the Milestone dates won’t change, we can set up some conditional formatting to show whether the Forecast is slipping.
If the Forecast date is 1 month later than the Milestone date, the cell should turn green (low risk).
If it's 2 months later, it should turn amber (medium risk).
If it's 3 months or more later, it should turn red (high risk).
Select the Forecast column (e.g., B5:B19 or expand if needed).
- Go to Home > Conditional Formatting > New Rule.
- Choose "Use a formula to determine which cells to format".
- Enter these formulas one at a time, setting the colours as you go:
Green (1 month later)
=DATEDIF(C5,B5,"M")=1
Amber (2 months later)
=DATEDIF(C5,B5,"M")=2
Red (3 months or more later)
=DATEDIF(C5,B5,"M")>=34. Click OK, and you're done!