# Write a formula to check if two dates are in same month? [homework]

Posted on November 2nd, 2012 in Excel Challenges , Excel Howtos - 159 comments

Its Home work time folks. Sharpen your Excel pencils and get cracking.

### Find out if 2 dates are in same month

Lets say you have 2 dates in A1, A2.

Q1. What formula tells us if both of them are in same month?

Both dates must be in same month & year!

### You think that was easy, try this then:

The above formula would be very easy for seasoned Excel pros. So I have 2 additional questions:

Q2. How to find out if n dates (A1:An) are in same month?

Q3. How to find if n dates (A1:An) are in a given date range – start date in B1, end date in B2?

Go ahead and post your answers in comments. I am eager to see what creative, fun & awesome solutions you can come up with for this problem.

 Journey of Hurricane Sandy – Animated Excel Chart Show monthly values & % changes in one pivot table
 Written by Chandoo Tags: between formula, date and time, homework, Learn Excel, Microsoft Excel Formulas Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 159 Responses to “Write a formula to check if two dates are in same month? [homework]”

1. Quotenjunkie says:

For Q1 I would say
=TEXT(E6,"mm-yyyy")=TEXT(E7,"mm-yyyy")

2. sam says:

Array Enter
=AND(TEXT(A1:An-1,"mmm-yyyy")=TEXT(A2:An,"mmm-yyyy"))

• DeepField says:

That is a great solution. Got a lot to learn of these array formulas, because my solution was several times longer (comparing min(range) and max(range))

• MS says:

You need to substitute the 'n' with an indirect formula which counts the number of cells with date values in it. Moreover this will not work if the values are in split ranges.

For example, if A1:A4 and A6:A7 have the required values, the answer would be "FALSE" only, irrespective of whether all the six date values are of the same month.

3. lockdalf says:

well, for Q1, as it was mentioned before an equal sign is enough so, as I usually use it YEAR(date1)&"/"&MONTH(Date1)=YEAR(date2)&"/"&MONTH(date2)...
Q2 =>YEAR(A1)&"/"&MONTH(A1)=YEAR(A2;An)&"/"&MONTH(A2:An)... probably array entered, haven't actually tried it at the moment...
Q3 => AND(A1:An>B1;A1:An<B2)

• lockdalf says:

correction of little typo in Q2... YEAR(A2:An)...

4. Quotenjunkie says:

Q2
{=SUM((TEXT(\$A\$1,"mm-yyyy")=TEXT(\$A\$1:\$A\$10,"mm-yyyy"))*1)=COUNTA(A1:A10)}

5. PPH says:

Q1.

=OR(AND(A1>=A2,EOMONTH(A2,0)>=A1),AND(A2>=A1,EOMONTH(A1,0)>=A2))

6. George says:

okay, so Q1 & 2 are solved as follows:

=AND(MONTH(\$A\$1:INDEX(\$A:\$A,COUNTA(\$A:\$A)))=MONTH(\$A\$1),YEAR(\$A\$1:INDEX(\$A:\$A,COUNTA(\$A:\$A)))=YEAR(\$A\$1))

entered as an array.

The index bit is used to define our range.

Q3 is:

=AND(\$B\$1<\$A\$1:INDEX(\$A:\$A,COUNTA(\$A:\$A)),\$A\$1:INDEX(\$A:\$A,COUNTA(\$A:\$A))<\$C\$1)

again, entered as an array.
There must be a more elegant solution, but I can't see it.

• George says:

since you probably want the dates to be inclusive, use this for Q3:

=AND(\$B\$1<=\$A\$1:INDEX(\$A:\$A,COUNTA(\$A:\$A)),\$A\$1:INDEX(\$A:\$A,COUNTA(\$A:\$A))<=\$C\$1)

(array)

7. Chris says:

This array formula works for the given range, taking a min and max date (inclusive) from cells D1 & E1 respectively:

=IF(MIN(IF(\$A\$1:\$A\$30="",1,(\$A\$1:\$A\$30>=D1)*(\$A\$1:\$A\$30<=E1)))=0,FALSE,TRUE)

ignoring any blank cells within the range.

8. PPH says:

=AND(EOMONTH(A1,0)-A2<DAY(EOMONTH(A1,0)),EOMONTH(A1,0)-A2>=0)

• George says:

what does the EOMONTH function do?  It doesn't seem to be an excel standard in 2003.

• Chris says:

EOMONTH returns the date of the last day of a month, using a date reference (A1 in aobve example) and offsetting by a given number of months (0 above).

So, for the last day of the previous month, the formula would be

=EOMONTH(TODAY(),-1)

