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","")

 

[strainu]

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!

Hi , can you help me with a template of that excel? I need somthing similar! And i'am new in excel, and i don't knwo to do it. THanks!

 

[Joe4]

Hi , can you help me with a template of that excel? I need somthing similar! And i'am new in excel, and i don't knwo to do it. THanks!

Welcome to the Board!

It is best to post your question to a new thread, as opposed to posting it to an old existing thread. That will get the most views, as most helpers use the "Unanswered threads" listing to look for new unanswered questions to post. You should only post back to old questions if you have a question about that original question or reply to it. For your own issues, it is best to start a new thread.

Also, be sure to provide details in your question (you really have not provided enough details for anyone to help you). See here for posting tips and tools that can assist you in posting your question:

Guidelines

Guidelines for MrExcel Message Board Use

www.mrexcel.com

If you are more interested in hiring a consultant to do the work for you, have a look here: Consulting Services