An MOT due date formula

[les361800]

A kind soul yesterday helped me with a formula for a vehicle maintenance sheet yesterday. I have an additional request.

Can someone please help me out with a formula that gives an "MOT due in..." time, but takes into account the fact that an MOT is only due after 3 years? I have added a date of manufacture column.

A little help with the conditional formatting to get it red when overdue would also be most appreciated.

Thank you very much Excel wizards!

 

[Joe4]

Try adding this formula to cell K2 and copy down:

=IF(J2>0,EDATE(J2,36),IF(B2>0,EDATE(B2,36),""))

What it does is first look at column J, and if there is a date there, will calculate 3 years from that date.
If there is no date in column J, it will look at column B, and if there is a date there, it will calulcate 3 years from that date.

Then, for conditional formatting on column K, use the following Conditional Formatting formula:

=K2<TODAY()

 

[les361800]

Try adding this formula to cell K2 and copy down:

=IF(J2>0,EDATE(J2,36),IF(B2>0,EDATE(B2,36),""))

What it does is first look at column J, and if there is a date there, will calculate 3 years from that date.
If there is no date in column J, it will look at column B, and if there is a date there, it will calulcate 3 years from that date.

Then, for conditional formatting on column K, use the following Conditional Formatting formula:

=K2<TODAY()

Thanks very much Joe! Is it possible to have it so that it calculates just the 1 year from column J?

My bad, I didn't explain this!

 

[les361800]

Try adding this formula to cell K2 and copy down:

=IF(J2>0,EDATE(J2,36),IF(B2>0,EDATE(B2,36),""))

What it does is first look at column J, and if there is a date there, will calculate 3 years from that date.
If there is no date in column J, it will look at column B, and if there is a date there, it will calulcate 3 years from that date.

Then, for conditional formatting on column K, use the following Conditional Formatting formula:

=K2<TODAY()

No matter I figured it out!! THANK YOU

 

[Joe4]

If you take a look at the EDATE function (see: Excel EDATE function to add or subtract months from date), you will see that the second argument is just the number of months you want to add to a date.
So to get 1 year instead of 3, just change 36 to 12 for that part of the formula, i.e.

=IF(J2>0,EDATE(J2,12),IF(B2>0,EDATE(B2,36),""))

 

[les361800]

If you take a look at the EDATE function (see: Excel EDATE function to add or subtract months from date), you will see that the second argument is just the number of months you want to add to a date.
So to get 1 year instead of 3, just change 36 to 12 for that part of the formula, i.e.

=IF(J2>0,EDATE(J2,12),IF(B2>0,EDATE(B2,36),""))

Fixed it! Thanks

Is it possible to add a *MOT Due in X months" counter like the service column?

 

[Joe4]

To borrow some of the logic from that other formula, you could do something like the following in cell I2:

=IF(M2>TODAY(),"Service Due in " & TEXT((M2-TODAY())/30,"0.0") & " months","")