In Excel 2003, you need to enable the 'Analysis Toolpak', found in Tools, Add-Ins (I think, it's been a while since I used 2003 now!).

Can be a very useful function...

9. Akash Khandelwal says:

For First Problem :

=MONTH(A1)&YEAR(A1)=MONTH(A2)&YEAR(A2)

• Akash Khandelwal says:

This gives result in the form of True or False.

• Jorge says:

The best and most elegant solutions are always the simplest. This works perfectly. good job.

10. DJ says:

My answer for Q1 is '=AND(MONTH(A1)=MONTH(A2),YEAR(A1)=YEAR(A2))

• Bram says:

Just a quick addition, should the date be filled in by someone unfamiliar with Excel, or imported, it might be necessarry to change the reference from A1 or A2 to "datevalue(A1)" or "datevalue(A2)". simply to convert the text value to datevalue.

or you can just convert it to text and crosscheck the last 7 characters ( 4 digits for year, a "/" and 2 digits for month) using the "right" formula or the text formula of Udit below this comment

11. Udit says:

Q1) =TEXT(A1,"MMMYYYY")=TEXT(A2,"MMMYYYY)

Q2) {=MIN(--(TEXT(A1,"MMMYYYY")=TEXT(A2:An,"MMMYYYY")))} will give 0 if all dates arent in the same month and year and 1 if all are in the same month and year.

Q3) {=MIN(--((A1:An>=B1)*(A1:An<=B2)))} will give 0 if all the dates arent within the range and 1 if all the dates are within the range.

12. Lokesh Goyal says:

We can use conditional formatting to highlight the duplicate values in any given matrix.

13. trupti says:

I will calculate salary on the basis of Phone Calls
If Call is done1-150 amount is 0.50 per call
from 150- 250 amount is 0.70 per call
from 250 and above amount is 0.80 per call .
for ex. one Employee done call 260
then frist 150 calls :150*0.50=75/-
after that 150 to 250 i.e 100 Calls
100* 0.70= 70/-
and remaing 10 cal:10*.80=8/-
But Another employee done call only 240
In this case upto 150 call :150*0.50=75/-
remaining 90 call :90* 0.70=63/-
which formula will using for that

• Bob says:

=IF(A1<151,A1*0.5,IF(A1>249,(150*0.5)+(100*0.7)+((A1-250)*0.8),((150*0.5)+((A1-250)*0.7))))

• Ben Niebuhr says:

=MAX(CallCount-250,0)*0.8+MAX(MIN(CallCount,250)-150,0)*0.7+MAX(MIN(CallCount,150),0)*0.5

14. Jai9 says:

Q1. =IF(AND(MONTH(A1)=MONTH(A2)),"True","False")

Q2. =SUMPRODUCT(MONTH(A1:A5)*MONTH(A1))/COUNT(A1:A5)=MONTH(A1)*MONTH(A1)

Q3. =IF(AND(A3>B1,A3<B2),"True","False")

Jai

15. Rajesh says:

=IF(YEAR(A1)=YEAR(A2),IF(MONTH(A1)=MONTH(A2),"same","FALSE"))

16. Detlef says:

Another formula for Q3:
=COUNTIFS(A1:An,">="&B1,A1:An,"<="&B2)=COUNT(A1:An)

17. Alan says:

=IF(MONTH(A2)=MONTH(B2), "Same month", "Different month")

18. Erika says:

=month(a1)=month(a2)

I used the logic you provided in one of the recent homeworks

19. Jeet says:

=YEAR(A1)&MONTH(A1)=YEAR(A2)&MONTH(A2)

20. Dennis says:

=AND(B1-A1<=31, MONTH(A1)=MONTH(B1))

21. Michael Pennington says:

Month & Year in A1 & A2 are the same:
=AND(MONTH(A1)=MONTH(A2),YEAR(A1)=YEAR(A2))

Month & Year in A1:A20 are the same (Array Entered):
=AND((MIN(MONTH(A1:A20))=(MAX(MONTH(A1:A20)))),(MIN(YEAR(A1:A20))=(MAX(YEAR(A1:A20)))))

Month & Year in A1:A20 are between B1 & B2 Dates:
=SUMPRODUCT((A1:A20>=B1)*(A1:A20<=B2))=COUNT(A1:A20)

22. Nunes d'Areeiro says:

For question 1 (verifying if all dates in range A1:An are in the same month (and year)):

{=AND(MONTH(A1:An)&YEAR(A1:An)=MONTH(\$A\$1)&YEAR(\$A\$1))}

_______________________

For question 2 (verifying if all dates in range A1:An are between (and including) the date range that begins with date in B1 and ends with date in B2):

{=AND(A1:An>=\$B\$1;A1:An<=\$B\$2)}

Both {vectorial} formulas,  as indicated by the {} notation.

23. Dennis says:

Q2. How to find out if n dates (A1:An) are in same month?
A1:A4 - the range with dates
=PRODUCT(IF((A1:A4)>=EOMONTH(A1,-1)+1,IF(A1:A4<=EOMONTH(A1,0)-1,1,0),0))

24. Thomas Brunsnes says:

Think

=EOMONTH(MIN(A1:An),0)=EOMONTH(MAX(A1:An),0)

should do the trick for Q2?

25. Ken R says:

As simple as it gets:  =MONTH(A1)=MONTH(A2)

26. Ashok says:

=IF(EOMONTH(A1,0)=EOMONTH(A2,0),"Same Month","Not in a same Month")

