Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

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

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

Its Home work time folks. Sharpen your Excel pencils and get cracking.
Write a formula to check if two dates are same month? [homework]

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?

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

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

  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)

  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 

  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:

    Answer for Q1

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

  27. Ashok says:

    Answer of Q2 -

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

    Answer of Q3 -

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

    • Squiggler says:

      How about just simply :

      =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

          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!
           

  31. Melanie says:

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

  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:

    answer 1: month(A1)=month(A2)
    answer2: ={MIN(MONTH(A1:A(n)))=MAX(MONTH(A1:A(n)))}

  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:

    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)

  56. Keyur Adhiya says:

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

  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:

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

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

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

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

     
     
     
     
     
     
     
     

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

  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”

  82. Benzadeus says:

    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”

  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:

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

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

  95. Anderson Coral says:

    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

  96. Anwar says:

    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?

  97. ES says:

    One solution :

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

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

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

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

  101. Ted says:

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

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

  103. Simon says:

    Q1 THE SIMPLEST ANSWER IS :-

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

    Which will return the logical TRUE or FALSE

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

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

  106. Ashok says:

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

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

  107. Ashok says:

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

  108. Hui... says:

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

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

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

  111. Mahesh says:

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

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

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

  114. Jagdish says:

    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

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

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

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

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

Leave a Reply