Conditional Formatting Date Ranges

[LeighMacKay7]

Hi all,
I am hoping you can help me.

I have a number of dates displayed dd/mm/yyyy hh:mm, which I need to add some conditional formatting too the column.

What I would like, is a formula which includes =TODAY() and helps me with the following.

> Between todays date and 5 months ago = GREEN text
> Greater than 5 months ago and less than and equal to 6 months = AMBER text
> Greater than 6 months = RED text.
There is also a 4th option, which is text based and says "No Staff Information Form", which would need to be displayed in another colour text (maybe purple?)

I have messed about in Conditional Formatting and have had no joy.
New Formatting Rule > Use a formula to determine which cells to format.=DATEDIF(TODAY(),T2:T1000,”m”)<=5



I have watched a couple of videos but have been unsuccesful.

Any help/pointers would be greatly received.

 

[Joe4]

A few things when writing Conditional Formatting Rules.

1. Do not use slanted double-quotes in your formula, i.e.
”m”
should be:
"m"

2. When writing the formula, select the range you want to apply the rule to, and then write the rule as it applies to the single activecell (usually the first cell in your selected range). Excel will adjust it automatically for all the other cells. So if you have selected rows 2 down to 1000, you should write the formula like:
=DATEDIF(TODAY(),$T2,"m")<=5

 

[LeighMacKay7]

A few things when writing Conditional Formatting Rules.

1. Do not use slanted double-quotes in your formula, i.e.
”m”
should be:
"m"

2. When writing the formula, select the range you want to apply the rule to, and then write the rule as it applies to the single activecell (usually the first cell in your selected range). Excel will adjust it automatically for all the other cells. So if you have selected rows 2 down to 1000, you should write the formula like:
=DATEDIF(TODAY(),$T2,"m")<=5

Thank you Joe4.
Much appreciated.

I think I am making progress with EDATE.

I just need the Amber text to display Greater than 5 months ago and less than and equal to 6 months and then Green Text to display between todays date and 5 months ago

 

[Joe4]

You can use AND to combine two conditions in one (to do a "between" two values), i.e.
=AND(T2>some calc, T2<some calc)

 

[LeighMacKay7]

I understand the value of < and > . however, I am not sure how I work this with dates which are constantly moving from Todays date.

 

[Joe4]

I understand the value of < and > . however, I am not sure how I work this with dates which are constantly moving from Todays date.

I am not sure I follow what you are saying. Using the TODAY function in your formulas/calculations, like you did in your posts takes care of that. It makes your formulas dynamic.
So what precisely is still tripping you up?

 

[LeighMacKay7]

Sorry, I am not following.

Basically, what I am saying is. I don't know what formula's to use. For both Amber (Between 5 months and 6 months) and then anything less than 5 months.

Which I have circled in red in the screenshot above.

 

[Joe4]

It is just like I said.

Amber:

=AND(T2>EDATE(TODAY(),-6),T2<EDATE(TODAY(),-5))

Green:

=T2<EDATE(TODAY(),-6)

Note that you will need to decide what to do if it is EXACTLY 5 months or 6 months, and change some ">" to ">=" and/or "<" to "<=" where necessary.

 

[awoohaw]

@LeighMacKay7 , here is a great YouTube tutorial on conditional formatting. Please take a look:

Below is what I think you asked for, although I'm changing fill and not text. It inlcudes a column checking for staff data, but not entirely sure how you calculate it:

Book1
	A	B	C	D	E	F
