Date Increment: dynamic date range

[oodai]

Hi I have seen a dynamic date range. However, just need a tweaking to the similar soultion

Need your help in excel date range in excel

Say when I update start Year and Month in cells B1 and D1 it should update the next seven years based on the Input.

below is the example highlighted in Red are my Inputs and highlighted in Green is my output in each column.


Start Year2014Start MonthMay
May'14-Apr'15 May'15-Apr'16 May'16-Apr'17 May'17-Apr'18 May'18-Apr'19 May'19-Apr'20 May'20-Apr'21 May'21-Apr'22
It would be a great help if you can give a formula for the same.

 

[Somendra Misra]

Hi,

Welcome to the forum.

Try below formula on Help sheet in A6 and copy right.

=TEXT(EOMONTH(DATE($D$1,MONTH($B$1&1),1),(COLUMNS($A6:A6)-1)*12),"mmm'yy")&"-"&TEXT(EOMONTH(EOMONTH(DATE($D$1,MONTH($B$1&1),1),(COLUMNS($A6:A6)-1)*12),11),"mmm'yy")

Regards,

 

[Mike H..]

Hi I have seen a dynamic date range. However, just need a tweaking to the similar soultion

Need your help in excel date range in excel

Say when I update start Year and Month in cells B1 and D1 it should update the next seven years based on the Input.

below is the example highlighted in Red are my Inputs and highlighted in Green is my output in each column.


Start Year2014Start MonthMay
May'14-Apr'15 May'15-Apr'16 May'16-Apr'17 May'17-Apr'18 May'18-Apr'19 May'19-Apr'20 May'20-Apr'21 May'21-Apr'22
It would be a great help if you can give a formula for the same.

Hi,

Put this in A6 and drag right. See your wotrkbook.


=TEXT(DATE($D$1+COLUMN(A1)-1,MONTH(1&$B$1),1),"MMM'YY")&"-"&TEXT(DATE(($D$1+1)+COLUMN(A1)-1,MONTH(1&$B$1)-1,1),"MMM'YY")

 

[John Jairo V]

Hi to all!

Another option:
=TEXT($B$1&$D$1+COLUMNS($A6:A6)-1,"mmm'yy")&"-"&TEXT(EDATE($B$1&$D$1+COLUMNS($A6:A6),-1),"mmm'yy")

Blessings!

 

[oodai]

Hi I have seen a dynamic date range. However, just need a tweaking to the similar soultion

Need your help in excel date range in excel

Say when I update start Year and Month in cells B1 and D1 it should update the next seven years based on the Input.

below is the example highlighted in Red are my Inputs and highlighted in Green is my output in each column.


Start Year2014Start MonthMay
May'14-Apr'15 May'15-Apr'16 May'16-Apr'17 May'17-Apr'18 May'18-Apr'19 May'19-Apr'20 May'20-Apr'21 May'21-Apr'22
It would be a great help if you can give a formula for the same.

 

[oodai]

wow wow wow.. this is excellent..exactly what i am looking for... thank you geniuses.. Thanks a ton...

 

[Khalid NGO]

Hi,
Just sharing another one:
=TEXT($B1&$D1+COLUMNS($A1:A1)-1,"mmm'yy")&"-"&TEXT(DATE($D1+COLUMNS($A1:A1),MONTH(1&$B1)-1,1),"mmm'yy")

Regards,