Rules/Formulas with dates and colors

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
645
Good Afternoon,

I'm drawing a blank here on something that should otherwise be simple.....

I have today's date in cell A1.
For monthly inspections, I have columns B and C populated with the date of inspection (B1-B10) and when the next inspection is due (C1-C10). So if the inspection is due March 15 (C1) and there's nothing, or a date prior to march in B1, then the rule makes cell C1 red. As long as the "next due" (C column) is at least one month ahead, nothing turns red.

I need to adjust this concept for a few quarterly inspections. I wold like it such that the year (Jan-Dec) is divided into quarters. Therefore, if the date in B1 is Feb 5, 2020, then the "Due" in C1 can be anytime in the next quarter and it won't turn red until a quarter has passed. Does that make sense? For example, incorrectly setup right now, the B1 date is Feb 5 and my formula
Code:
=edate(b1,1)
shows the next inspection is due march 5 and therefore the cell is red. this should go to the next quarter and not be red...until we are entering that quarter.

Thanks for the help!

Rule is simple currently,
Code:
cell < A1 (date)
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Watch MrExcel Video

Forum statistics

Threads
1,119,085
Messages
5,576,027
Members
412,694
Latest member
Deaf1Too
Top