# Highlighting an overdue date

#### shaftdx

##### Board Regular
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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### jtakw

##### Well-known Member
Hi,

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

B2 containing your Next Quarterly Due date.

#### shaftdx

##### Board Regular
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

##### Well-known Member
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
AB
1Last QtrNext Qtr
21/15/20204/14/2020
34/15/2020
Sheet913
Cell Formulas
RangeFormula
B2B2=A2+90
B3B3=EDATE(A2,3)

#### shaftdx

##### Board Regular

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
AB
1Last QtrNext Qtr
21/15/20204/14/2020
34/15/2020
Sheet913
Cell Formulas
RangeFormula
B2B2=A2+90
B3B3=EDATE(A2,3)
Thank you for the tip!

You're welcome.

#### shaftdx

##### Board Regular

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
AB
1Last QtrNext Qtr
21/15/20204/14/2020
34/15/2020
Sheet913
Cell Formulas
RangeFormula
B2B2=A2+90
B3B3=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

##### Well-known Member
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

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

Book3.xlsx
ABCDEF
1Last QtrInspection DateNext QtrInspection DateNext QtrInspection Date
21/10/20213/31/20214/10/20217/9/2021
3FALSEFALSE
Sheet913
Cell Formulas
RangeFormula
C2,E2C2=A2+90
C3,E3C3=AND(B2="",C2<TODAY())

#### shaftdx

##### Board Regular
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

#### Attachments

• Screen Shot 2021-04-13 at 2.51.27 PM.png
79.8 KB · Views: 2

Replies
7
Views
203
Replies
1
Views
18
Replies
5
Views
171
Replies
2
Views
35
Replies
0
Views
118

1,133,271
Messages
5,657,765
Members
418,411
Latest member
Excellency

### 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.

### Which adblocker are you using?

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

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