Recurring 12 month formula in excel

[varghesep]

Hello All

I work on a huge database that get updated each month with fresh data. The date on this data is captured in MMYYYY format. Now at times i am asked to build a report on a rolling 12 month basis ex. Jul 2017 to June 2018, Jun 2017 - May 2018, May 2017 - Apr 2018 so and so forth. Usually i would be expected to add just the recent 12 months, unfortunately there are chances data in the previous 12 months also may change, so every month i need to undertake this task afresh. Can you please help me with a formula that will identify the latest month and year and tag it as "Year 1" the previous month as "Year 2" for example:

Jul 2017 to June 2018 = Year 1
Jun 2017 - May 2018 = Year 2
May 2017 - Apr 2018 = Year 3

Now when data for July 2018 comes in i need the system to identify this and tag as follows:

Aug 2017 to July 2018 = Year 1
Jul 2017 to June 2018 = Year 2
Jun 2017 - May 2018 = Year 3
May 2017 - Apr 2018 = Year 4

Would be really grateful if anyone could assist.

TIA

 

[steve the fish]

Hi. There is crossover in these dates. June 2018 appears within the Year 1 'window' and Year 2. It would be a good idea to include some sample data and then include the expected results of those samples.

 

[varghesep]

Hi. There is crossover in these dates. June 2018 appears within the Year 1 'window' and Year 2. It would be a good idea to include some sample data and then include the expected results of those samples.

Sorry that was a typo, below is the table have changed month

NAMES	Feb 2017 - Jan 2018	Mar 2017 - Feb 2018	Apr 2017 - Mar 2018	May 2017 - Apr 2018
SM	34	45	44	77
BM	59	55	45	67
WW	34	67	67	67

<tbody>
</tbody>

 

[steve the fish]

Sorry i dont know what you are after. Its not explained at all as far as i can tell.

 

[varghesep]

Thanks Steve for looking at it, you're right the question is wrong... i realised i should've researched my question more sorry for the trouble i have asked for the thread to be deleted

 

[Macropod]

We don't delete threads once there's been a discussion. Start a new one with the 'right' question.