Please help

[Melmillw82]

Hi all,
I need help with a formula. I have reg date in column A, I would like to find out the next service date of the vehicle. I need a formula that will add 18 months to the reg date then keep adding 18 months untill a certain date. But only if its less than my end date.
Thins is driving me mad please help.

Thanks in advance

 

[Dave Patton]

Agreed
it's probably the DAYS function that OP may not have,

One commentary stated that the Days function started with version 2013.

 

[Melmillw82]

Thank you
=EDATE(A12,CEILING(DATEDIF(A12,TODAY(),"m"),18))
Worked!!.
I am also wanting something similar but it adds 36 months to the reg date then adds 12 after that until todays date? Any help appreciated. ?

 

[Dave Patton]

What did you try?

It is not clear how you are using the formula.

If Today is the expiry, the formula will show a blank.

consider
=IF(EDATE(A13,MAX(36,CEILING((TODAY()-A13)/30.4375,12)))>TODAY(),"",EDATE(A13,MAX(36,CEILING((TODAY()-A13)/30.4375,12))))

 

[Dave Patton]

=IF(EDATE(A13,MAX(36,CEILING((TODAY()-A13)/30.4375,12)))>Expiry,"",EDATE(A13,MAX(36,CEILING((TODAY()-A13)/30.4375,12))))

 

[Melmillw82]

reg date date of next service
01/01/2013 01/01/2019
01/01/2018 01/01/2021

 

[Melmillw82]

It has to take reg date add 36 months then keep adding a year. So I get the next service date after today. but if the car has only just been registered then it has to add 3 years on the another 12 months.

 

[Dave Patton]

so you didn't try the formula!

Excel 2010
	A	B	C	D
13	1-Jan-2013			1-Jan-2019
14	1-Jan-2018			1-Jan-2021
15
2b

Cell Formulas
Range	Formula
D13	=EDATE(A13,MAX(36,CEILING((TODAY()-A13)/30.4375,12)))
D14	=EDATE(A14,MAX(36,CEILING((TODAY()-A14)/30.4375,12)))