Forum Discussion
Solved
Conditional Formatting between months
Hi
I'm struggling with a formula to change the colour on forecast cells.
The dates in the milestone cells will not change once entered, while the forecast months could change. So if the date in the forecast cell is one month later I want the cell to turn green, if its two months later then amber, if three months later then red.
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!
3 Replies
Perfect!!!!
Thank you!!
I assume under the dates you mean actual dates, not texts, (e.g. 2025-03-31) formatted as "mmm yy".
If so rules could be
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!
