how to make highlights dependent on distance to today's date?

[Matthew82]

So I have cells populated by dates that show only month and year (I'll use Feb-24 as an example of just one cell). I want all these cells to be:

1. Highlighted in green if the the date is more than 3 calendar months away. For example Feb-24 would be green today (October 31, 2023).
2. Highlighted in yellow if the date is within 3 calendar months away. For example a date of Feb-24 will turn yellow as of tomorrow (because Feb 2024 - 3 months = Nov 2023)
3. Highlighted in red if the date is today or in the past. So in the above example, January 31st of 2024 the cell would be yellow but on February 1st it would turn red.

My issue is I don't understand formulas well enough to have anything more than a simple if/then formula and this must have all three conditions applied at once. I assume it is an easy conditional for someone with a bit more knowledge so any assistance you can provide would be very much appreciated!

 

[etaf]

is the entries an actual date formatted to show MMM-YY , or is it text you are entering ??

what date would be entered - for your example - any day in Feb entered - but display is FEB-24

3 rules in conditional formatting - 1 for each colour

what cells columns are you using

red
=and(cell<>"", Cell < today())

yellow
this is where the day in the cell may help -

 

[Matthew82]

is the entries an actual date formatted to show MMM-YY , or is it text you are entering ??

what date would be entered - for your example - any day in Feb entered - but display is FEB-24

3 rules in conditional formatting - 1 for each colour

what cells columns are you using

Good point on that question. The cell is just a formula displaying as a date. So if E5 is one date input manually with no conditioning applied whatsoever (and let's say it is Sep-23), then I want E6 to automatically spit out Nov-24 (14 months after the date in E5). I am currently doing this by setting E6 to be "=E5+430" with the display formatting set to show "Nov-24"

In this example, E6 is the cell where I want that conditioning applied. I want it to be highlighted in green from now until August 1st 2024, then yellow August 1st 2024 to October 31 2024, then red as of November 1st 2024.

 

[etaf]

as i say , day may be important here

i may be a bit confused on this - sorry

I have based everything on today - rather than wht may happen in a future date
so as of today - i have a column in E of various dates and coloured RED, AMBER , GREEN


but get the basics done

using column E as the cell with dates in , range E2:E10000
based on today() which is 31st oct 23

Red ,

any date in column E , less that today
so rule would be

=and(E2<>"", E2 < today())

yellow would be dates from today() 31st oct - to the End of the month - in 3 months time
(What if the date in E2 is 15-oct-23, would that be to 15-jan-24 or to the end of the month - 31-jan-24

I have used end of month , based on todays date based on 3 months ahead

amber
=AND(E2>=TODAY(),E2<EOMONTH(TODAY(),3))

green
=AND(E2>=TODAY(),E2>EOMONTH(TODAY(),3))

i have shown the TRUE and FALSE in columns F,G,H for the 3 colours - just for an indication

today() + 430 = thats the 26/11/24

Book5
	A	B	C	D	E	F	G	H
1	today	today + 430		actual date	shown as month year	RED	AMBER	GREEN
2				10/1/23	Oct-23	TRUE	FALSE	FALSE
3	9/23/23	11/26/24		10/10/23	Oct-23	TRUE	FALSE	FALSE
4				10/19/23	Oct-23	TRUE	FALSE	FALSE
5				10/28/23	Oct-23	TRUE	FALSE	FALSE
6				11/6/23	Nov-23	FALSE	TRUE	FALSE
7				11/15/23	Nov-23	FALSE	TRUE	FALSE
8				11/24/23	Nov-23	FALSE	TRUE	FALSE
9				12/3/23	Dec-23	FALSE	TRUE	FALSE
10				12/12/23	Dec-23	FALSE	TRUE	FALSE
11				12/21/23	Dec-23	FALSE	TRUE	FALSE
12				12/30/23	Dec-23	FALSE	TRUE	FALSE
13				1/8/24	Jan-24	FALSE	TRUE	FALSE
14				1/17/24	Jan-24	FALSE	TRUE	FALSE
15				1/26/24	Jan-24	FALSE	TRUE	FALSE
16				2/4/24	Feb-24	FALSE	FALSE	TRUE
17				2/13/24	Feb-24	FALSE	FALSE	TRUE
18				2/22/24	Feb-24	FALSE	FALSE	TRUE
19				3/2/24	Mar-24	FALSE	FALSE	TRUE
20				3/11/24	Mar-24	FALSE	FALSE	TRUE
21				3/20/24	Mar-24	FALSE	FALSE	TRUE
22				3/29/24	Mar-24	FALSE	FALSE	TRUE
23				4/7/24	Apr-24	FALSE	FALSE	TRUE
24				4/16/24	Apr-24	FALSE	FALSE	TRUE
25				4/25/24	Apr-24	FALSE	FALSE	TRUE
26				5/4/24	May-24	FALSE	FALSE	TRUE
27				5/13/24	May-24	FALSE	FALSE	TRUE
28				5/22/24	May-24	FALSE	FALSE	TRUE
29				5/31/24	May-24	FALSE	FALSE	TRUE
30				6/9/24	Jun-24	FALSE	FALSE	TRUE
31				6/18/24	Jun-24	FALSE	FALSE	TRUE
32				6/27/24	Jun-24	FALSE	FALSE	TRUE
33				7/6/24	Jul-24	FALSE	FALSE	TRUE
34				7/15/24	Jul-24	FALSE	FALSE	TRUE
35				7/24/24	Jul-24	FALSE	FALSE	TRUE
36				8/2/24	Aug-24	FALSE	FALSE	TRUE
37				8/11/24	Aug-24	FALSE	FALSE	TRUE
Sheet1

Cell Formulas
Range	Formula
F2:F37	F2	=AND(E2<>"",E2<TODAY())
G2:G37	G2	=AND(E2>=TODAY(),E2<EOMONTH(TODAY(),3))
H2:H37	H2	=AND(E2>=TODAY(),E2>EOMONTH(TODAY(),3))
B3	B3	=A3+430
D2:D37	D2	=E2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E37	Expression	=AND(E2>=TODAY(),E2>EOMONTH(TODAY(),3))	text	NO
E2:E37	Expression	=AND(E2>=TODAY(),E2<EOMONTH(TODAY(),3))	text	NO
E2:E37	Expression	=AND(E2<>"",E2<TODAY())	text	NO

i have also added to a dropbox share - will only be available for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Matthew82]

as i say , day may be important here

i may be a bit confused on this - sorry

I have based everything on today - rather than wht may happen in a future date
so as of today - i have a column in E of various dates and coloured RED, AMBER , GREEN


but get the basics done

using column E as the cell with dates in , range E2:E10000
based on today() which is 31st oct 23

Red ,

any date in column E , less that today
so rule would be

=and(E2<>"", E2 < today())

yellow would be dates from today() 31st oct - to the End of the month - in 3 months time
(What if the date in E2 is 15-oct-23, would that be to 15-jan-24 or to the end of the month - 31-jan-24

I have used end of month , based on todays date based on 3 months ahead

amber
=AND(E2>=TODAY(),E2<EOMONTH(TODAY(),3))

green
=AND(E2>=TODAY(),E2>EOMONTH(TODAY(),3))

i have shown the TRUE and FALSE in columns F,G,H for the 3 colours - just for an indication

today() + 430 = thats the 26/11/24

Book5
	A	B	C	D	E	F	G	H
1	today	today + 430		actual date	shown as month year	RED	AMBER	GREEN
2				10/1/23	Oct-23	TRUE	FALSE	FALSE
3	9/23/23	11/26/24		10/10/23	Oct-23	TRUE	FALSE	FALSE
4				10/19/23	Oct-23	TRUE	FALSE	FALSE
5				10/28/23	Oct-23	TRUE	FALSE	FALSE
6				11/6/23	Nov-23	FALSE	TRUE	FALSE
7				11/15/23	Nov-23	FALSE	TRUE	FALSE
8				11/24/23	Nov-23	FALSE	TRUE	FALSE
9				12/3/23	Dec-23	FALSE	TRUE	FALSE
10				12/12/23	Dec-23	FALSE	TRUE	FALSE
11				12/21/23	Dec-23	FALSE	TRUE	FALSE
12				12/30/23	Dec-23	FALSE	TRUE	FALSE
13				1/8/24	Jan-24	FALSE	TRUE	FALSE
14				1/17/24	Jan-24	FALSE	TRUE	FALSE
15				1/26/24	Jan-24	FALSE	TRUE	FALSE
16				2/4/24	Feb-24	FALSE	FALSE	TRUE
17				2/13/24	Feb-24	FALSE	FALSE	TRUE
18				2/22/24	Feb-24	FALSE	FALSE	TRUE
19				3/2/24	Mar-24	FALSE	FALSE	TRUE
20				3/11/24	Mar-24	FALSE	FALSE	TRUE
21				3/20/24	Mar-24	FALSE	FALSE	TRUE
22				3/29/24	Mar-24	FALSE	FALSE	TRUE
23				4/7/24	Apr-24	FALSE	FALSE	TRUE
24				4/16/24	Apr-24	FALSE	FALSE	TRUE
25				4/25/24	Apr-24	FALSE	FALSE	TRUE
26				5/4/24	May-24	FALSE	FALSE	TRUE
27				5/13/24	May-24	FALSE	FALSE	TRUE
28				5/22/24	May-24	FALSE	FALSE	TRUE
29				5/31/24	May-24	FALSE	FALSE	TRUE
30				6/9/24	Jun-24	FALSE	FALSE	TRUE
31				6/18/24	Jun-24	FALSE	FALSE	TRUE
32				6/27/24	Jun-24	FALSE	FALSE	TRUE
33				7/6/24	Jul-24	FALSE	FALSE	TRUE
34				7/15/24	Jul-24	FALSE	FALSE	TRUE
35				7/24/24	Jul-24	FALSE	FALSE	TRUE
36				8/2/24	Aug-24	FALSE	FALSE	TRUE
37				8/11/24	Aug-24	FALSE	FALSE	TRUE
Sheet1

Cell Formulas
Range	Formula
F2:F37	F2	=AND(E2<>"",E2<TODAY())
G2:G37	G2	=AND(E2>=TODAY(),E2<EOMONTH(TODAY(),3))
H2:H37	H2	=AND(E2>=TODAY(),E2>EOMONTH(TODAY(),3))
B3	B3	=A3+430
D2:D37	D2	=E2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E37	Expression	=AND(E2>=TODAY(),E2>EOMONTH(TODAY(),3))	text	NO
E2:E37	Expression	=AND(E2>=TODAY(),E2<EOMONTH(TODAY(),3))	text	NO
E2:E37	Expression	=AND(E2<>"",E2<TODAY())	text	NO

i have also added to a dropbox share - will only be available for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

THANK YOU for the dropbox but unfortunately I cannot access that site from my work computer though sadly. I appreciate the effort though.

The day of the manually-entered date (in my original example) is unimportant. It doesn't matter what day in September of 2023 they trained in... only the month matters. So if someone writes "Sep-23-23" I'd want it to simplify it to reflect just "Sep-23".

The date of their training expiration is 14 months after the month they trained. So adding 14 months to someone who was trained in September 2023 = As of November 1st 2024 they can't do their job (displayed solely as Nov-24 in my spreadsheet).

My formula used 430 days because that generates the correct month / 14 months in advance. Since the original DAY didn't matter (only the month) and the day of expiration is always the same (1st of the month) 430 works for this.

My goal is to, at a glance, see who is up to date (green), who needs to get retrained (amber), and who is ineligible to work (red). My 430 formula already works to spit out the due dates but I want to make it easier for training to use the colors to see what they need to do.

 

[etaf]

i'm sorry making a bit of a big deal of this \

should be simple - but i'm not following very well, based on the first post and now 14 months


does my example in xl2bb - not work then ?
if so why

based on column E as the actual training date

1. Highlighted in green if the the date is more than 3 calendar months away. For example Feb-24 would be green today (October 31, 2023).
2. Highlighted in yellow if the date is within 3 calendar months away. For example a date of Feb-24 will turn yellow as of tomorrow (because Feb 2024 - 3 months = Nov 2023)
3. Highlighted in red if the date is today or in the past. So in the above example, January 31st of 2024 the cell would be yellow but on February 1st it would turn red.

3. anything below today is red
2. yellow coming up in next 3 mths
1. geen anything over 3 mths is OK




mm see now we are saying 14mths ahead