How many Mondays between two dates? [homework]

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

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

 Color changing line chart [tutorial] Generate a snow flake pattern Excel [holiday fun]
 Written by Chandoo Tags: date and time, homework, Learn Excel, Microsoft Excel Formulas, no-nl, weekday Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

134 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
http://wmfexcel.com/2014/04/12/calculate-number-of-a-specific-day-between-two-dates/
Hope you like it!

2. prashant says:

for Excel 2010 & above

=WORKDAY.INTL(SD,ED-SD,12)-ED

SD ED are start date & end date resp.

• Michael (Micky) Avidan says:

If I'm not mistake there are 8 Mondays between 01/12/2015 and
31/01/2016.

• Chandra Mohan says:

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

3. Bob Phillips says:

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

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B1&":"&B2)))=2))

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?

Thanks

Mark

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

=ROW(7:17)

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 :

=ROW(A7:A17)

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

=ROW(IX7:IX17)

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 :

=ROW(B1:B2)

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 :

=ROW(INDIRECT(B1:B2))

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,

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

I'm much clearer on how it works now.

tnx

Mark

• Jan Martens says:

http://www.emailoffice.com/excel/arrays-bobumlas.html
Here you can learn a lot about array formulas.

• Mark says:

Thanks Jan, there are some pretty cool array examples there!

4. Michael (Micky) Avidan says:

=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(SD&":"&ED)))=2))

• TheQ47 says:

I like that one, Micky. I never saw the "N" Formula, it's a very useful one.

5. mahesh says:

hi prashant
sd:11/2/2015
ed:11/18/2015
result :2 this is wrong

6. Bertrand says:

In DAX, assuming you have a Date table

EVALUATE
ROW (
"Count of Mondays", COUNTROWS (
FILTER (
DateTable,
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.

• Bill says:

never mind - that doesn't work for periods less than 7 days

8. Gianluca says:

=INT((ED-SD-WEEKDAY(SD,3)-WEEKDAY(ED,3))/7)+1

• Gianluca says:

Not working for periods less than 7 days...
This should fix the problem:
=INT((ED-SD+7)/7)-AND(WEEKDAY(SD,3)>0;WEEKDAY(ED,3)>0)

• Michael (Micky) Avidan says:

@Gianluca,
January 2016 has 3 Mondays (1/1/2016-31/1/2016)

• Michael (Micky) Avidan says:

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

• Gianluca says:

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

• Michael (Micky) Avidan says:

@Gianluca,
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:
=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(B1&":"&B2)))=2))
is the shortest that also works as expected.

• Gianluca says:

Shorter then your shortest... (array formula)

={INT((ED-SD+7)/7)-AND(WEEKDAY(SD:ED,3)>0)}

9. Harold Mude says:

=(WEEKDAY(SD,2)=1)+QUOTIENT(ED-SD,7)+(WEEKDAY(ED,2)<WEEKDAY(SD,2))

10. Harold Mude says:

Btw. the picture above has Sundays marked.

• Chandoo says:

@Harold... thanks mate. Fixed the error now.

11. Mehmet Gunal OLCER says:

=IF(WEEKDAY(FIRSTDAY,11)=1,1,0)+INT(DAYS(LASTDAY,FIRSTDAY)/7)+IF(MOD(DAYS(LASTDAY,FIRSTDAY),7)+WEEKDAY(FIRSTDAY,11)>7,1,0)

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

• Mehmet Gunal OLCER says:

Let me give another solution.

=ABS(INT((N(DAY_2)-2)/7)-INT((N(DAY_1)-2)/7))

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:

=SUMPRODUCT(WEEKDAY(ROW(INDIRECT(VALUE(A1)&":"&VALUE(A2))))=2)*1)

14. Jeff Fordon says:

=IF(MOD(Start,INT(Start/7))=2,1)+INT(End/7)-INT(Start/7)-1

15. Jeff 1 says:

typo, should be

=IF(MOD(Start,INT(Start/7))=2,1)+INT(End/7)-INT(Start/7)-1

