Conditional formatting of dates

[BIGPHILL]

I have a date column in my spreadsheet. I want the date in the cell to go Red if the date has come and gone, amber if the current month and green if the date is ahead.

I know there is conditional formatting, however it only allows me to choose next month to go green, and last month to go red, not all the other months for example 2 months in advance.


Anyone have any suggestions for me on how to do this.

Thanks

 

[etaf]

=and ( g5<today()-31
< </today()-31
TODAY()-31<today()-31
, g5 < > "" )</today()-31

 

[BIGPHILL]

Hello All,Last little bit of help needed by me,
Can someone tell me the formula for what i need below.

When i enter a date and for the duration within 9 months i would like cell to it go green
When i enter a date and for the duration when its 9 12 months old then to go amber
When i enter a date and duration of 1 year passes then to go Red.Thank you all for your help.

 

[etaf]

three rules
1 for red, 1 for green and 1 for amber

use date()

assuming the date is entered into G5 again

Put in this ORDER - you use an arrow button on the conditional formatting to change the order
and USE STOP if true

RED rule
=AND(G5 < > "",G5 < DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))

Amber
=AND(G5 < > "",G5 < DATE(YEAR(TODAY()),MONTH(TODAY())-9,DAY(TODAY())))

Green = less than today
=AND(G5 < > "",G5 < DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))

If the date is in the future no colour applied

 

[etaf]

Excel Workbook
	G
5	11/01/2017
6	11/07/2017
7	11/02/2017
8	11/02/2018
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G5	1. / Formula is =AND(G5<>"",G5	Abc
G5	2. / Formula is =AND(G5<>"",G5	Abc
G5	3. / Formula is =AND(G5<>"",G5	Abc
G6	1. / Formula is =AND(G5<>"",G5	Abc
G6	2. / Formula is =AND(G5<>"",G5	Abc
G6	3. / Formula is =AND(G5<>"",G5	Abc
G7	1. / Formula is =AND(G5<>"",G5	Abc
G7	2. / Formula is =AND(G5<>"",G5	Abc
G7	3. / Formula is =AND(G5<>"",G5	Abc
G8	1. / Formula is =AND(G5<>"",G5	Abc
G8	2. / Formula is =AND(G5<>"",G5	Abc
G8	3. / Formula is =AND(G5<>"",G5	Abc

 

[BIGPHILL]

Thank you for your reply, I tried the above Formula in G5 and i have error message come up ...Forumla you have typed contains an error. Is this because i just copied it across?

Many thanks for your help.

• RED rule
  =AND(G5 < > "",G5 < DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))
  
  Amber
  =AND(G5 < > "",G5 < DATE(YEAR(TODAY()),MONTH(TODAY())-9,DAY(TODAY())))
  
  Green = less than today
  =AND(G5 < > "",G5 < DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))
  
  ​
  
  
  
  

 

[etaf]

probably
BUT I have to add spaces to stop the forum from using the code as HTML

[COLOR=#333333]RED rule[/COLOR]
[COLOR=#333333]=AND(G5<>"",G5<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))[/COLOR]

[COLOR=#333333]Amber[/COLOR]
[COLOR=#333333]=AND(G5<>"",G5<DATE(YEAR(TODAY()),MONTH(TODAY())-9,DAY(TODAY())))[/COLOR]

[COLOR=#333333]Green = less than today[/COLOR]
[COLOR=#333333]=AND(G5<>"",G5<DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))[/COLOR][/CODE

 

[BIGPHILL]

If the date is in the future no colour applied

Thank you for your help, really appreciate it. If i was to enter a date in the future, can i set this to be green too.

 

[etaf]

for green use
=AND(G5< > "",G5>=DATE(YEAR(TODAY()),MONTH(TODAY())-9,DAY(TODAY())))

 

[BIGPHILL]

Thank you, all worked.

Lastly (This is last bit i promise)

What would be the formula for the same principle but for different time scales. Its for different spreadsheets, i finished the 1 year one, but now i have to do the same but for 2, 3 and 5 years on each separate sheet

Required.....


When i enter a date and for the duration within 21 months i would like cell to it go green
When i enter a date and for the duration when its 21 - 24 months old then to go amber
When i enter a date and duration of 2 year passes then to go Red.
Also all future dates to go green

AND

When i enter a date and for the duration within 33 months i would like cell to it go green
When i enter a date and for the duration when its 33 - 36 months old then to go amber
When i enter a date and duration of 3 year passes then to go Red.
Also all future dates to go green

AND

When i enter a date and for the duration within 57 months i would like cell to it go green
When i enter a date and for the duration when its 57 - 60 months old then to go amber
When i enter a date and duration of 5 year passes then to go Red.
Also all future dates to go green

Thank you in advance

 

[BIGPHILL]

I sorted it, no help no longer needed. Many Thanks