YY/MM entries dependent on another cell

Hi,

I'm doing a lot of analyses in Excel where I compare data between months. In order to reduce the amount of cells where I have to plug in numbers manually, I'd like to know whether the following is possible:

Is there a function that will allow me to write "16-05" (i.e. May 2016) into cell B1 and then cell B2 will become "16-04" (April 2016) and cell B4 will become "15-05" (May 2015)? As of now, I write it manually.

Hi,

Add a proper date in B1. i.e. 1/5/2016
In B2 enter : =EOMONTH(B1,-2)+1
In B4 enter: =EOMONTH(B1,-13)+1

Custom format all three cells as "YY-MM"

Enter in B2

Formula:

Please Login or Register  to view this content.

Enter in B4

Formula:

Please Login or Register  to view this content.

v	B
1	16-05
2	16-04
3
4	15-05

AlKey's method works for me with 16-05. However, when I change the "YY-MM" in B1, the formula does not work.

vidhyakrr: Your method is working for me now. Thanks a lot!

Originally Posted by KasperMikkelsen

AlKey's method works for me with 16-05. However, when I change the "YY-MM" in B1, the formula does not work.

vidhyakrr: Your method is working for me now. Thanks a lot!

Please see attached file where cells are formatted as YY-MM

Here are two possibilities for your consideration.
1/

	A	B	C	D
1	Month chosen to analyze:	16-05	16-05	=DATE(YEAR(B1),MONTH(B1),DAY(1))
2	Month before that:	16-04	16-04	=EDATE(C1,-1)
3
4	Same month year before:	15-05	15-05	=EDATE(C1,-12)

2/

	A	B	C
1	Month chosen to analyze:	16-05
2	Month before that:	16-04	=DATE(YEAR(B1),MONTH(B1)-1,DAY(1))
3
4	Same month year before:	15-05	=EDATE(B1,-12)

In case you want to see them in a file with the formatting I have enclosed the file. The dates are formatted yy-mm as a custom date format.