Conditional formatting dates

[BlueH1]

Hi all,

I need help with conditional formatting!
I am in the process of making a tracker used to keep track of when certain machines need maintenance.
Could someone help me out with creating a formula for conditional formatting that will highlight cells with dates in the month ahead, 2 months ahead and 3 months ahead.
Help would be highly appreciated!
Thank you

 

[Kaper]

Select a range to be formatted, like A2:H100 - you can do it including empty (now) rows and add rules based on formula. the rules shall be added in a sequence starting from the nearest. And all could have form:

=AND($C2<>"",today()+30>=$E2)

note that adresses are mixed - with absolute column and relative row number. So it will be applied to all cells in given row.
If your real data does not start from row 2 bot other select cells starting from this row, and change row in formula accordingly.

The final sequence of conditional formats shall look like this screenshot (ignore semicolons in formulas on screenshot. most of the world uses comma as argument separator)

Just in case - my proposition for E2 formula is:

=IF(C2="","",DATE(YEAR(C2),MONTH(C2)+D2,DAY(C2))

 

[BlueH1]

Select a range to be formatted, like A2:H100 - you can do it including empty (now) rows and add rules based on formula. the rules shall be added in a sequence starting from the nearest. And all could have form:

=AND($C2<>"",today()+30>=$E2)

note that adresses are mixed - with absolute column and relative row number. So it will be applied to all cells in given row.
If your real data does not start from row 2 bot other select cells starting from this row, and change row in formula accordingly.

The final sequence of conditional formats shall look like this screenshot (ignore semicolons in formulas on screenshot. most of the world uses comma as argument separator)

Just in case - my proposition for E2 formula is:

=IF(C2="","",DATE(YEAR(C2),MONTH(C2)+D2,DAY(C2))

View attachment 117233

Hi,

Thank you so much for your response!
I have tried applying the rules in the correct order but I can't seen to work out what it is that I am doing wrong

I have also tried the other formula you provided and it doesn't seem to be working either

 

[Joe4]

Are you values in columns C and E really entered as dates, or as text?
One easy way to tell is select cell C2, and try changing the format to a different date format.
Does it change the way that cell C2 looks? If not, you have text entries and not valid date entries.

Also, in your version of Excel, does it uses commas or semi-colons to separate the different arguments in your formulas?

 

[Kaper]

2 questions:

Are dates in columns C and E "excel dates" - if you change format of these cells to General do they look like fourty-some thousand ...

45559​

(it's today in Exclel calendar)

Have you clicked Apply (after making your screenshot)?

Edit: Well, the third questions about semicolons not comma is probably also valid, but I suspect comma, as the currency is represented by GBP sign.

 

[BlueH1]

Are you values in columns C and E really entered as dates, or as text?
One easy way to tell is select cell C2, and try changing the format to a different date format.
Does it change the way that cell C2 looks? If not, you have text entries and not valid date entries.

Also, in your version of Excel, does it uses commas or semi-colons to separate the different arguments in your formulas?

both columns are date entries, and I have commas as argument separators. I mean I think so anyways as it gives me an error if I add semi colon

 

[BlueH1]

2 questions:

Are dates in columns C and E "excel dates" - if you change format of these cells to General do they look like fourty-some thousand ...

45559​

(it's today in Exclel calendar)

Have you clicked Apply (after making your screenshot)?

Edit: Well, the third questions about semicolons not comma is probably also valid, but I suspect comma, as the currency is represented by GBP sign.

Hey,

Both are dates, all rules have been applied to this worksheet and I tried the semi colon but it is giving me an error

 

[Kaper]

Try to evaluate these formulas in H2 I2 etc and check if all tey are really false?

 

[BlueH1]

Try to evaluate these formulas in H2 I2 etc and check if all tey are really false?

Sorry I don't know what you meant by evaluate in H2 I2, but I opened up a whole new file and still have not been able to get any results

 

[BlueH1]

I FOUND THE PROBLEM!!
My date settings are strange (not sure if this is a UK thing?) but I had to replace . with /

Thank you both for your help!