• PPH says:

So simple, thanks.

=EOMONTH(A1,0)=EOMONTH(A2,0)

• Hui... says:

@PPH (Jeff)
If that had been a contest, I would give you the prize
Hui...

27. Ashok says:

=IF(EOMONTH(MIN(\$A\$1:\$A\$10),0)=EOMONTH(MAX(\$A\$1:\$A\$10),0),"Same Month","Not a same month")

28. Hugo Uvin says:

Q1 = and(A2-A1<=31;month(A1)=month(A2))

29. Ayush Jain says:

=AND(MONTH(G18)=MONTH(H18),YEAR(G18)=YEAR(H18))

if G18 & H18 are dates

30. Ashok says:

=AND(MIN(\$A\$1:\$A\$10)=\$B\$1,MAX(\$A\$1:\$A\$10)=\$B\$2)

• Squiggler says:

=SUMPRODUCT(--TEXT(C3:C4,"mm")*(-1^{1;2}))=0

Where c3 and c4 are the dates to test!

• Squiggler says:

or in the case of more than 2 dates :

=AND(TEXT(C3:C5,"mm")=TEXT(C3,"mm"))

entered with CTRL+SHIFT+ENTER

• Squiggler says:

RTFQ

=SUMPRODUCT((TEXT(A1:A2,"yyyymm"))-TEXT(A1,"yyyymm"))=0

Where A1:a2 is the range to check

And A1 is any one of the values, all values have to be the same month!

31. Melanie says:

=IF(MONTH(A1)=MONTH(B1),"true","false")

• PPH says:

Unfortunately this will return true if A1 = 11/02/2012 and B1 = 11/03/1999

32. LFC says:

=IF(AND(MONTH(A1)=MONTH(A2),YEAR(A1)=YEAR(A2)),"Yes","No")

33. Rob says:

=IF(MONTH(A1)-MONTH(B1),"DIFFERENT MONTH","SAME MONTH")

34. Luke says:

Here's what I came up with:

(first created a dynamic list for date values in column A)

Q1:

=IF(AND(MONTH(A1)=MONTH(B1),YEAR(A1)=YEAR(B1)),"yes","no")

