Highlighting an overdue date

shaftdx

Board Regular
Joined
Aug 20, 2018
Messages
51
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

B2 containing your Next Quarterly Due date.
 

shaftdx

Board Regular
Joined
Aug 20, 2018
Messages
51
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 20, 2018
Messages
51

ADVERTISEMENT

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!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome.:)
 

shaftdx

Board Regular
Joined
Aug 20, 2018
Messages
51

ADVERTISEMENT

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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 20, 2018
Messages
51
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
    Screen Shot 2021-04-13 at 2.51.27 PM.png
    79.8 KB · Views: 2

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top