highlighting dates within a 6 month period

[shaftdx]

Good morning all. i am looking to Highlight a column of dates that fall within a certain time frame of 6 months. So if someones License expires 1/19/24
I would like that date to be highlighted 6 months before that date. and maybe another color after that date passes.

 

[Joe4]

You can use Conditional Formatting, along with the EDATE function, in which you can add/subtract a set number of months from some date.
See: How to use the Excel EDATE function | Exceljet

If you need help setting this up, please provide us with details (i.e. where does the date you want to check reside? where does the date you want to compare it to reside?).

 

[shaftdx]

You can use Conditional Formatting, along with the EDATE function, in which you can add/subtract a set number of months from some date.
See: How to use the Excel EDATE function | Exceljet

If you need help setting this up, please provide us with details (i.e. where does the date you want to check reside? where does the date you want to compare it to reside?).

the dates I want to check are in Column T I want to be able to subtract 6 months from what ever date that is in many different rows in Column T Since all of our drivers have different expiration dates for a lot of things (ie. Registration, medical, drivers license ect.) so depending on what it is I want the cell to change colors at certain times based on that date.

 

[Joe4]

Let's say you wanted to highlight the value in cell T1 if the date in cell T1 is less than 6 months from today.
Then you could use a Conditional Formatting rule of:

=T1<EDATE(TODAY(),6)

Now, let's say that does almost what you want to do, but you want to exclude any past days. Then you could amend that CF formula like this:

=AND(T1>TODAY(),T1<EDATE(TODAY(),6))

If you have multiple ranges that you want to do multiple colors for, you can do multiple Conditional Formatting rules, creating intervals using AND like I showed in the last example (greater than some date AND less than some other date).

 

[shaftdx]

Let's say you wanted to highlight the value in cell T1 if the date in cell T1 is less than 6 months from today.
Then you could use a Conditional Formatting rule of:

=T1<EDATE(TODAY(),6)

Now, let's say that does almost what you want to do, but you want to exclude any past days. Then you could amend that CF formula like this:

=AND(T1>TODAY(),T1<EDATE(TODAY(),6))

If you have multiple ranges that you want to do multiple colors for, you can do multiple Conditional Formatting rules, creating intervals using AND like I showed in the last example (greater than some date AND less than some other date).

ok I think we got something here so if a drivers medical card expired on 1/19/23 I can put the cell number T6 (where the date is located) in the place of where it says today and it will Highlight once it gets to within 6 months of that date correct? and just create another CF and changes the 6 to a 3 if I want another color for 3 months. I will try this wen I get back to the office I am out running errands right now

 

[Joe4]

ok I think we got something here so if a drivers medical card expired on 1/19/23 I can put the cell number T6 (where the date is located) in the place of where it says today and it will Highlight once it gets to within 6 months of that date correct? and just create another CF and changes the 6 to a 3 if I want another color for 3 months. I will try this wen I get back to the office I am out running errands right now

To see if the date in cell T6 falls between 3 months and 6 months in the future, you would use this CF formula:

=AND(T6>EDATE(TODAY(),3),T6<=EDATE(TODAY(),6))

For your second CF rule, where it is in between today and 3 months, that would look something like:

=AND(T6>TODAY(),T6<=EDATE(TODAY(),3))

Note that you may need to play around with the >, <, >=, and <= signs, depending on whether you want to include the dates or not, i.e.

T6>=TODAY()

would include today's date, but

T6>TODAY()

would not.

So you need to determine exactly how you want it to work and adjust accordingly (i.e. which CF rule should EXACTLY 3 months show up under, and should today be Conditionally Formatted).