16. Jon says:

=MAX(ROUNDUP(((A2-A1+1)+(IF(WEEKDAY(A1)>2,WEEKDAY(A1)-9,WEEKDAY(A1)-2)))/7,0),0)

Where A1 = Start Date
A2 = End date

17. Gopi Krishna says:

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

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:
={SUM((E:E>=StartDate)*(E:E<=StopDate)*(WEEKDAY(E:E)=DayChosen)/DayChosen*(WEEKDAY(E:E)))}

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:

=MAX(ROUNDUP(((A2-A1+1)+(IF(WEEKDAY(A1)>2,WEEKDAY(A1)-9,WEEKDAY(A1)-2)))/7,0),0)

Where:
A1 = Start date
A2 = End Date

20. ramjiyahoo says:

http://www.easysurf.cc/wdate2.htm

• Michael (Micky) Avidan says:

L-O-L !!!

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:

@Daniel,
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:

=SUMPRODUCT(--(MOD(ROW(INDIRECT(start&":"&end)),7)=2))

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"
=TOPLA.ÇARPIM(--(METNEÇEV?R(SATIR(DOLAYLI(SD&":"&ED));"GGGG")="Pazartesi"))

#2 =SD-ED+1-NETWORKDAYS.INTL(SD;ED;12)

• Kivanç Yildiz says:

correction

=SUMPRODUCT(–(TEXT(ROW(INDIRECT(SD&”:”&ED)),”DDDD”)=”Monday”))

25. Leonid says:

=SUMPRODUCT(N(WEEKDAY(ROW(INDEX(A:A,StartDate,):INDEX(A:A,Enddate,)),2)=1))

26. Michael (Micky) Avidan says:

@Gianluca:
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:

=ROUNDDOWN((((A2-A1+1)-CHOOSE(WEEKDAY(A1),1,0,6,5,4,3,2))/7)+1,0)

START DATE - A1
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:

FREQUENCY(N(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)),1)
if only for Mondays

SUM(--(TEXT(ROW(INDIRECT(A1&":"&B1)),"DDD")="Mon"))

30. Jan Martens says:

Hi
INT(DATEDIF(SD+7-WEEKDAY(SD,3),ED,"d"))/7)+1

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
SUM(N(MOD(ROW(OFFSET(A1,SD-1,,ED-SD+1)),7)=2))

32. Mindaugas says:

=TRUNC((A2-A1+1)/7;0)+IF((WEEKDAY(A2;2)-MOD(A2-A1+1;7))>0;0;1)

START DATE – A1
END DATE – A2

33. mma173 says:

Guys,

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:

@mma173,
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
Thanks.

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

Thanks'

• Michael (Micky) Avidan says:

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

• Michael (Micky) Avidan says:

@mma173,
...by 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:

@mma173,
http://screenpresso.com/=EAOEg
Thanks.

• mma173 says:

The adjustment part was incorrect. Check this now:

=(End Date - (Start Date + MOD((7-(WEEKDAY(Start Date)-Weekday(Monday),7)))/7+1

• Michael (Micky) Avidan says:

Sorry, but I gave up.

36. Abhijeet says:

Hi

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
Wend

37. param nayak says:

a mathematicl approach

=INT((A2-A1+1)/7)+IF(AND(MOD((A2-A1+1),7)+WEEKDAY(A1,2)-1<=7,WEEKDAY(A1,2)1),0,1)

38. Denys Calvin says:

QUOTIENT(End-Start+WEEKDAY(Start,3),7)

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

QUOTIENT(End-Start+WEEKDAY(Start,3),7)+IF(WEEKDAY(Start,3)=0,1,0)

39. param nayak says:

a mathematical approach

=INT((A2-A1+1)/7)+IF(AND(MOD((A2-A1+1),7)+WEEKDAY(A1,2)-1<=7,WEEKDAY(A1,2)1),0,1)

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:

=IF(OR(MOD(SD,7)=2,MOD(ED,7)=2),ROUNDDOWN((ED-SD)/7,0)+1,ROUNDDOWN((ED-SD)/7,0))

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

=IF(TEXT(A1,"DDD")="Mon",ROUND((A2-A1)/7,0)+1,ROUND((A2-A1)/7,0))

A1 is Start Date
A2 is End Date

• Dheeran says:

this doesn't work so nicely if end and start day is Monday...

43. Seema says:

((ED+(7-WEEKDAY(ED,12))-(SD+(7-WEEKDAY(SD,12))))/7)+1

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

• Jan Martens says:

Best formula to me.

45. Robert says:

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

=IF(D5E5,INT(IF(WEEKDAY(start)-_
WEEKDAY(end)=0,wkdsum+1,wkdsum)),_
INT(IF(WEEKDAY(start)-WEEKDAY(end)=0,wkdsum+1,wkdsum)-1))

were wkdsum=IF(wkd>7,dsum/7-1,dsum/7)...
dsum=(DAYS(end,start)+WEEKDAY(end))...
wkd=E5+D5

47. Dheeran says:

WEEKDAY(end)=0,wkdsum+1,wkdsum)),_
INT(IF(WEEKDAY(start)-WEEKDAY(end)=0,wkdsum+1,wkdsum)-1))

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

49. Manuel Vasquez says:

SD = Start Date
ED = End Date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(SD&":"&ED)))=1))

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:

=IF(WEEKDAY(StartDate,2)=1,1,0)+((EndDate-StartDate-WEEKDAY(EndDate,2)-(7-WEEKDAY(StartDate,2)))/7)+1

52. Pablo says:

It seems that many answers include a variation of this:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=2)*1)

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.

{=SUM(IF(WEEKDAY(B1+ROW(INDIRECT("1:"&DAYS(A2;B1))))=2;1;0))}

where b1 - start date, a2 - end date

54. Paul S. says:

Starting date in A1 and Ending date in A2

=IF(WEEKDAY(A1,1)=2,ROUNDUP((A2-A1)/7,0)+1,ROUNDUP((A2-A1)/7,0))

55. Kcdog says:

=INT((WEEKDAY(\$A\$1-2)-\$A\$1+\$A\$2)/7)

A1 is Start Date
A2 is End Date

• SAURABH SHUKLA says:

• SAURABH SHUKLA says:

sorry.... my mistake

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.

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(SD&":"&ED)),"ddd")="Mon"))

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:

=A2-A1+1-NETWORKDAYS.INTL(A1,A2,12)

Regards,

Alex

59. SAURABH SHUKLA says:

=ROUNDUP(DATEDIF(A1,B1,"D")/7,0)+IF(AND(WEEKDAY(A1)=2,WEEKDAY(B1)=2),1,0)

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

• SAURABH SHUKLA says:

=ROUNDUP((B1-A1)/7,0)+IF(AND(WEEKDAY(A1)=2,WEEKDAY(B1)=2),1,0)

60. Artem says:

=SUM((WEEKDAY((Start_Date+ROW(INDIRECT("1:"&End_Date-Start_Date))-1),2)=1)*1)

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

• Artem says:

Forgot to mention, enter as array formula (Ctrl+Shift+Enter)

61. John Jairo V says:

Hi!

A1: Start Date, B1: End Date
=NETWORKDAYS.INTL(A1,B1,"0111111")

Blessings!

62. Peter says:

This should work:

=ROUNDUP((D8-IF(E7=5,D7+3,IF(E7=6,D7+2,IF(E7=7,E7+1,IF(E7=4,E7+4,IF(E7=3,D7+5,IF(D7=2,D7+6,D7)))))))/7,0)

63. AK says:

my solution (limited to dates within the same year)

https://www.dropbox.com/s/8vja3qn5yca7cs2/Between_two_dates.xlsx?dl=0

64. Vangelis M says:

One more

=INT((end-start+8-WEEKDAY(end,how_many))/7)

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

65. Gopi Krishna says:

