Time horizon

[ks1987]

Hi,

I am building a document which requires users to define the time horizon and periodicity requirements, upon which we collect data. This is done through two fields - Start Date and End Date. The period defined will be typically be a 5 year period but can be more or less.

Upon defining this period there is another column 'Time Period' which should automatically be populated with annual periods. For example:-

Start Date - 2/1/2013
End Date - 11/30/2017

Time Period - Feb 2013 - Dec 2013
- Jan 2014 - Dec 2014
- Jan 2015 - Dec 2015
- Jan 2016 - Dec 2016
- Jan 2017 - Nov 2017

I am at odds to figure this problem out. Any help on this is highly appreciated

 

[raj2206]

Hey Thanks for writing!!

I have the Start Date in A1 and the End Date in Cell B1

Now in A2: Apply the following formula:

=IFERROR(IF(YEAR(A1)<YEAR($B$1),IF(ROWS($A$1:A1)=1,TEXT(A1,"MMMM")&" "&YEAR($A$1)+ROWS($A$2:A2)-1,"January "&YEAR($A$1)+ROWS($A$2:A2)-1),""),"")

In B2 : Apply the following formula:

=IFERROR(IF(YEAR(A2)<=YEAR($B$1),TEXT(EDATE(B1,12-MONTH(B1)),"MMMM")&" "&YEAR(A2),""),"")

Now Drag the formulas in A2 and B2 down till you see blank cells.

Hope this helps!!


Thanks/Raj

 

[raj2206]

Please Ignore the previous Post.......


Hey Thanks for writing!!

I have the Start Date in A1 and the End Date in Cell B1

Now in A2: Apply the following formula:

=IFERROR(IF(YEAR(A1)<YEAR($B$1),IF(ROWS($A$1:A1)=1,TEXT(A1,"MMMM")&" "&YEAR($A$1)+ROWS($A$2:A2)-1,"January "&YEAR($A$1)+ROWS($A$2:A2)-1),""),"")
<year($b$1),if(rows($a$1:a1)=1,text(a1,"mmmm")&" "&year($a$1)+rows($a$2:a2)-1,"january="" "&year($a$1)+rows($a$2:a2)-1),""),"")
</year($b$1),if(rows($a$1:a1)=1,text(a1,"mmmm")&">
In B2 : Apply the following formula:

=IFERROR(IF(YEAR(A2)<=YEAR($B$1),TEXT(EDATE(B1,12-MONTH(B1)),"MMMM")&" "&YEAR(A2),""),"")

Now Drag the formulas in A2 and B2 down till you see blank cells.

Hope this helps!!


Thanks/Raj
​