Q2 (same month and year; don't know how to solve for just month...):

=IF(AND(YEAR(MIN(list))=YEAR(MAX(list)),MONTH(MIN(list))=MONTH(MAX(list))),"yes","no")

Q3:

=IF(AND(MIN(list)>=B1,MAX(list)<=B2),"yes","no")

35. Maggie says:

=CONCATENATE(YEAR(A7),"-",TEXT(MONTH(A7),"00")) = CONCATENATE(YEAR(B7),"-",TEXT(MONTH(B7),"00"))

36. DeepField says:

Q3 is easy:
=AND(MIN(A1:An)>=B1,MAX(A1:An)<=B2)
(A1:An is the data range to be tested against B1 and B2).

37. ZORRO2005 says:

Q1:
=IF(SUM(EOMONTH(TRANSPOSE(A1:A2),1)*{1,-1})=0,”Same month”,”Different month”)

38. ZORRO2005 says:

Q1:
=IF(SUM(EOMONTH(TRANSPOSE(A1:A2),1)*{1,-1}),”Different month”,”Same month”)

39. ZORRO2005 says:

Q2:
=AND(EOMONTH(TRANSPOSE(A1:An),1)=EOMONTH(A1,1))

40. ZORRO2005 says:

Q2:
=STDEV(EOMONTH(TRANSPOSE(A1:A9),1))=0

41. ZORRO2005 says:

Q2:
=STDEV(EOMONTH(TRANSPOSE(A1:An),1))=0
Q3:
=MATCH(COUNT(A1:An),FREQUENCY(A1:An,B1:B2),)=2

• Bonzai Haircut says:

@Zorro -- Thanks for your response to Q3. It was very helpful in solving a similar problem I've been fighting with for sometime.

42. Farid ALVI says:

Formula:
=if(month(a1)=month(a2),(if(year(a1)=year(a2),"Yes","No"),"No")

43. Aniket says:

for the first one:

=MONTH(A1)=MONTH(A2)

44. shrivallabha says:

Q1: EOMONTH(A1,0)=EOMONTH(A2,0)

Q2: =SUMPRODUCT((EOMONTH(A1:A28+0,0)=EOMONTH(A1+0,0))*1)=COUNT(A1:A28)

Q3: =SUMPRODUCT(--(A1:A29>=B1),--(A1:A29<=B2))

45. Himu says:

Put a Date In Cell A1 & Another Date in Cell B1 Then Write This Formula In Cell C1 "=IF(MONTH(A1)=MONTH(B1),"Same Month","Not Same Month")"

46. =MONTH(A1)&YEAR(A1)=MONTH(A2)&YEAR(A2) SHOULD GIVE TRUE

47. Manoj Gupta says:

=(MONTH(A1)=MONTH(B1))

48. stuart says:

Q2 (&Q1) - enter as array
=SUM((TEXT(A1:A5,"mmyy"))/(TEXT(A1,"mmyy")))=COUNT(A1:A5)

Q3  =AND(MIN(A1:A5)>=B1,MAX(A1:A5)<=B2)

49. Rob says:

Question 2

=IF(MONTH(MIN(F2:F10))-MONTH(MAX(F2:F10))=0,"ALL MONTHS SAME","MONTHS NOT SAME")

50. URI WEISS says:

51. cllach says:

B7 is the date, and A1 is init date and A2 end date
=IF((B7>A1) * (B7<A2);TRUE)

52. SCoorg says:

Q 1. =AND(MONTH(A1)=MONTH(A2),YEAR(A1)=YEAR(A2))

Q 2.
{=AND(MAX(MONTH(A1:A10))=MIN(MONTH(A1:A10)),MAX(YEAR(A1:A10))=MIN(YEAR(A1:A10)))}

Q 3.  =AND(MIN(A1:A10)>=MIN(B1:B2),MAX(A1:A10)<=MAX(B1:B2))

53. Squiggler says:

About as short as it gets :

={STDEV(-TEXT(D1:D4,"ym"))=0}

CTRL+SHIFT+ENTER

• stuart says:

squiggler - best i've seen - brill idea uning sd! - there's a lot of potential with this - thank-you

• stuart says:

squiggler - lots more answers out there, many have faults, and yet still this is the best! - thank-you again!!

• Squiggler says:

Well just be aware that it fails if the years are 100 apart, which is easily fixed if needed by changing it to "yyyymm", but I assume the most common case which is that the dates are reasonably close!

54. Frazz says:

I worked out a fairly simple solution to Q1, similar to one of the ones above. Nice answers up there!
=IF(YEAR(A69)=YEAR(A70),"Yes",IF(MONTH(A69)=MONTH(A70),"Yes","No"))

• PPH says:

This won't work because it will only check the month if the year is false. Try this instead:

=IF(YEAR(A69)=YEAR(A70),IF(MONTH(A69)=MONTH(A70),"Yes","No"),"No")

55. Venugopal says:

answer2 =Not sure. Should be similar to answer 3, with start date and end date of the month written in b1 and b2

To anwer second question we need to have reference, let us assume date is cell A1 is the reference date, than following Array formula will give answer
=IF(AND(A1=A1:An),"True","False" )

To answer third question we need to have reference To & From date. Let us assume that these dates are in A2 & B2 cells then following array would give the answer
My dates are in A4 to A18 cells, A18 can be An
=IF(AND(A4:A18<=B2,A4:A18>=A2),"True","False")

57. Sudipto Roy says:

=IF(MONTH(B2)=MONTH(C2),"True","False")

Dates
Dates
Logical

11/3/2012
11/3/2012
True

11/4/2012
10/3/2012
False

11/3/2012
11/2/2012
True

12/3/2012
11/3/2012
False

58. Senthilkumar RM says:

=IF(MONTH(A1)=MONTH(A2)," Same month", " Not")

59. vikas says:

If in between to cells then use text formula
=text(a1,"mm-Yy")=text(a2,"mm-Yy")

60. zur says:

NICE PROVOKING HOMEWORK

61. Rob says:

Q3
=IF(AND(MIN(A1:A9)>=B1,MAX(A1:A9)<=B2),"DATES IN RANGE","DATES NOT IN RANGE")

62. Amardeep says:

Formula to find out if n dates (A1:An) are in same month:
{=AND(AVERAGE(MONTH(A2:A7)) = MONTH(A2),AVERAGE(YEAR(A2:A7)) = YEAR(A2))}

63. Sameer Kakkar says:

=IF(MONTH(A1)=MONTH(A1:A4),"Same month","Different months" )

=IF(AND(A1:A4>=C1,A1:A4<=D1),"True","False")

64. Q1: =MONTH(A1)=MONTH(A2)

Q3: =COUNTIFS(A:A,">"&B1,A:A,"<"&B2)

• Q1: Forgot about the year. This should work:
=AND(YEAR(A1)=YEAR(A2),MONTH(A1)=MONTH(A2))

65. Or even easier: =YEAR(A1)&MONTH(A1)=YEAR(A2)&MONTH(A2)

66. Sudipto Roy says:

Ans to Question 1

11/4/2012
12/4/2012
F

11/4/2012
11/4/2012
T

11/4/2012
11/4/2012
T

11/4/2012
10/4/2012
F

11/4/2012
11/4/2012
T

11/4/2012
11/4/2012
T

12/4/2012
11/4/2012
F

Formula :=IF(AND(YEAR(A1)=(YEAR(B1)),MONTH(A1)=(MONTH(B1))),"T","F")

67. Allan R says:

=IF(MONTH(A1)=MONTH(B1),IF(YEAR(A1)=YEAR(B1),"Equal","Different Year"), "Different Month")  (Excel 2003)

68. Sudipto Roy says:

Ans Q2: Array

Formula:={IF(MAX(MONTH(A1:A10))=MIN(MONTH(A1:A10)),"Same Month","Different Month")}

11/4/2012

11/4/2012

11/4/2012

Ans:

11/4/2012

Same Month

11/4/2012

11/4/2012

11/4/2012

11/4/2012

11/4/2012

11/4/2012

Second example:

11/4/2012

11/4/2012

11/4/2012

Ans:

12/4/2012

Different Month

11/4/2012

11/4/2012

11/4/2012

11/4/2012

11/4/2012

11/4/2012

69. Sudipto Roy says:

Ans 3.

Array Formula:={IF(AND(A1:A10>=C2,A1:A10<=D2),"Within Range","Outside Range")}

11/4/2012

Start date
End Date

11/4/2012

11/4/2012
12/4/2012

11/4/2012

12/4/2012

Within Range

11/4/2012

11/4/2012

11/4/2012

11/4/2012

11/4/2012

11/4/2012

2nd Example:

11/4/2012

Start date
End Date

11/4/2012

11/4/2012
12/4/2012

11/4/2012

12/4/2012

Outside Range

11/4/2012

11/1/2012

11/4/2012

11/4/2012

11/4/2012

11/4/2012

70. MCH says:

Q1:
=A1-DAY(A1)=A2-DAY(A2)

Q2:
{=VARP(A1:A9-DAY(A1:A9))=0}

71. Godfrey says:

=IF(AND(EOMONTH(A1,0)=EOMONTH(B1,0),YEAR(A1)=YEAR(B1)),"OK","NOT")

• PPH says:

Whereas MONTH would only function by the month generically without regard to which year the month is in, EOMONTH will function for the full date. So your formula can simply be:

IF(EOMONTH(A1,0)=EOMONTH(A2,0),"OK","NOT")

72. MCH says:

Q3:
=AND(MIN(A1:A9)>=B1,MAX(A1:A9)<=B2)
=INDEX(FREQUENCY(A1:A9,B1:B2-{1;0}),2)=COUNT(A1:A9)

73. David - Nairobi Kenya says:

For both month and year
=AND(MONTH(a1)=MONTH(a2),YEAR(a1)=YEAR(a2))

Date range A1:An are in the same month
=MONTH(A1)=MONTH(\$A\$1)

01-10-12
=MONTH(A1)=MONTH(\$A\$1)
02-10-12
TRUE
03-10-12
TRUE
04-10-12
TRUE
05-10-12
TRUE
06-11-12
FALSE
07-10-12
TRUE
08-10-12
TRUE
09-10-12
TRUE
10-10-12
TRUE
11-10-12
TRUE
12-10-12
TRUE

Date range A1:An are in the same date range (Start Date B1, End date in B2)
AND(A1>=\$B\$1,A1<=\$B\$2)
01-10-12
01-10-12
=AND(A1>=\$B\$1,A1<=\$B\$2)
02-10-12
05-10-12
TRUE
03-10-12
TRUE
04-10-12
TRUE
05-10-12
TRUE
06-11-12
FALSE
07-10-12
FALSE
08-10-12
FALSE
09-10-12
FALSE
10-10-12
FALSE
11-10-12
FALSE
12-10-12
FALSE

74. Rob says:

=IF(MONTH(A2)=MONTH(B2);TRUE;FALSE)

Date-1
Date-2
Month(A1)
Month(B2)
Equal Month

2-6-2011
1-11-2012
6
11
FALSE

4-5-2012
15-5-2012
5
5
TRUE

75. S Khan says:

Q1 : =AND(MONTH(A1)=MONTH(A2),YEAR(A1)=YEAR(A2))

Q2 : {=AND(MONTH(A1:A5)=MONTH(A1),YEAR(A1:A5)=YEAR(A1))}

Q3 : {=AND((A1:A5)>=B1,(A1:A5)<=B2)}

76. Junizar Iwan Halim says:

Date 1
Date 2

12-Mar-12
6-May-12
They are different in month

My answer : IF(TEXT(B2,"mmm")=TEXT(C2,"mmm"),"Both in the same Month","They are different in month")
Question 2:

3-Apr-12
16-May-12
6-Apr-12
17-May-12
17-Apr-12
15-Apr-12

4
5
4
5
4
4
One or More are different

My answer : IF(B2*COUNTA(B1:G1)=SUM(B2:G2),"They are all in the same month","One or More are different") --> only if you can use helping column..
Question 3:

3-Apr-12
Start Date

15-Apr-12
End Date

3-Apr-12
5-Apr-12
6-Apr-12
12-Apr-12
18-Apr-12
15-Apr-12
16-Apr-12
7-May-12

OK
OK
OK
OK
Not OK
OK
Not OK
Not OK

My answer :[ if this is what it meant ]

either you use a formula or you can work with Conditional Formatting to determine whether a date is in the given range or not

77. Brian says:

=IF(YEAR(A1)-YEAR(A2)=0,(IF(MONTH(A1)-MONTH(A2)=0,"This is the same month","This is a different month")))

78. MCH says:

Q3:
{=COUNT(1/(2-MATCH(A1:A9,B1:B2+{0;1})))=COUNT(A1:A9)}

79. Fernando says:

=SUMPRODUCT(MONTH(OFFSET(A1,0,0,COUNT(A:A)-1,1))=MONTH(OFFSET(A2,0,0,COUNT(A:A)-1,1)))

• Fernando says:

that's for q2

80. Andrew says:

=IF(AND(MONTH(A3)=MONTH(\$A\$1),YEAR(A3)=YEAR(\$A\$1)),"True","False")

This one will take care of a range and can drag and fill:

=IF(AND(E3>=\$E\$1,E3<=\$F\$1),"True","False")

81. SOKOL says:

IF(MONTH(A1)-MONTH(A2)=0,"true","false"

Q1:
=TEXT(A1,"mm/yyyy")=TEXT(A2,"mm/yyyy")

Q2:
=COUNTA(A1:A3)=SUMPRODUCT(--(TEXT(A1:A3,"mm/yyyy")=TEXT(A1,"mm/yyyy")))

Q3:
=COUNTA(A1:A3)=SUMPRODUCT(--(A1:A3>B1),--(A1:A3<B2))

Where A1:A3 can be A1:An

83. Umesh says:

Date 1 in A1
Date 2 in B1

=(MONTH(A1)+YEAR(A1)=MONTH(B1)+YEAR(B1))

Then this formula will give "TRUE" result if month & year are same otherwise it will give "FALSE"

• Squiggler says:

Not if A1=1/1/2012 and B1=1/2/2011 (dd/mm/yyyy)

1+2012=2+2011
2013=2013 true.....

• Umesh says:

Yes you are write;

Thanks for review.

• Umesh says:

Date 1 in A1
Date 2 in B1

=IF(AND(MONTH(A1)=MONTH(B1),YEAR(A1)=YEAR(B1)),"TRUE","FALSE")

Then this formula will give “TRUE” result if month & year are same otherwise it will give “FALSE”

84. Vidya says:

=TEXT(P16,"MmmYY")=TEXT(P17,"MmmYY")

85. Jerome says:

Q2: {=NOT(VAR.P(MONTH(K1:K27)))}

• Jerome says:

Oops, I forgot to check for the year. Revised: {=NOT(VAR.P(MONTH(A1:A20))+VAR.P(YEAR(A1:A20)))}

• Jerome says:

Q3: Looked at some old posts using the median function. This should work if the start and end dates get transposed accidentally.

{=AND(IF(MEDIAN(B1,B2,MIN(A1:An))=MIN(A1:An),TRUE,FALSE),IF(MEDIAN(B1,B2,MAX(A1:An))=MAX(A1:An),TRUE,FALSE))}

86. Zaigham says:

=AND(MONTH(A1)=MONTH(A2),YEAR(A1)=YEAR(A2))

87. Tom C says:

For Question 2, I was trying to get
{=MAX(EOMONTH(\$A\$1:\$A\$5,0))=MIN(EOMONTH(\$A\$1:\$A\$5,0))}

to work, but it seems as if array formulae don't work with Eomonth()
Am I missing something?

88. ROGER MORENO says:

Q1. What formula tells us if both of them are in same year/month?
=IF(YEAR(A1)&MONTH(A1)=YEAR(A2)&MONTH(A2),"Both Dates in same Year/Month", "Dates in Different Year/Month")
Q2. How to find out if n dates (A1:An) are in same year/month? (Create a Table name with the range with dates (TDATES))
=IF(YEAR(MIN(Tdates))&MONTH(MIN(Tdates))=YEAR(MAX(Tdates))&MONTH(MAX(Tdates)),"Dates within the same Year/Month","Dates in Different Year/Month")
Q3. How to find if n dates (A1:An) are in a given date range – start date in B1, end date in B2? (Create a Table name with the range with dates (TDATES))
=IF(AND(MIN(TDates)>=B1, MAX(TDates)<=B2),"Dates within range", "Dates are out of range")

89. digs says:

=IF((YEAR(A2)=YEAR(B2)),IF((MONTH(A2)=MONTH(B2)),"Same"),"Different")

90. Marty says:

=IF(YEAR(A1)&MONTH(A1)=YEAR(A2)&MONTH(A2),"Match","Mis-Match")

91. Vijaykumar Shetye says:

I have named the cells A 1 to A50 as DATA

Q1. What formula tells us if both of them are in same month? Both dates must be in same month & year!
=IF(DATE(YEAR(a1),MONTH(a1),1)=DATE(YEAR(B1),MONTH(B1),1),"same month and year","different month and year")

Q1. What formula tells us if both of them are in same month?
=IF(MONTH(A1)=MONTH(B1),"same month","different month")

Q2. How to find out if n dates (A1:An) are in same month? (any year)
={SUM(IF(MONTH(DATA)=MONTH(B1),1,0))}  array formula

Q2. How to find out if n dates (A1:An) are in same month and year?
{=SUM(IF(DATE(YEAR(DATA),MONTH(DATA),1)=DATE(YEAR(B1),MONTH(B1),1),1,0))}   array formula

Q3. How to find if n dates (A1:An) are in a given date range – start date in B1, end date in B2?
{=SUM(IF(DATA>=B\$1,1,0)*IF(DATA<=B\$2,1,0))}   array formula

Vijaykumar Shetye,
India

92. Doug says:

for question 3

Q3. How to find if n dates (A1:An) are in a given date range – start date in B1, end date in B2?

=IF(AND(A1:A8>=B1,A1:A8<=B2),"in date range","out of date range")

93. Doug says:

For Q2

Q2. How to find out if n dates (A1:An) are in same month?

={AND(MONTH(A1)=MONTH(A1:An))}

94. […] Check if two dates are in same month […]

95. Siddharth Panvalkar says:

=IF((MONTH(A2)-MONTH(A1))*1+(YEAR(A2)-YEAR(A1))*1=0,"same month & year","different month & year")

For two dates in same month & year

96. […] Check if two dates are in same month […]

97. Anderson Coral says:

Hi !

Q1: =SE(MÊS(A2)-MÊS(A1)=0;"Dates are in same month";"Date are in different months")

Q2: {=SE(SOMA(MÊS(C1:C6))/CONT.NÚM(C1:C6)=MÊS(C1);1;0)}
considering dates in range c1:c6

98. Anwar says:

A1 = 12/5/2010 , A2 = 22/5/2010
2) =IF(MONTH(E1)=MONTH(E2);"Same month";"different months")
Result: Same month.
Ans Part2: Does "An" stands for a named date data/range?

99. ES says:

One solution :

=(Month(A1)=Month(A2))*(Year(A1)=Year(A2))=1

100. Peter Thompson says:

Question 2
Say for example there are values in rows 1 to 9 use the following Array formula
{=IF(OR(MONTH(A1:A9)MONTH(C1)),0,1)}
Question 3
=IF(OR(MIN(A1:A9)B2),0,1)

101. chris says:

others beat me to it but i had

=MONTH(A1)&YEAR(A1)=MONTH(A2)&YEAR(A2)

no idea on Q2 and Q3 but ill learn from the responses 🙂

102. Mehmet Gunal OLCER says:

I will use Conditional Formatting to demonstrate the solution. If there is an opportunity to send my worksheet then I will study the problem.

103. Ted says:

Q1: =MONTH(A1-DATE(YEAR(A1),1,0)+1)=MONTH(A2-DATE(YEAR(A2),1,0)+1)
Brute force but it works.

104. Ted says:

Q1:
=AND(MONTH(A1-DATE(YEAR(A1),1,0)+1)=MONTH(A2-DATE(YEAR(A2),1,0)+1),YEAR(A1)=YEAR(A2))
This will check the year as well.

Q2:
{=AND(MONTH(\$A\$1-DATE(YEAR(\$A\$1),1,0)+1)=MONTH(A1:An-DATE(YEAR(A1:An),1,0)+1),YEAR(A1:An)=YEAR(A1:An))}

Q3:
To find how many are in this range:
=COUNTIFS(A1:An,"=" & B1)
To determine if all of them are in the range:
=COUNT(A1:An)=COUNTIFS(A1:An,"=" & B1)

105. Simon says:

Q1 THE SIMPLEST ANSWER IS :-

=EOMONTH(A1,0)=EOMONTH(A2,0)

Which will return the logical TRUE or FALSE

106. Danail says:

My quick and dirty solution to Q2 is :
=SUM(1*((MONTH(A1:A4)&"_"&YEAR(A1:A4))=MONTH(A1)&"_"&YEAR(A1)))=COUNT(A1:A4)
The dates are in the range A1:A4

It returns TRUE if all are in the same month and FALSE other wise.

107. Ashok says:

=IF(MONTH(A5)=MONTH(\$A\$7),"Same Month","-")

=IF(MONTH(A5)MONTH(\$A\$7),"Next","Same Month"))

First formula shows whether that date is in same month or not in B column.
Second formula shows is it in range or not (Here shows if in month which is in first cell. Used dollar sign).

29-Jul-2014 - Privius
31-Jul-2014 - Privius
1-Aug-2014 Same Month Same Month
6-Aug-2014 Same Month Same Month
14-Aug-2014 Same Month Same Month
1-Sep-2014 - Next
12-Sep-2014 - Next
5-Oct-2014 - Next
16-Oct-2014 - Next
24-Nov-2014 - Next
27-Nov-2014 - Next
6-Dec-2014 - Next
11-Dec-2014 - Next

108. Ashok says:

=IF(MONTH(A5)MONTH(\$A\$7),"Next","Same Month"))