1		Staff Info	<=5Month	<=6 &>5	>6Month	No Staff
2	2023-01-20	Yes	FALSE	FALSE	TRUE	FALSE
3	2023-02-20	no	FALSE	FALSE	TRUE	TRUE
4	2023-03-20	no	FALSE	FALSE	TRUE	TRUE
5	2023-04-20	no	FALSE	FALSE	TRUE	TRUE
6	2023-05-20	Yes	FALSE	FALSE	TRUE	FALSE
7	2023-06-20	Yes	FALSE	FALSE	TRUE	FALSE
8	2023-07-20	Yes	FALSE	FALSE	TRUE	FALSE
9	2023-08-20	Yes	FALSE	TRUE	FALSE	FALSE
10	2023-09-20	Yes	TRUE	FALSE	FALSE	FALSE
11	2023-10-20	Yes	TRUE	FALSE	FALSE	FALSE
12	2023-11-20	Yes	TRUE	FALSE	FALSE	FALSE
13	2023-12-20	No	TRUE	FALSE	FALSE	TRUE
14	2024-01-20	No	TRUE	FALSE	FALSE	TRUE
15	2024-02-20	Yes	TRUE	FALSE	FALSE	FALSE
Sheet9

Cell Formulas
Range	Formula
C2:C15	C2	=$A2>=EDATE(TODAY(),-5)
D2:D15	D2	=AND($A2>=EDATE(TODAY(),-6),$A2<EDATE(TODAY(),-5))
E2:E15	E2	=$A2<EDATE(TODAY(),-6)
F2:F15	F2	=$B2="No"

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F15	Expression	=$B2="No"	text	NO
A2:F15	Expression	=$A2<EDATE(TODAY(),-6)	text	NO
A2:F15	Expression	=AND($A2>=EDATE(TODAY(),-6),$A2<EDATE(TODAY(),-5))	text	NO
A2:F15	Expression	=$A2>=EDATE(TODAY(),-5)	text	NO

 

[LeighMacKay7]

@LeighMacKay7 , here is a great YouTube tutorial on conditional formatting. Please take a look:

Below is what I think you asked for, although I'm changing fill and not text. It inlcudes a column checking for staff data, but not entirely sure how you calculate it:

Book1
	A	B	C	D	E	F
1		Staff Info	<=5Month	<=6 &>5	>6Month	No Staff
2	2023-01-20	Yes	FALSE	FALSE	TRUE	FALSE
3	2023-02-20	no	FALSE	FALSE	TRUE	TRUE
4	2023-03-20	no	FALSE	FALSE	TRUE	TRUE
5	2023-04-20	no	FALSE	FALSE	TRUE	TRUE
6	2023-05-20	Yes	FALSE	FALSE	TRUE	FALSE
7	2023-06-20	Yes	FALSE	FALSE	TRUE	FALSE
8	2023-07-20	Yes	FALSE	FALSE	TRUE	FALSE
9	2023-08-20	Yes	FALSE	TRUE	FALSE	FALSE
10	2023-09-20	Yes	TRUE	FALSE	FALSE	FALSE
11	2023-10-20	Yes	TRUE	FALSE	FALSE	FALSE
12	2023-11-20	Yes	TRUE	FALSE	FALSE	FALSE
13	2023-12-20	No	TRUE	FALSE	FALSE	TRUE
14	2024-01-20	No	TRUE	FALSE	FALSE	TRUE
15	2024-02-20	Yes	TRUE	FALSE	FALSE	FALSE
Sheet9

Cell Formulas
Range	Formula
C2:C15	C2	=$A2>=EDATE(TODAY(),-5)
D2:D15	D2	=AND($A2>=EDATE(TODAY(),-6),$A2<EDATE(TODAY(),-5))
E2:E15	E2	=$A2<EDATE(TODAY(),-6)
F2:F15	F2	=$B2="No"

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F15	Expression	=$B2="No"	text	NO
A2:F15	Expression	=$A2<EDATE(TODAY(),-6)	text	NO
A2:F15	Expression	=AND($A2>=EDATE(TODAY(),-6),$A2<EDATE(TODAY(),-5))	text	NO
A2:F15	Expression	=$A2>=EDATE(TODAY(),-5)	text	NO

Thank you. This has been a great help. And has helped me achieve what I set out to do