Highlighting an overdue date

[shaftdx]

ok I have a series of dates for our fleet of vehicles for service. I want to highlight the ones that are overdue. I know how to do it from todays date =today and all that but that is not what I need. The way I have it now there is a column that has "last Quarterly" which we physically type in the date. In the Next Column over is "Next Quarterly Due" which I take the previous date and add 90 days to that. What I am looking to do is Highlight the cell when those 90 days are done if we have not already done the check up to that vehicle.

 

[jtakw]

Hi,

Why not just =B2<TODAY() as CF formula.

B2 containing your Next Quarterly Due date.

 

[shaftdx]

Hi,

Why not just =B2<TODAY() as CF formula.

B2 containing your Next Quarterly Due date.

Excellent Thank you that definitely seems to work how i needed it to. The whole "today" thing sends me for a loop sometimes I am guessing in this instance "today" means the date that is typed in there and not actual TODAY

 

[jtakw]

You're welcome, thanks for the feedback.

So TODAY() is actually today's date, and changes everyday, what we're doing is comparing the Date in Next Qtr to Today, if it's Less than Today, that means it's Past and Expired.
On another note, alternatively, if you choose, you can use the EDATE function instead of +90 to your Last Qtr date, so it provides a Date that is Exactly 3 months from Last Qtr, accounting for months with 30, 31, 28 days and leap years. Just your choice.

Book3.xlsx
	A	B
1	Last Qtr	Next Qtr
2	1/15/2020	4/14/2020
3		4/15/2020
Sheet913

Cell Formulas
Range	Formula
B2	B2	=A2+90
B3	B3	=EDATE(A2,3)

 

[shaftdx]

You're welcome, thanks for the feedback.

So TODAY() is actually today's date, and changes everyday, what we're doing is comparing the Date in Next Qtr to Today, if it's Less than Today, that means it's Past and Expired.
On another note, alternatively, if you choose, you can use the EDATE function instead of +90 to your Last Qtr date, so it provides a Date that is Exactly 3 months from Last Qtr, accounting for months with 30, 31, 28 days and leap years. Just your choice.

Book3.xlsx
	A	B
1	Last Qtr	Next Qtr
2	1/15/2020	4/14/2020
3		4/15/2020
Sheet913

Cell Formulas
Range	Formula
B2	B2	=A2+90
B3	B3	=EDATE(A2,3)

Thank you for the tip!

 

[jtakw]

You're welcome.

 

[shaftdx]

You're welcome, thanks for the feedback.

So TODAY() is actually today's date, and changes everyday, what we're doing is comparing the Date in Next Qtr to Today, if it's Less than Today, that means it's Past and Expired.
On another note, alternatively, if you choose, you can use the EDATE function instead of +90 to your Last Qtr date, so it provides a Date that is Exactly 3 months from Last Qtr, accounting for months with 30, 31, 28 days and leap years. Just your choice.

Book3.xlsx
	A	B
1	Last Qtr	Next Qtr
2	1/15/2020	4/14/2020
3		4/15/2020
Sheet913

Cell Formulas
Range	Formula
B2	B2	=A2+90
B3	B3	=EDATE(A2,3)

actually I just double checked something since you mentioned today, It is actually not working the way I want (I only thought it was because I put todays date in the cell to test it.) I need the cell to highlight once a day in the future comes and passes. so for example if a vehicle went in for a quarterly inspection 2 days ago the next scheduled inspection would be 7/10/21. on the 11th I would like for that to be highlighted. so the next quarterly column, the dates will constantly be changing

 

[jtakw]

So, sounds like we just need to add a test if the vehicle has done this Qtr's inspection.

Can you show a small (may be just even for 1 vehicle) sample of your setup?

 

[jtakw]

May be something like this, you'll need to adjust the formula to reference the correct cells in your set up.

Book3.xlsx
	A	B	C	D	E	F
1	Last Qtr	Inspection Date	Next Qtr	Inspection Date	Next Qtr	Inspection Date
2	1/10/2021	3/31/2021	4/10/2021		7/9/2021
3			FALSE		FALSE
Sheet913

Cell Formulas
Range	Formula
C2,E2	C2	=A2+90
C3,E3	C3	=AND(B2="",C2<TODAY())

 

[shaftdx]

Here is a screen shot of a few of out buses the dates are really out of wack, we just got a new forman and I am trying to make this easier for them. so we physically type out the date of the inspection for column I Column J automatically calculates the next time that bus is due for another inspection if it goes past that date is when I want it highlighted right now the date in J% is highlighted but it should not be as that date has not past yet