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]

A complete explanation and an example would have been very useful.

Consider the following

Excel 2010
	A	B
1	Reg Date	Expiry
2	1-Oct-2018	1-Jan-2022
3	1-Apr-2020
4	1-Oct-2021
2b

Cell Formulas
Range	Formula
A3	=IF(DATE(YEAR(A2),MONTH(A2)+18,DAY(A2))<$B$2,DATE(YEAR(A2),MONTH(A2)+18,DAY(A2)),"")
A4	=IF(DATE(YEAR(A3),MONTH(A3)+18,DAY(A3))<$B$2,DATE(YEAR(A3),MONTH(A3)+18,DAY(A3)),"")

 

[jtakw]

Hi,

Depending on how your data is setup, the formula will be written differently, this is one way:

Book1
	A	B	C	D
1	Reg Date	11/4/2018	End Date	9/1/2030
2	Service Dates	5/4/2020
3		11/4/2021
4		5/4/2023
5		11/4/2024
6		5/4/2026
7		11/4/2027
8		5/4/2029
9
10
Sheet334

Cell Formulas
Range	Formula
B2	=IFERROR(IF(EDATE(B1,18)>D$1,"",EDATE(B1,18)),"")

Formula copied down.

 

[Melmillw82]

Sorry should have given more detail.

A2 reg date 1st oct 2013
B2 I would like 18 months from this date and keep adding 18 months and give me the next date after todays date.

Hope this makes sense

 

[jtakw]

This should give you what you want, what about the End Date?

Book1
	A	B
1	Reg Date
2	10/1/2013	10/1/2019
Sheet335

Cell Formulas
Range	Formula
B2	=EDATE(A2,18*ROUNDUP(DAYS(TODAY(),A2)/(365*1.5),0))

 

[Melmillw82]

Hi, getting a name error? I don't need an end date just need the next service date after today
Thanks

 

[Dave Patton]

try one of

Excel 2010
	A	B	C	D
11	Reg Date			4-Nov-2018
12	1-Oct-2013	1-Oct-2019	1-Oct-2019	1-Oct-2019
13
2b

Cell Formulas
Range	Formula
B12	=EDATE(A12,CEILING(DATEDIF(A12,D11,"m"),18))
C12	=EDATE(A12,CEILING(DATEDIF(A12,TODAY(),"m"),18))
D12	=EDATE(A12,CEILING((TODAY()-A12)/30.25,18))

 

[jtakw]

Hi, getting a name error? I don't need an end date just need the next service date after today
Thanks

Don't see why you would get a NAME error, does your region require ; (semicolon) rather than , (comma)?
If so, replace all commas in formula with semicolon.

 

[Dave Patton]

Did you mean
=EDATE(A2,18*ROUNDUP((TODAY()-A2)/(365*1.5),0)) ?

 

[jtakw]

This should give you what you want, what about the End Date?

Book1
	A	B
1	Reg Date
2	10/1/2013	10/1/2019
Sheet335

Cell Formulas
Range	Formula
B2	=EDATE(A2,18*ROUNDUP(DAYS(TODAY(),A2)/(365*1.5),0))

Did you mean
=EDATE(A2,18*ROUNDUP((TODAY()-A2)/(365*1.5),0)) ?

Thanks Dave, I think you might be correct, it's probably the DAYS function that OP may not have, so yes, just subtract the dates instead.