How many Mondays between two dates? [homework]

Posted on December 18th, 2015 in Excel Challenges - 129 comments

count-of-mondays-between-two-datesHere is a quick but challenging homework problem for you.

Let’s say you have two dates – Start and End.

And you want to find out how many Mondays are there between those two dates (including the start & end dates).

What formula would give the answer?

Please post your formulas / VBA functions / DAX measures in the comments section.

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

129 Responses to “How many Mondays between two dates? [homework]”

  1. MF says:

    I have a post related to this question:
    Calculate number of a specific day between two dates | wmfexcel
    Hope you like it!
    Merry Christmas in advance!

  2. prashant says:

    for Excel 2010 & above


    SD ED are start date & end date resp.

  3. Bob Phillips says:

    Your solution might be a tad more efficient using a numeric test rather than a string test


    It might also be worth pointing out that the dates in B1 & B2 can be any order, B1 does not have to be the earlier date.

    • Mark says:

      Hi Bob,
      Love the solution, very neat and crisp. Even though i use the functions in your solution and i still confused about the row(Indirect(B1&":"&B2)) bit.

      To take a simple example If B1 was 2 and B2 was 6 what would Indirect(2:6) evaluate to?

      Applying the row function to this then yields the actual values of the reference range (2;3;4;5;6).

      Would you mind explaining a little if its not too much for you?



      • NARAYAN says:

        Hi Mark ,

        Let us start from the outside and move inwards.

        It is clear that we need a function to sum values ; since this has to operate on an array of values , SUMPRODUCT is an obvious choice.

        Now we need an array of values , which will have 1 for a Monday , and 0 for all other days of the week. This we can get by using the WEEKDAY function , and checking whether it returns the value for a MONDAY. Depending on the second parameter of the WEEKDAY function , the value used for checking will change.

        The default value for this second parameter is 1 , and this gives the weekdays from 1 through 7 , with 1 signifying Sunday , and 7 signifying Saturday ; thus , in this set , Monday would be 2.

        Now , we need to pass an array of dates to the WEEKDAY function ; this will be an array of dates from StartDate through EndDate , both dates inclusive.

        This we can do using the ROW function.

        Unfortunately , the ROW function acts on values , not on cell references ; thus , given the formula :


        we get an array of values {7;8;9;10;11;12;13;14;15;16;17}.

        To get the same array of values using cell references , we would need to use :


        Any other column reference can be used in place of column A e.g.


        would return the same array of values from 7 through 17.

        The situation we have in our case is that the StartDate is in a cell reference B1 , and the EndDate is in another cell reference. Using these references directly , as in :


        would only result in an array of two values {1;2} !

        Thus , to use the values of StartDate and EndDate through their cell references , one way would be to use the INDIRECT function ( there are other ways too ).

        The complication here is that the INDIRECT function takes in parameters which are strings ; hence we cannot use :


        To generate the string for the INDIRECT function , we use the CONCATENATE function , or its shortcut , the & symbol. Hence :

        =ROW(INDIRECT(B1 & ":" & B2))

        This generates an array of values from the StartDate through the EndDate , both dates inclusive.

        • Mark says:

          Hi Narayan,

          I have only just seen your reply to my question.

          Thank you for taking the time to explain this to me.

          I'm much clearer on how it works now.



      • Jan Martens says:
        Here you can learn a lot about array formulas.

  4. Michael (Micky) Avidan says:


  5. mahesh says:

    hi prashant
    result :2 this is wrong

  6. Bertrand says:

    In DAX, assuming you have a Date table

    ROW (
    "Count of Mondays", COUNTROWS (
    FILTER (
    DateTable[Date] > DATEVALUE ( "2014-12-31" )
    && DateTable[Date] < TODAY ()
    && WEEKDAY ( DateTable[Date] ) = 2

  7. Bill says:

    Mine certainly isn't the shortest formula, but is more intuitive for me to understand:
    =QUOTIENT((End-Start),7) + OR(WEEKDAY(Start)=2,WEEKDAY(End)=2)

    The second term covers the case where the start or the end date is on a Monday, which is needed to cover for partial weeks.

  8. Gianluca says:


    • Gianluca says:

      Not working for periods less than 7 days...
      This should fix the problem:

    • Michael (Micky) Avidan says:

      January 2016 has 3 Mondays (1/1/2016-31/1/2016)
      Your suggested formula returns: 3

      • Michael (Micky) Avidan says:

        Sorry for the TIPO:
        January 2016 has 4 Mondays (1/1/2016-31/1/2016)

        • Gianluca says:

          the formula returns 4 for DS=1/1/2016 and SD=31/1/2016.
          The result is correct.

          • Michael (Micky) Avidan says:

            I was referring to your first(!) formula.
            (You posed a new and longer one WHILE I was typing my comments).
            The last one works OK - however, as there might be many sorts of formulas - I always look for shorter formulas.
            So far the:
            is the shortest that also works as expected.

          • Gianluca says:

            @Michael (reply to post below)
            Shorter then your shortest... (array formula)


  9. Harold Mude says:

    How about this one:


  10. Harold Mude says:

    Btw. the picture above has Sundays marked.

  11. Mehmet Gunal OLCER says:


    Where LASTDAY is assumed to be later or equal to FIRSTADAY.

    • Mehmet Gunal OLCER says:

      Let me give another solution.


      where DAY_1 and DAY_2 are two DIFFERENT dates.

  12. Josh Saavoss says:

    Function monday_count(date1 As Date, date2 As Date)
    Dim i As Long: Dim count As Long
    For i = date1 To date2
    If Weekday(i) = 2 Then count = count + 1
    Next i
    monday_count = count
    End Function

  13. Ashish says:


  14. Jeff Fordon says:


  15. Jeff 1 says:

    typo, should be


  16. Jon says:


    Where A1 = Start Date
    A2 = End date

  17. Gopi Krishna says:


    Will return the number of Mondays.

    • Gopi Krishna says:

      The above formula will return incorrect number of Mondays if the start date and end date are same..

      The following formula will return correct number of Mondays..

      =IF(AND(G15=F15,WEEKDAY(G15,1)=2),1,IF(G15>=F15,IF(AND(WEEKDAY(F15,1)=2,WEEKDAY(G15,1)=2),QUOTIENT((G15-1)-(F15+1),7)+2,IF(OR(WEEKDAY(F15,1)=2,WEEKDAY(G15,1)=2),QUOTIENT((G15-1)-(F15+1),7)+1,QUOTIENT((G15-F15),7))),"Start Date later than End Date"))

  18. Darin Scott says:

    Here is my try at this:

    Where Col E contains dates
    StartDate & StopDates are input cells
    DayChosen is a dropdown list Sun, Mon, Tue,... which converts to 7,1,2,... based on day chosen

  19. Jon says:


    A1 = Start date
    A2 = End Date

  20. ramjiyahoo says:

    Google gives easy answer thru this website

  21. Sorry for me VBA is easier to use across workbooks...

    Function HowManyMonday(datStart As Date, datEnd As Date)
    ' Returns the number of Mondays between two dates.
    Dim i As Long, j As Integer

    For i = datStart To datEnd

    j = j + Abs(Weekday(i, vbMonday) = 1)
    Next i
    HowManyMondays = j
    End Function

    In VBA Weekday(i, vbMonday) = 1 returns TRUE which equals -1 that is why ABS is used. A minus would do the same but more obscure.

    • Michael (Micky) Avidan says:

      You have a slight TIPO in the Function's Name.
      If I may suggest (especially for Maintenance & User Friendly reasons) the following UDF:
      Function HowManyMondays(datStart As Date, datEnd As Date, D As Integer)
      ' Returns the number of days "D" between two dates.
      Dim i As Long, j As Integer
      For i = datStart To datEnd
      HowManyMondays = HowManyMondays + Abs(Weekday(i, D) = 1)
      Next i
      End Function

      • Michael (Micky) Avidan says:

        As a better praxis the UDF's name should be changed to: HowMany_D_Days

        • Michael (Micky) Avidan says:

          Function HowMany_D_Days(datStart As Date, datEnd As Date, D As Integer)
          ‘ Returns the number of days “D” between two dates.
          Dim i As Long, j As Integer
          For i = datStart To datEnd
          HowMany_D_Days = HowMany_D_Days + Abs(Weekday(i, D) = 1)
          Next i

  22. Gordon says:

    =IF(SD<=ED,INT(WEEKDAY(SD,12)/7)+ INT((ED-SD+WEEKDAY(SD,3))/7),"error -start date is after end date")
    Amazed at the variety of answers - my formula is a little bit wasteful. I am sure there is a simpler way, but I tested it and it works.

  23. Jason Morin says:


  24. K?vanç Y?ld?z says:

    #1 =SUMPRODUCT(--(TEXT(ROW(INDIRECT(SD&":"&ED)),"DDDD")="Sunday"))
    But this formula is English.

    Turkish formula use "GGGG"


  25. Leonid says:


  26. Michael (Micky) Avidan says:

    As for your really short formula (posted December 19, 2015 at 12:40 am) Please check it against:
    SD = 01/01/2016
    ED = 28/09/2016
    It should return: 39 (not 38)

  27. Lewis Kirby says:

    Here's mine:
    =((A2-WEEKDAY(A2,3))-(A1-WEEKDAY(A1, 3)))/7+IF(WEEKDAY(A1,3)=0,1,0)
    Using WEEKDAY(A1,3) gives you 0 for Monday, then subtracting the two and dividing by 7: ((A2-..)-(A1-..))/7 gives the number of Mondays, but not if the first date A1 is a Monday, so we have to add 1 in that case.

    BTW what is the advantage of using the function QUOTIENT instead of simply dividing?

  28. SAGAR MOHITE says:


    END DATE - A2

    S, M, T, W, T, F, S = 1, 0, 6, 5, 4, 3, 2

    Which day you want to count arrenge serial no. that way i.e. Monday = 0

  29. Alan says:

    if only for Mondays


  30. Jan Martens says:


    Where Monday is the first day of the week.

    If SD=Monday and this Monday should be included, then ((Weekday, 3) =1)*1 +formula

    Have a good day.

  31. Alan says:

    This appears to work

  32. Mindaugas says:


    END DATE – A2

  33. mma173 says:


    Why are you proposing complicated solutions?

    IMHO, the easiest way would be to get the floor of (the difference between SD, ED, and the adjustment to first Monday using Weekday) / 7.

    • Michael (Micky) Avidan says:

      As per your verbal explanation - would you be kind enough to present the actual FORMULA(!) that returns: 66 FRIDAYs for:
      SD = 01/01/2016
      ED = 31/03/2017

      • mma173 says:

        Without the Floor part, the formula should look something like:
        =((End Date-Start Date)+(7-(WEEKDAY(Start Date)-2)))/7+1

        I forgot to mention the (+1) required to include the first Monday.
        You can also simplify the adjustment part.


        • Michael (Micky) Avidan says:

          Sorry, but if you find it difficult to present a FULL FORMULA (without "look something like") - I have nothing more to say.

        • Michael (Micky) Avidan says:

 the way I presume that Chandoo's basic meaning was that the requested formula should work as expected by counting all sort of weekdays (not only Mondays).

          • mma173 says:

            The formula I presented is the full formula. I tried it and it works. Sorry for using the wrong English expression ????

            Moreover, it can be adjusted to count another day of the week.

  34. Danny Baetens says:

    =IF(WEEKDAY(enddate;2)=1; 1+ INTEGER(DAYS(enddate;startdate)/7); INTEGER(DAYS(enddate;startdate)/7))

  35. Michael (Micky) Avidan says:

    Please examine the linked picture:

  36. Abhijeet says:


    This macro pull all Mondays then cout
    Dim dStart As Date
    Dim dEnd As Date
    Dim rw As Integer

    dStart = Range("A2").Value
    dEnd = Range("A3").Value

    rw = 2
    While dStart < dEnd
    If Weekday(dStart) = vbMonday Then
    Cells(rw, 3).Value = dStart
    Cells(rw, 3).NumberFormat = "m/d/yyyy"
    rw = rw + 1
    End If
    dStart = dStart + 1

  37. param nayak says:

    a mathematicl approach


  38. Denys Calvin says:


    • Denys Calvin says:

      Oops! Didn't read the instructions. Small adjustment added at the end of the above to deal with "(including the start & end dates)"


  39. param nayak says:

    a mathematical approach


    start day a1
    end day a2

  40. Jan Martens says:

    Finished my homework.
    This formula can return the number of any weekday. The 'daynumber' in the formula can be hard-coded or a named range. Enter 1 for Sunday and 7 for Saturday
    This formula works.

    INT((DATEDIF (SD +7+"daynumber"-WEEKDAY(SD,17)-7*(WEEKDAY (SD, 17)<="daynumber "), ED,"d")) /7)+1

    Have a good day.

  41. Swapnil Shah says:


    This formula is based on simple division rule , considering the 0th date in excel falls on saturday , we need to add one to our count, if any of start date or End date falls on Monday.

    SD : Start Date
    ED : End Date

  42. Farzad says:


    A1 is Start Date
    A2 is End Date

  43. Seema says:


    • Gopi Krishna says:

      =IF(F15>G15,"End Date must be later than start date",IF(AND(F15=G15,WEEKDAY(F15,1)=2),1,IF(AND(WEEKDAY(F15,1)=2,WEEKDAY(G15,1)=2),QUOTIENT((G15-1)-(F15+1),7)+2,IF(OR(WEEKDAY(F15,1)=2,WEEKDAY(G15,1)=2),QUOTIENT((G15-1)-(F15+1),7)+1,QUOTIENT((G15-F15),7)))))

      This formula works for all situations.. even if start date is equal to end date

  44. Satya Murthy says:

    = INT( (WEEKDAY(A1-2) + A2 - A1) / 7)

    where the cell A1 holds the start date and A2 holds the end date.

    This formula works correctly for all situations. It can be tweaked for any day of the week. It is very simple and elegant.

    In the expression (A1-2), 2 denotes Mon. Use 1 for Sun, 3 for Tue, 4 for Wed, 5 for Thu, 6 for Fri, 7 for Sat.

  45. Robert says:

    What about this?

    =EndDate - StartDate - NETWORKDAYS.INTL(StartDate,EndDate,12) + 1

    where 12 means Mondays "are weekends"

    Gives me 8 for example of 01/12/2015 - 31/01/2016

    Shouldn't this be the simplest formula to get the job done?

  46. Dheeran says:


    were wkdsum=IF(wkd>7,dsum/7-1,dsum/7)...

  47. Dheeran says:

    made a mistake...should read IF(WEEKDAY(start)WEEKDAY(end),INT(IF(WEEKDAY(start)-_

  48. Dheeran says:

    there needs to be a "not equal" sign where the "***" is in the formula...IF(WEEKDAY(start)***WEEKDAY(end),INT(IF(WEEKDAY(start)-_

    no idea why char aren't uploading...

  49. Manuel Vasquez says:

    SD = Start Date
    ED = End Date


  50. Ray Gaurav says:

    =Weeknumber (XX,2)-Weeknumbr (YY,2)
    Were xx = cell which has end date and yy = cell which has begiate

  51. Brian says:


  52. Pablo says:

    It seems that many answers include a variation of this:


    where the initial date is on A1 and the final date on A2

    • mma173 says:

      This formula is neat. However, I prefer the ones based on simple division and small adjustment. The reason is that Sumproduct is less efficient; much more calculations (comparsions) are being done in the background.

  53. Arkadiusz says:


  54. Paul S. says:

    Starting date in A1 and Ending date in A2


  55. Kcdog says:


    A1 is Start Date
    A2 is End Date

  56. GMF says:

    Adapting Mike Girvin's formula to find the number of Friday 13ths between two dates, it can be simplified just to look for a weekday.


  57. Vishwamitra says:

    Sub HowManyMonday(startDate As Date, endDate As Date)

    Dim startDay As String

    If startDate > endDate Then Exit Sub
    startDay = VBA.Format(startDate, "ddd")
    countMonday = VBA.Round((VBA.DateDiff("d", startDate, endDate) / 7), 0)
    If startDay = "Mon" Then countMonday = countMonday + 1 Else countMonday = countMonday
    MsgBox "There are " & countMonday & " Monday(s) between " & startDate & " and " & endDate
    End Sub

  58. Alex Groberman says:

    How about:




  59. SAURABH SHUKLA says:


    Here, Cell A1 = Start Date
    and Cell B1 = End Date

  60. Artem says:


    I live in country where Sunday is last day of week, so Monday = 1.

  61. John Jairo V says:


    What about this:
    A1: Start Date, B1: End Date


  62. Peter says:

    This should work:


  63. Vangelis M says:

    One more


    where for
    how_many=11 : the number of Mondays is calculated
    how_many=12 : the number of Tuesdays is calculated
    how_many=17 : the number of Sundays is calculated

    However John Jairo Vs answer is excellent!!!!

  64. Gopi Krishna says:


  65. Pedro says:


    Press Ctrl + Shift + Enter

    Where M5 anda M6 are the dates

  66. BL says:

    Why not

    =FLOOR( (A2-A1-WEEKDAY(A2,3)) / 7 ,1) + 1

    A1 = Start Date
    A2 = End Date

    or ignore date order

    =FLOOR( (ABS(A2-A1) - WEEKDAY( MAX(A2,A1) ,3)) /7 ,1) + 1

  67. Mohd Mukeet says:

    A1 = 1-Dec-15
    B2 = 22-Dec-15

    Press CTRL+SHIFT+ENTER in the above formula
    = 3 (Monday)

  68. Haz says:


  69. Eric L. says:


    Got this idea from the formula for "Reverse Coding" survey results.

    (Number of Choices-x)+1

  70. Sabeesh says:


    Where Celll A1contains the start date and A2 contains the end date

  71. Jon Valz says:

    I think I've got q couple solns:




    As in, the solution should be the number of weeks between the Monday of each week.

  72. Jan Martens says:

    Hi, found this on the Web. The formula was written by Laurent .

    =INT((Ed-MOD(Ed- daynumber,7)-SD+7)/7)

  73. Anant Jain says:

    Answer is to calculate monday between 2 dates is:


    SD = Start Date
    ED = End Date

  74. Chirayu says:

    Use CTRL + SHIFT + ENTER when using formula:


    - Column A has dates
    - Weekday formula turn dates into day number. 2 is Monday

  75. Danny Baetens says:


  76. Chandra Mohan says:

    =ED-SD-NETWORKDAYS.INTL(SD,ED,12)+1, try this formula

  77. MichaelCH says:


  78. Philip Stevenson says:

    Function CountADayBetwen(StartDAte As Date, EndDate As Date, TheDay As String)

    Select Case TheDay

    Case "Sunday"
    Nday = 1

    Case "Monday"
    Nday = 2

    Case "Tuesday"
    Nday = 3

    Case "Wednesday"
    Nday = 4

    Case "Thursday"
    Nday = 5

    Case "Friday"
    Nday = 6

    Case "Saturday"
    Nday = 7

    End Select

    For i = StartDAte To EndDate

    If Weekday(i) = Nday Then
    x = x + 1
    End If


    CountADayBetwen = x

    End Function

  79. mukesh says:

    A2 = Start date; A3 = End date


  80. Jogo do Texto says:

    Man, this article it's what I was looking for in the last couple weeks! Congratulations for this great text

Leave a Reply