109. Ashok says:

=IF(MONTH(A5)
MONTH(\$A\$7),"Next","Same Month"))

110. Hui... says:

Make sure you check out PPH's answer above
=Eomonth(A1,0)=Eomonth(A2,0)

111. Bong says:

Q1: =IF(AND(MONTH(A1)-MONTH(A2)=0,YEAR(A1)-YEAR(A2)=0),"same month","not same month")

Q2: =IF(AND(MONTH(\$A\$1)-MONTH(A2)=0,YEAR(\$A\$1)-YEAR(A2)=0),"same month","not same month")

Q3: =IF(A4>\$A\$3,"out of range",IF(A4<\$A\$2,"out of range","within range"))

112. T.Srinivasa rao says:

For Q1.
if one date in B1 and second date is in B2 then use the following formula in any of the destination cell.
=IF(AND(AND(MONTH(B1)=MONTH(B2)),AND(YEAR(B1)=YEAR(B2))),"Both dates are in same month and year", "One of the field month or rear is not matching")

113. Mahesh says:

=IF(MONTH(A1)=MONTH(A2),TRUE,FALSE)

114. Kishan says:

Q1: '=IF(DATEDIF(C9,D9,"m")=0,"Samemonth","Notinsamemonth")
To check if they are in the same month and year

