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

Matthew82

New Member
Joined
Oct 31, 2023
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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 -
 
Upvote 0
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.
 
Upvote 0
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

Cell Formulas
RangeFormula
F2:F37F2=AND(E2<>"",E2<TODAY())
G2:G37G2=AND(E2>=TODAY(),E2<EOMONTH(TODAY(),3))
H2:H37H2=AND(E2>=TODAY(),E2>EOMONTH(TODAY(),3))
B3B3=A3+430
D2:D37D2=E2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E37Expression=AND(E2>=TODAY(),E2>EOMONTH(TODAY(),3))textNO
E2:E37Expression=AND(E2>=TODAY(),E2<EOMONTH(TODAY(),3))textNO
E2:E37Expression=AND(E2<>"",E2<TODAY())textNO


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

 
Upvote 0
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

Cell Formulas
RangeFormula
F2:F37F2=AND(E2<>"",E2<TODAY())
G2:G37G2=AND(E2>=TODAY(),E2<EOMONTH(TODAY(),3))
H2:H37H2=AND(E2>=TODAY(),E2>EOMONTH(TODAY(),3))
B3B3=A3+430
D2:D37D2=E2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E37Expression=AND(E2>=TODAY(),E2>EOMONTH(TODAY(),3))textNO
E2:E37Expression=AND(E2>=TODAY(),E2<EOMONTH(TODAY(),3))textNO
E2:E37Expression=AND(E2<>"",E2<TODAY())textNO


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

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.
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top