Identifying if 2 dates are alike and entering the appropriate month

[Gordon1963]

I have spreadsheet similar to this:

This Year Last Year
Date Event Date Event
3/1/17 Event 1 March 3/6/16 Event 1
5/6/17 Event 2 May
4/12/17 Event 3 April 4/15/16 Event 3
February 2/20/17 Event 4

I need a formula to look at This Year event 1 date and last year event 1 date and tell me the month. Where it gets tricky, if this year had an event and last year didn't it should give me this year event month and likewise if this year didn't have an event. See above how the month should be displayed.

 

[StephenCrump]

Assuming there will always be at least one date specified per event, you could do something like this:

E3: =TEXT(IF(ISBLANK(B3),C3,B3),"MMMM") copy down the column

Excel 2010
	A	B	C	D	E
1		This Year	Last Year		Month
2	Event	Date	Date
3	Event 1	1 Mar 2017	6 Mar 2016		March
4	Event 2	6 May 2017			May
5	Event 3		15 Apr 2016		April
Sheet1

But what if the months for 2016 and 2017 are different, e.g. as would often be the case for an Easter event?

 

[Gordon1963]

Stephen-Thanks! It works. As for Easter or other events, if it falls in another week or month, both events would be on separate weeks or month.

 

[StephenCrump]

You're welcome, I'm glad it works for you.

 

[Gordon1963]

Stephen or All,

I'm in retail so we use 4-5-4 calendar, so to get the correct month for each event on the same line, so I placed 2 columns between both years and used the following formula:

This is 2017 =TEXT(DATE(YEAR(C9),MATCH(C9-DATE(YEAR(C9),1,0),{0,29,64,92,120,155,183,211,246,274,302,337,365}),1),"mmmm")

This is 2016 =TEXT(DATE(YEAR(C9),MATCH(C9-DATE(YEAR(C9),1,0),{0,31,66,94,122,157,185,213,248,276,304,339,367}),1),"mmmm")

However, if a row is blank, it enters January, so this formula I received from Stephen above =TEXT(IF(ISBLANK(B3),C3,B3),"MMMM") doesn't work because the 4-5-4 formula above puts in January in the blank column on one side and the correct month on the other so I received a false. How can I get the 4-5-4 formula to leave that cell blank? or can I combine both formulas somehow>

 

[StephenCrump]

Perhaps something like this:

B2: =IF(ISBLANK(A2),"",TEXT(DATE(YEAR(A2),MATCH(A2-DATE(YEAR(A2),1,0),{0,29,64,92,120,155,183,211,246,274,302,337,365}),1),"mmmm"))

E2: =IF(ISBLANK(D2),"",TEXT(DATE(YEAR(D2),MATCH(D2-DATE(YEAR(D2),1,0),{0,31,66,94,122,157,185,213,248,276,304,339,367}),1),"mmmm"))

G2: =IF(B2="",E2,B2)

Excel 2010
	A	B	C	D	E	F	G
1	2017 date	4-5-4 month		2016 date	4-5-4 month		Result
2	1 Mar 2017	February		6 Mar 2016	March		February
3				15 Apr 2016	April		April
4	1 Apr 2017	March					March
Sheet1

 

[Gordon1963]

Stephen - That was PERFECT! Thanks!