Q3:
'=IF(DATEDIF(\$B\$1,B2,"m")=0,"Samemonth","Notinsamemonth")

115. Hubert says:

Q1:
=(YEAR(A1)&MONTH(A1))=(YEAR(A2)&MONTH(A2))

Q2:
=SUMPRODUCT(--((YEAR(\$A\$1:\$A\$6)&MONTH(\$A\$1:\$A\$6))=(YEAR(A1)&MONTH(A1))))>1

Q3:
=AND(A1>=\$B\$1,A1<=\$B\$2)

116. Jagdish says:

Hi All,

Extract Month and Year > then concatenate it > Repeat same steps for other date >> Compare as shown in below formula>>

=IF(MONTH(A1)&YEAR(A1)=MONTH(A2)&YEAR(A2),"Same Month and Year","Month and Year NOT Same")

Let me know if something is not clear

117. Mari says:

My answer for the following questions:

Q1. =IF(MONTH (A1)= MONTH(A2), 1, 2)

Q2. =AND(MONTH(\$A\$1:\$A\$7)=MONTH())

Q3. =AND(A3>\$B\$2, A3<\$B\$3)

118. Sam says:

=if(and(month(A1)=month(A2), year(A1)=year(A2)), "same month", "not same month")

=if(and(month(A1)=month(An), year(A1)=year(An)),"same month", not same month")

=if(and(month(An)>month(B1), year(An)>year(B1), month(An)<month(B2), year(An)<year(B2)), "in a given date range", "not in range")

119. Ritesh says:

for the first problem: Enter array formula-
=IF(AND(TEXT(A1:A10,"mmm-yyyy")=TEXT(A2:A11,"mmm-yyyy")),"Yes","No")

for the second formula: Enter Array formula
=IF(AND(\$A\$1:\$A\$12>=F2,\$A\$1:\$A\$12<=H2),"Yes","No")

120. Ueritom says:

You mean, the same year too?

I'd do like this (considering A1 and A2):

=AND(MONTH(A1)=MONTH(A2);YEAR(A1)=YEAR(A2))

Is there a simpler way to do that?

Ueritom

121. Rubandoss says:

=IF(MONTH(A1)=MONTH(B1),"Yes","no"). assuming my data is in cell a1 & B1

• Hui... says:

@Rubandoss

Your solution will say that 15 Dec 2014 is the same as 15 Dec 2015

You just need to be careful and ensure if that is what you really want

122. dan says:

=IF(AND(YEAR(A1)=YEAR(A2); MONTH(A1)=MONTH(A2));"same month";"diferent month")

123. Sourav says:

=IF(AND(MONTH(A1)=MONTH(B1)),"Same Month","Different Month")

Q1 | "=MONTH(A1)=MONTH(A2)"

Q2 | We create a parallel column B with the formula "=month(An)". The
check can be performed using the formula "=AVERAGE(Bn) = B1 or
B2 .... Bn".

Q3 | "=AND(MIN(A1:A10)=B2)"

125. Prashant Giri says:

All these answer by without using array

Ans 1 is MONTH(A20)=MONTH(A21)

Ans 2 is MONTH(A1)=AVERAGE(INDEX(MONTH(A1:A20),))

Ans 3 is for start date TEXT(MIN(INDEX(I20:I50,)),"DD-MM-YY"); and for end date =TEXT(MAX(INDEX(I20:I50,)),"DD-MM-YY")

126. Virender sharma says:

=IF(DAY(A1)=DAY(B1),"Same month", "different month")

u may also try

127. Virender sharma says:

=IF(DAY(A1)=DAY(B1),"Same day", "different day")

128. Yves S says:

Q1: =AND(MONTH(A1)=MONTH(A2),YEAR(A1)=YEAR(A2))

Q2: = {SUMPRODUCT((MONTH(A:A)=MONTH(\$A\$1))*(YEAR(A:A)=YEAR(\$A\$1)))}

Q3: ={SUMPRODUCT((A:A>=\$B\$1)*(A:A<=\$B\$2))}

 Journey of Hurricane Sandy – Animated Excel Chart Show monthly values & % changes in one pivot table