Excel month & year

[sandeep_sarkar]

Dear Mr Excel Forum,

Greetings. Hope all is going well.

Like to take your advice. I entered this formula in cell F2 : =MONTH(B2:D2)&YEAR(B2:D2)=MONTH($G$1)&YEAR($G$1).

But it is not working after pressing ctr+shift+enter.

Whatever month & year I type in G1 [say Sep-20] it will show TRUE or YES beside the names in Column F.

Please help.

Sandeep
Kolkata

 

[CA_Punit]

Do you want Date 1 or Date 2 or Date 3 to be equal to G1 or Date 1&Date2&Date to be equal to G1 to return True or False.

The Formula which you have entered is only returning is D1=G1

 

[sandeep_sarkar]

1) When I type Sep-21 in F1, the matching month-year will be shown in Result Col-E (beside name) - marked as 'Renewal'.

2) When I type Jul-20 in F1, the matching month-year will be shown in Result Col-E (beside name) - marked as 'Renewal'.

3) When I type Oct-20 in F1, the matching month-year will be shown in Result Col-E (beside name) - marked as 'Renewal'.

And so on.

Thanks,

Sandeep

 

[CA_Punit]

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E	F
2	Name	End Date1	End Date2	End Date3	Result	Sep-21
3	x	Sep-21	Oct-21	Nov-21	Renewal
4	y	Jul-21	Aug-21
5	z	Aug-21	Sep-21		Renewal
6
7
Sheet4

Cell Formulas
Range	Formula
E3:E5	E3	=IF(ISNUMBER(MATCH($F$2,$B3:$D3,0)),"Renewal","")

 

[CA_Punit]

Also i more question you have 8-Nov-2018 in 3rd table It will never match Nov 18.

The formula will only consider 1 st of Month

 

[CA_Punit]

Try This for above error

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E	F
2	Name	End Date1	End Date2	End Date3	Result	Sep-21
3	x	Sep-21	Oct-21	Nov-21	Renewal
4	y	Jul-21	Aug-21
5	z	Aug-21	Sep-21		Renewal
Sheet4

Cell Formulas
Range	Formula
E3:E5	E3	=IF(ISNUMBER(MATCH(MONTH($F$2)&"-"&YEAR($F$2),MONTH($B3:$D3)&"-"&YEAR($B3:$D3),0)),"Renewal","")

 

[sandeep_sarkar]

Mr. Punit,

Thank you for your advice - its working. I have also tried with the following -

=IF(SUMPRODUCT((MONTH(B2:D2)=MONTH($G$1))*(YEAR(B2:D2)=YEAR($G$1)))=1,"Renewal","")

Thanks a lot,

Sandeep

 

[CA_Punit]

Glad it worked for you

and Thanks for the feedback