=SUMPRODUCT(IF(WEEKDAY(ROW(INDIRECT(E7&":"&F7)),1)=2,1,0))

• Gopi Krishna says:

CTRL+SHIFT+ENTER in the above formula

66. Pedro says:

=SUM((WEEKDAY(ROW(INDIRECT(M5&":"&M6)))=2)*1)

Press Ctrl + Shift + Enter

Where M5 anda M6 are the dates

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

68. Mohd Mukeet says:

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

{=SUM(IF(TEXT(A1+ROW(INDIRECT("1:"&DAY(B1))),"DDD")="Mon",1,0))}
Press CTRL+SHIFT+ENTER in the above formula
= 3 (Monday)

69. Haz says:

=SUMPRODUCT(0+(TEXT(ROW(INDIRECT(A1&":"&A2)),"DDD")="MON"))

70. Eric L. says:

=IF(WEEKDAY(Start_Date,2)>1,ROUNDDOWN(DAYS(End_Date,Start_Date)/7,0),ROUNDDOWN(DAYS(End_Date,Start_Date)/7,0)+1)

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

(Number of Choices-x)+1

• Eric L. says:

=IF(WEEKDAY(B9,2)>1,ROUNDDOWN(DAYS(B10,B9)/7,0),
ROUNDDOWN(DAYS(B10,B9)/7,0)+1)

• Eric L. says:

One more try...

=IF(WEEKDAY(Start_Date,2)>1,
ROUNDDOWN(DAYS(End_Date,Start_Date)/7,0),
ROUNDDOWN(DAYS(End_Date,Start_Date)/7,0)+1)

71. Sabeesh says:

=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=2))

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

72. Jon Valz says:

I think I've got q couple solns:

=round(networksdays(start-weekday(start,3),end-weekday(end,3))/5,0)

Or

=round((end-weekday(end,3)-start-weekday(start,3)))/7,0)

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

73. Jan Martens says:

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

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

• Jan Martens says:

Written by Laurent Longre.

74. Anant Jain says:

Answer is to calculate monday between 2 dates is:

=IF(WEEKDAY(SD,1)=2,INT((ED-SD)/7)+1,INT((ED-SD)/7))

SD = Start Date
ED = End Date

75. Chirayu says:

Use CTRL + SHIFT + ENTER when using formula:

=COUNT(IF(WEEKDAY(A:A)=2,A:A))

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

76. Danny Baetens says:

INT((end-start+WEEKDAY(start;12))/7)

77. Chandra Mohan says:

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

78. MichaelCH says:

=INT((ED-SD+MOD(SD-3,7)+1)/7)

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

Next

End Function

80. mukesh says:

A2 = Start date; A3 = End date

=IF(WEEKDAY(A2)=WEEKDAY(A3),ROUND(((A3-A2)/7)-0.14286,0)+1,ROUND(((A3-A2)/7)-0.14286,0))

81. Jogo do Texto says:

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

82. =INT((WEEKDAY(A1-2)-A1+A2)/7)
just put starting date in cell A1 and end date in Cell B2.
and change the day no in formula where -2 is a day no..
Day no like {sun, mon, tue, wed, thu, fri, sun} {1,2,3,4,5,67}

83. Michael (Micky) Avidan says:

@To whom it may concern,
Just a short update shown by the linked picture:
https://s29.postimg.org/hfxvuuz13/NONAME.png

84. GOPI kRISHNA says:

H8 = SUMPRODUCT(IF(WEEKDAY(ROW(INDIRECT(E8&":"&F8)))=2,1,0))

by putting starting date in E8 & ending date in F8

CTRL+SHiFT+ENTER

• Michael (Micky) Avidan says:

@GOPI?
It should not, necessarily, be an "Array formula and the use of IF is also unnecessary.

Try: =SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(E8&":"&F8)))=2))

85. =INT((A2-A1+WEEKDAY(A1,16))/7)

 Color changing line chart [tutorial] Generate a snow flake pattern Excel [holiday fun]