Recurring 12 month formula in excel

varghesep

New Member
Joined
Sep 4, 2018
Messages
3
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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
[TABLE="width: 500"]
<tbody>[TR]
[TD]NAMES[/TD]
[TD]Feb 2017 - Jan 2018[/TD]
[TD]Mar 2017 - Feb 2018[/TD]
[TD]Apr 2017 - Mar 2018[/TD]
[TD]May 2017 - Apr 2018[/TD]
[/TR]
[TR]
[TD]SM
[/TD]
[TD]34[/TD]
[TD]45[/TD]
[TD]44[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]59[/TD]
[TD]55[/TD]
[TD]45[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]WW[/TD]
[TD]34[/TD]
[TD]67[/TD]
[TD]67[/TD]
[TD]67[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Sorry i dont know what you are after. Its not explained at all as far as i can tell.
 
Upvote 0
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
 
Upvote 0
We don't delete threads once there's been a discussion. Start a new one with the 'right' question.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top