Formula to Determine sequential Month / Year

[Caleeco]

Hello,

Some help with the following problem would be much appreciated!

I have a formula which will determine the CURRENT month and Year in the following format:
if today = 12/09/2017
result = AP9 2017

="AP" & MONTH(TODAY()) & " " & YEAR(TODAY())

I would like a formula, whereby I can drag the cell right to fill out adjacent Months/Years eg

AP9 2017 | AP10 2017 | AP11 2017 | AP12 2017 | AP1 2018

The biggest problem is the inflection point when the year changes. Any ideas?

The first column should always represent TODAYs Month/Year combo.

Many Thanks
Caleeco

 

[oldbrewer]

[TABLE="width: 605"]
<colgroup><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]01/09/2017[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]01/11/2017[/TD]
[TD="align: right"]01/12/2017[/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]01/02/2018[/TD]
[TD="align: right"]01/03/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP9 2017[/TD]
[TD]AP10 2017[/TD]
[TD]AP11 2017[/TD]
[TD]AP12 2017[/TD]
[TD]AP1 2018[/TD]
[TD]AP2 2018[/TD]
[TD]AP3 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A2=[/TD]
[TD="colspan: 3"]="AP"&MONTH(A1)&" "&YEAR(A1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]what is your question in terms of the above[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
2	AP9 2017	AP10 2017	AP11 2017	AP12 2017	AP1 2018
3
4	Custom Format	"AP"m yyyy
4b

Cell Formulas
Range	Formula
A2	=TODAY()
B2	=EOMONTH(A2,1)

 

[Caleeco]

[TABLE="width: 605"]
<tbody>[TR]
[TD="align: right"]01/09/2017[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]01/11/2017[/TD]
[TD="align: right"]01/12/2017[/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]01/02/2018[/TD]
[TD="align: right"]01/03/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP9 2017[/TD]
[TD]AP10 2017[/TD]
[TD]AP11 2017[/TD]
[TD]AP12 2017[/TD]
[TD]AP1 2018[/TD]
[TD]AP2 2018[/TD]
[TD]AP3 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A2=[/TD]
[TD="colspan: 3"]="AP"&MONTH(A1)&" "&YEAR(A1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]what is your question in terms of the above[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sorry for the delayed reply. Evidently my OP wasn't as clear as it could have been. I dont have the dates in the first row to calculate from, the solution Dave Patton posted resolves the issue.

Thanks for the response nonetheless! :D

 

[Caleeco]

Excel 2010

	A	B	C	D	E
Custom Format	"AP"m yyyy

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]AP9 2017[/TD]
[TD="align: right"]AP10 2017[/TD]
[TD="align: right"]AP11 2017[/TD]
[TD="align: right"]AP12 2017[/TD]
[TD="align: right"]AP1 2018[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

4b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=TODAY()[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=EOMONTH(A2,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Not all Heros wear capes... here i was messing around with MID, LEFT, FIND functions...

Thanks for the easy solution!

 

[Dave Patton]

Thanks for the feedback. Dave