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

[les361800]

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!

 

[JamesCanale]

I think you need to add 'mileage at last service'

MrExcelPlayground18.xlsx
	A	B	C	D	E	F	G	H
1	Reg	Mileage	Year	Last Service or New Date	Mileage at last Service	Service Interval miles	Service Interval (duration)	Service due?
2		61383	2017	8/11/2022	40000	18000	24	*Service Due (mileage)
3		48783	2018	1/11/2023	37000	18000		Service Due in 6217 miles
4		9655	2022	6/18/2023	1000	25000	24	Service Due in 16345 miles or 23.6 months
5		26100	2022	5/16/2023	1000	25000	24	*Service Due (mileage)
6		3052	2021	11/19/2022	1000		24	Service Due in 16.6 months
7		8574	2021	10/24/2022	1000	25000	24	Service Due in 17426 miles or 15.7 months
8		428	2023	1/1/2023	0	12000	12	Service Due in 11572 miles or 5.8 months
Sheet20

Cell Formulas
Range	Formula
H2:H8	H2	=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
Cell	Condition		Cell Format	Stop If True
A2:H8	Expression	=LEFT($H2,1)="*"	text	NO

 

[les361800]

I think you need to add 'mileage at last service'

MrExcelPlayground18.xlsx
	A	B	C	D	E	F	G	H
1	Reg	Mileage	Year	Last Service or New Date	Mileage at last Service	Service Interval miles	Service Interval (duration)	Service due?
2		61383	2017	8/11/2022	40000	18000	24	*Service Due (mileage)
3		48783	2018	1/11/2023	37000	18000		Service Due in 6217 miles
4		9655	2022	6/18/2023	1000	25000	24	Service Due in 16345 miles or 23.6 months
5		26100	2022	5/16/2023	1000	25000	24	*Service Due (mileage)
6		3052	2021	11/19/2022	1000		24	Service Due in 16.6 months
7		8574	2021	10/24/2022	1000	25000	24	Service Due in 17426 miles or 15.7 months
8		428	2023	1/1/2023	0	12000	12	Service Due in 11572 miles or 5.8 months
Sheet20

Cell Formulas
Range	Formula
H2:H8	H2	=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
Cell	Condition		Cell Format	Stop If True
A2:H8	Expression	=LEFT($H2,1)="*"	text	NO

Wow it worked!! Good point, I have added that column in too.
Thank you very much kind stranger