Extracting MMYY from a MMDDYY cell then auto-populating ascending and descending MMYY cells

[mspushypots]

So I am not really sure how to ask this to search the board and Google is no help, so I am just going to post. I apologize if this has been answered before. Feel free to redirect me

I have a cell, #1 (A2) that I enter a date in. Cell #2 (AK) is set to =A2 and change the format to MMM YY. What I am wondering is if there a formula that I can use in the cells up and down from AK to convert the value in AK to the prior/subsequent month in the format shown. Or is there a better formula I can use for AK to make this easier? Or am I wishful thinking? Thanks for any input here!

 

[mspushypots]

 

[Toadstool]

Merge rows 1 & 2
Highlight B1 & B2 then right-click, Format Cells, Alignment, Merge cells. Then copy B1 and paste into rows 1 & 2 as far right as you want to go.

Enter Formulae
In AK1 put =A2

In AL1 put =DATE(YEAR(AK1),MONTH(AK1)+1,DAY(AK1)) then copy&paste that cell as far right as you want to go.

In AJ1 put =DATE(YEAR(AK1),MONTH(AK1)-1,DAY(AK1)) then copy&paste that cell as far left as you want to go.

Format Date & Orientation
Select B1 through as far right as you want to go, right-click, Format Cells, Number tab, Date and select type Mar-12. Alignment tab, Orientation 90 degrees, select OK.

 

[jtakw]

Hi,

One way:



AJ1 formula copied left, AL1 formula copied right.

 

[jtakw]

Hi,

One way:



AJ1 formula copied left, AL1 formula copied right.

My solution didn't post properly:

Book1
	A	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1	This row Custom formatted mmm-yy	Sep-16	Oct-16	Nov-16	Dec-16	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17
2	January 4, 2017
Sheet10

Cell Formulas
Range	Formula
AJ1	=EOMONTH(AK1,-2)+DAY(AK1)
AK1	=A2
AL1	=EOMONTH(AK1,0)+DAY(AK1)

 

[mspushypots]

Thank you!

 

[jtakw]

You're welcome, welcome to the forum.

 

[Peter_SSs]

Thank you!

Are you sure that does what you want? For example, if you enter 31 January 2017 in A4 you will find two cells with Dec-16 and no cells with Feb-17 in AG1:AR1 (using post #5 screen shot range)

If you want 1 of each month consecutively then I would suggest
- in AJ1, copied left =EDATE(AK1,-1)
- in AL1, copied right =EDATE(AK1,1)

Edit: Actually, if you are trying to keep the 'day' part of the row 1 dates (even though the day is not displayed) as close to the A4 day as possible, then perhaps

AJ1: =EDATE($AK1,-COLUMNS(AJ1:$AJ1))
AL1: =EDATE($AK1,COLUMNS($AL1:AL1))

 

[mspushypots]

Are you sure that does what you want? For example, if you enter 31 January 2017 in A4 you will find two cells with Dec-16 and no cells with Feb-17 in AG1:AR1 (using post #5 screen shot range)

If you want 1 of each month consecutively then I would suggest
- in AJ1, copied left =EDATE(AK1,-1)
- in AL1, copied right =EDATE(AK1,1)

Edit: Actually, if you are trying to keep the 'day' part of the row 1 dates (even though the day is not displayed) as close to the A4 day as possible, then perhaps

AJ1: =EDATE($AK1,-COLUMNS(AJ1:$AJ1))
AL1: =EDATE($AK1,COLUMNS($AL1:AL1))

Thank you! I haven't had time to go back to the spreadsheet so I will see how these all work out for me.

 

[jtakw]

You're right, that didn't come to mind when I posted, my formulas will possibly fail for 29th, 30th, & 31st day of the month.
If the OP don't care about the day, but just the month and year then a small adjustment to my formula should work, but I think your EDATE is better.

Book1
	A	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1	This row Custom formatted mmm-yy	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18
2	January 1, 2018
Sheet10

Cell Formulas
Range	Formula
AJ1	=EOMONTH(AK1,-1)
AK1	=A2
AL1	=EOMONTH(AK1,1)