A formula that highlights either age or mileage of a vehicle, whichever comes first

les361800

New Member
Joined
Jul 11, 2023
Messages
28
Office Version
  1. 365
Platform
  1. Windows
I have a challenge, if anyone is kind enough to assist.
I can probably put a formula together for 1 condition, but the mix of mileage/date has be stumped!

I am attempting to create a spreadsheet for my boss that tells him if a vehicle is due a service. (I have been in this job 1 month and I would very much like to make myself useful!)
The thing is, its whatever condition arrives first, either a 1 or 2 yearly interval, or every 10/25k etc (this is for multiple vehicles and they are have different service intervals).

Is there a formula I can use that can address both of these conditions? I suppose the answer box would need to show an answer to be something like service due in "100 miles" or "1 month" depending on what condition was true? Maybe just "no" if neither condition true?

If the box could turn red/green that would be super helpful if its possible to add this in!
 

Attachments

  • Screenshot 2023-07-11 141631.png
    Screenshot 2023-07-11 141631.png
    46.5 KB · Views: 24

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
I think you need to add 'mileage at last service'

MrExcelPlayground18.xlsx
ABCDEFGH
1RegMileageYearLast Service or New DateMileage at last ServiceService Interval milesService Interval (duration)Service due?
26138320178/11/2022400001800024*Service Due (mileage)
34878320181/11/20233700018000Service Due in 6217 miles
4965520226/18/202310002500024Service Due in 16345 miles or 23.6 months
52610020225/16/202310002500024*Service Due (mileage)
63052202111/19/2022100024Service Due in 16.6 months
78574202110/24/202210002500024Service Due in 17426 miles or 15.7 months
842820231/1/202301200012Service Due in 11572 miles or 5.8 months
Sheet20
Cell Formulas
RangeFormula
H2:H8H2=LET(m,B2-E2,t,EDATE(D2,G2),md,F2,td,TODAY(),a,IF(AND(md>0,m>=md),"mileage",""),b,IF(AND(G2>0,t<=td),"duration",""),c,IF(OR(a<>"",b<>""),"*Service Due ("&TEXTJOIN(",",TRUE,a,b)&")",""),d,IF(md>0,md-m&" miles",""),e,IF(G2>0,TEXT((t-td)/30,"0.0")&" months",""),f,"Service Due in "&TEXTJOIN(" or ",TRUE,d,e),IF(c="",f,c))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:H8Expression=LEFT($H2,1)="*"textNO
 
Upvote 0
I think you need to add 'mileage at last service'

MrExcelPlayground18.xlsx
ABCDEFGH
1RegMileageYearLast Service or New DateMileage at last ServiceService Interval milesService Interval (duration)Service due?
26138320178/11/2022400001800024*Service Due (mileage)
34878320181/11/20233700018000Service Due in 6217 miles
4965520226/18/202310002500024Service Due in 16345 miles or 23.6 months
52610020225/16/202310002500024*Service Due (mileage)
63052202111/19/2022100024Service Due in 16.6 months
78574202110/24/202210002500024Service Due in 17426 miles or 15.7 months
842820231/1/202301200012Service Due in 11572 miles or 5.8 months
Sheet20
Cell Formulas
RangeFormula
H2:H8H2=LET(m,B2-E2,t,EDATE(D2,G2),md,F2,td,TODAY(),a,IF(AND(md>0,m>=md),"mileage",""),b,IF(AND(G2>0,t<=td),"duration",""),c,IF(OR(a<>"",b<>""),"*Service Due ("&TEXTJOIN(",",TRUE,a,b)&")",""),d,IF(md>0,md-m&" miles",""),e,IF(G2>0,TEXT((t-td)/30,"0.0")&" months",""),f,"Service Due in "&TEXTJOIN(" or ",TRUE,d,e),IF(c="",f,c))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:H8Expression=LEFT($H2,1)="*"textNO
Wow it worked!! Good point, I have added that column in too.
Thank you very much kind stranger :)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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
Back
Top