Its Home work time folks. Sharpen your Excel pencils and get cracking.
![Write a formula to check if two dates are same month? [homework]](https://img.chandoo.org/hw/check-two-dates-in-same-month-excel-homework.png)
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: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?
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?
162 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")
=MONTH(A1)&YEAR(A1)=MONTH(A2)&YEAR(A2)
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: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))
Think
=EOMONTH(MIN(A1:An),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")
=IF(DAY(A1)=DAY(B1),"Same month", "different month")
u may also try
=IF(DAY(A1)=DAY(B1),"Same day", "different day")
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))}
=EOMONTH(A2,0)=EOMONTH(A1,0)
{=COUNT(IF(MONTH(A1:A3)&YEAR(A1:A3)=MONTH(A1)&YEAR(A1),0))=COUNTA(A1:A3)}
This is to check whether all cells in the range are of same month