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

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!*

Go ahead and post your answer in comments.

### 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:A n) are in same month?**

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

### Post your answers in comments

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.

Click here to post your answer.

PS: Click here for more Excel home works, quizzes & challenges.

Clue: Click here for a clue. Got it?

### Leave a Reply

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

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

For Q1 I would say

=TEXT(E6,"mm-yyyy")=TEXT(E7,"mm-yyyy")

Array Enter

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

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))

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.

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)

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

Q2

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

Q1.

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

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.

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)

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.

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

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

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...

For First Problem :

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

This gives result in the form of True or False.

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

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

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

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.

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

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

=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))))

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

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

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

Another formula for Q3:

=COUNTIFS(A1:An,">="&B1,A1:An,"<="&B2)=COUNT(A1:An)

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

=month(a1)=month(a2)

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

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

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

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)

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.

Q2. How to find out if n dates (A1:A

n) 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))

Think

=EOMONTH(MIN(A1:A

n),0)=EOMONTH(MAX(A1:An),0)should do the trick for Q2?

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

Answer for Q1

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

So simple, thanks.

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

@PPH (Jeff)

If that had been a contest, I would give you the prize

Very neat answer.

Hui...Answer of Q2 -

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

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

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

if G18 & H18 are dates

Answer of Q3 -

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

How about just simply :

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

Where c3 and c4 are the dates to test!

or in the case of more than 2 dates :

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

entered with CTRL+SHIFT+ENTER

RTFQ

How about

=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!

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

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

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

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

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")

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

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).

Q1:

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

Q1:

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

Q2:

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

Q2:

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

Q2:

=STDEV(EOMONTH(TRANSPOSE(A1:An),1))=0

Q3:

=MATCH(COUNT(A1:An),FREQUENCY(A1:An,B1:B2),)=2

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

Formula:

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

for the first one:

=MONTH(A1)=MONTH(A2)

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))

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")

"=MONTH(A1)&YEAR(A1)=MONTH(A2)&YEAR(A2) SHOULD GIVE TRUE

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

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)

Question 2

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

answer 1: month(A1)=month(A2)

answer2: ={MIN(MONTH(A1:A(n)))=MAX(MONTH(A1:A(n)))}

B7 is the date, and A1 is init date and A2 end date

=IF((B7>A1) * (B7<A2);TRUE)

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))

About as short as it gets :

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

CTRL+SHIFT+ENTER

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

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

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!

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"))

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")

answer1 =month(A1)&year(a1)=month(a2)&year(a2)

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

answer3 =AND(A1>=$B$1,A1<=$B$2)

Answer1=month(A1)&year(A1)=month(A2)&year(A2)

Answer2

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" )

Answer3

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")

=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

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

If in between to cells then use text formula

=text(a1,"mm-Yy")=text(a2,"mm-Yy")

NICE PROVOKING HOMEWORK

Q3

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

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))}1st answer : =IF(AND(MONTH(A1)=MONTH(A2),YEAR(A1)=YEAR(A2)),"True","False")

2nd answer:

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

3rd answer:

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

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))

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

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")

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

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

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

Q1:

=A1-DAY(A1)=A2-DAY(A2)

Q2:

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

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

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")

Q3:

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

=INDEX(FREQUENCY(A1:A9,B1:B2-{1;0}),2)=COUNT(A1:A9)

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

=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

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)}

Date 1

Date 2

Answer

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

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

Q3:

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

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

that's for q2

=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")

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

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"

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

1+2012=2+2011

2013=2013 true.....

Yes you are write;

Thanks for review.

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”

Please review the same

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

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

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

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))}

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

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?

THE ANSWERS ARE VERY SIMPLE:

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")

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

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

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

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")

For Q2

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

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

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

=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

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

Hi !

Answers:

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

A1 = 12/5/2010 , A2 = 22/5/2010

Answer Part1: 1) =MONTH(E1)=MONTH(E2)=TRUE

2) =IF(MONTH(E1)=MONTH(E2);"Same month";"different months")

Result: Same month.

Ans Part2: Does "An" stands for a named date data/range?

One solution :

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

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)

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 🙂

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

Q1: =MONTH(A1-DATE(YEAR(A1),1,0)+1)=MONTH(A2-DATE(YEAR(A2),1,0)+1)

Brute force but it works.

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)

Q1 THE SIMPLEST ANSWER IS :-

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

Which will return the logical TRUE or FALSE

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.

=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

By mistake second formula pasted incorrect please read it as follows.

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

=IF(MONTH(A5)

MONTH($A$7),"Next","Same Month"))

Make sure you check out PPH's answer above

=Eomonth(A1,0)=Eomonth(A2,0)

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"))

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")

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

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")

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)

Hi All,

Here is my answer.

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

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)

=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")

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")

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

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

@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

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

=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)"

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")