How Many Mondays Between Two Dates? [homework]
Maybe your like
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?
Please post your formulas / VBA functions / DAX measures in the comments section.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
138 Responses to “How many Mondays between two dates? [homework]”
-
MF says: December 18, 2015 at 12:16 pm
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! Merry Christmas in advance!
Reply -
prashant says: December 18, 2015 at 12:24 pm
for Excel 2010 & above
=WORKDAY.INTL(SD,ED-SD,12)-ED
SD ED are start date & end date resp.
Reply-
Michael (Micky) Avidan says: December 18, 2015 at 1:09 pm
If I'm not mistake there are 8 Mondays between 01/12/2015 and 31/01/2016. Your formula retures: 10
Reply-
Chandra Mohan says: January 6, 2016 at 6:16 am
=ED-SD-NETWORKDAYS.INTL(SD,ED,12)+1, try this formula
Reply
-
-
-
Bob Phillips says: December 18, 2015 at 12:51 pm
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.
Reply-
Mark says: December 22, 2015 at 7:45 am
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
Reply-
NARAYAN says: December 27, 2015 at 1:43 pm
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.
Reply-
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.
tnx
Mark
Reply
-
-
Jan Martens says: December 29, 2015 at 10:33 pm
http://www.emailoffice.com/excel/arrays-bobumlas.html Here you can learn a lot about array formulas.
Reply-
Thanks Jan, there are some pretty cool array examples there!
Reply
-
-
Jan says: August 16, 2022 at 6:01 pm
Hé Bob,
It may be a few years ago but this is the most beautiful solution I came accros till now.
Cheers, Jan
Reply
-
-
-
=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(SD&":"&ED)))=2))
Reply-
TheQ47 says: December 18, 2015 at 2:28 pm
I like that one, Micky. I never saw the "N" Formula, it's a very useful one.
Reply -
Steve says: October 6, 2020 at 1:08 pm
many thanks!
Reply -
Hé Mike,
I like your formula too and like the formula of Bob, this is also one of the most beautiful solutions.
Cheers Jan
Reply
-
-
mahesh says: December 18, 2015 at 3:12 pm
hi prashant sd:11/2/2015 ed:11/18/2015 result :2 this is wrong
Reply -
Bertrand says: December 18, 2015 at 3:14 pm
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 ) ) )
Reply -
Bill says: December 18, 2015 at 3:19 pm
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.
Reply-
never mind - that doesn't work for periods less than 7 days
Reply
-
-
Gianluca says: December 18, 2015 at 3:31 pm
=INT((ED-SD-WEEKDAY(SD,3)-WEEKDAY(ED,3))/7)+1
Reply-
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)
Reply -
@Gianluca, January 2016 has 3 Mondays (1/1/2016-31/1/2016) Your suggested formula returns: 3
Reply-
Sorry for the TIPO: January 2016 has 4 Mondays (1/1/2016-31/1/2016)
Reply-
@Michael the formula returns 4 for DS=1/1/2016 and SD=31/1/2016. The result is correct.
Reply-
@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.
-
@Michael (reply to post below) Shorter then your shortest... (array formula)
={INT((ED-SD+7)/7)-AND(WEEKDAY(SD:ED,3)>0)}
-
-
-
-
-
Harold Mude says: December 18, 2015 at 3:53 pm
How about this one:
=(WEEKDAY(SD,2)=1)+QUOTIENT(ED-SD,7)+(WEEKDAY(ED,2)<WEEKDAY(SD,2))
Reply -
Btw. the picture above has Sundays marked.
Reply-
Chandoo says: December 18, 2015 at 4:02 pm
@Harold... thanks mate. Fixed the error now.
Reply
-
-
Mehmet Gunal OLCER says: December 18, 2015 at 4:03 pm
=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.
Reply-
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.
Reply-
Karthik says: June 28, 2018 at 1:08 pm
Worked!!
Reply
-
-
-
Josh Saavoss says: December 18, 2015 at 4:19 pm
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
Reply -
Ashish says: December 18, 2015 at 5:56 pm
=SUMPRODUCT(WEEKDAY(ROW(INDIRECT(VALUE(A1)&":"&VALUE(A2))))=2)*1)
Reply -
Jeff Fordon says: December 18, 2015 at 7:03 pm
=IF(MOD(Start,INT(Start/7))=2,1)+INT(End/7)-INT(Start/7)-1
Reply -
typo, should be
=IF(MOD(Start,INT(Start/7))=2,1)+INT(End/7)-INT(Start/7)-1
Reply -
Jon says: December 18, 2015 at 7:28 pm
=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
Reply -
Gopi Krishna says: December 18, 2015 at 7:37 pm
=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.
Reply-
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"))
Reply
-
-
Darin Scott says: December 18, 2015 at 8:07 pm
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
Reply -
=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
Reply -
ramjiyahoo says: December 18, 2015 at 8:14 pm
Google gives easy answer thru this website
http://www.easysurf.cc/wdate2.htm
Reply-
L-O-L !!!
Reply
-
-
Daniel Lamarche says: December 18, 2015 at 8:28 pm
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.
Reply-
@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
Reply-
As a better praxis the UDF's name should be changed to: HowMany_D_Days
Reply-
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
Reply
-
-
-
-
Gordon says: December 18, 2015 at 8:32 pm
=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.
Reply -
Jason Morin says: December 18, 2015 at 10:14 pm
=SUMPRODUCT(--(MOD(ROW(INDIRECT(start&":"&end)),7)=2))
Reply -
K?vanç Y?ld?z says: December 18, 2015 at 10:32 pm
#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)
Reply-
correction
=SUMPRODUCT(–(TEXT(ROW(INDIRECT(SD&”:”&ED)),”DDDD”)=”Monday”))
Reply
-
-
Leonid says: December 19, 2015 at 1:20 am
=SUMPRODUCT(N(WEEKDAY(ROW(INDEX(A:A,StartDate,):INDEX(A:A,Enddate,)),2)=1))
Reply -
@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)
Reply -
Lewis Kirby says: December 19, 2015 at 9:02 am
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?
Reply -
SAGAR MOHITE says: December 19, 2015 at 9:21 am
=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
Reply -
Alan says: December 19, 2015 at 1:05 pm
FREQUENCY(N(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)),1) if only for Mondays
SUM(--(TEXT(ROW(INDIRECT(A1&":"&B1)),"DDD")="Mon"))
Reply -
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.
Reply -
This appears to work SUM(N(MOD(ROW(OFFSET(A1,SD-1,,ED-SD+1)),7)=2))
Reply -
Mindaugas says: December 19, 2015 at 8:08 pm
=TRUNC((A2-A1+1)/7;0)+IF((WEEKDAY(A2;2)-MOD(A2-A1+1;7))>0;0;1)
START DATE – A1 END DATE – A2
Reply -
mma173 says: December 19, 2015 at 8:43 pm
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.
Reply-
@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.
Reply-
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'
Reply-
mma173, Sorry, but if you find it difficult to present a FULL FORMULA (without "look something like") - I have nothing more to say. Thanks.
Reply -
@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).
Reply-
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.
-
-
-
-
-
Danny Baetens says: December 20, 2015 at 7:45 am
=IF(WEEKDAY(enddate;2)=1; 1+ INTEGER(DAYS(enddate;startdate)/7); INTEGER(DAYS(enddate;startdate)/7))
Reply -
@mma173, Please examine the linked picture: http://screenpresso.com/=EAOEg Thanks.
Reply-
Thanks' for your interest. The adjustment part was incorrect. Check this now:
=(End Date - (Start Date + MOD((7-(WEEKDAY(Start Date)-Weekday(Monday),7)))/7+1
Reply-
Sorry, but I gave up.
Reply
-
-
-
Abhijeet says: December 20, 2015 at 11:07 am
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
Reply -
param nayak says: December 20, 2015 at 11:15 am
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)
Reply -
Denys Calvin says: December 20, 2015 at 11:23 am
QUOTIENT(End-Start+WEEKDAY(Start,3),7)
Reply-
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)
Reply
-
-
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
Reply -
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.
Reply -
Swapnil Shah says: December 21, 2015 at 3:29 am
=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
Reply -
Farzad says: December 21, 2015 at 6:54 am
=IF(TEXT(A1,"DDD")="Mon",ROUND((A2-A1)/7,0)+1,ROUND((A2-A1)/7,0))
A1 is Start Date A2 is End Date
Reply-
Dheeran says: December 21, 2015 at 2:59 pm
this doesn't work so nicely if end and start day is Monday...
Reply
-
-
Seema says: December 21, 2015 at 8:30 am
((ED+(7-WEEKDAY(ED,12))-(SD+(7-WEEKDAY(SD,12))))/7)+1
Reply-
=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
Reply
-
-
Satya Murthy says: December 21, 2015 at 12:25 pm
= 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.
Reply-
Best formula to me.
Reply
-
-
Robert says: December 21, 2015 at 12:35 pm
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?
Reply -
=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
Reply -
made a mistake...should read IF(WEEKDAY(start)WEEKDAY(end),INT(IF(WEEKDAY(start)-_ WEEKDAY(end)=0,wkdsum+1,wkdsum)),_ INT(IF(WEEKDAY(start)-WEEKDAY(end)=0,wkdsum+1,wkdsum)-1))
Reply -
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...
Reply -
Manuel Vasquez says: December 21, 2015 at 3:10 pm
SD = Start Date ED = End Date
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(SD&":"&ED)))=1))
Reply -
Ray Gaurav says: December 21, 2015 at 4:00 pm
=Weeknumber (XX,2)-Weeknumbr (YY,2) Were xx = cell which has end date and yy = cell which has begiate
Reply -
Brian says: December 21, 2015 at 5:40 pm
=IF(WEEKDAY(StartDate,2)=1,1,0)+((EndDate-StartDate-WEEKDAY(EndDate,2)-(7-WEEKDAY(StartDate,2)))/7)+1
Reply -
Pablo says: December 21, 2015 at 6:34 pm
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
Reply-
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.
Reply
-
-
Arkadiusz says: December 21, 2015 at 6:55 pm
{=SUM(IF(WEEKDAY(B1+ROW(INDIRECT("1:"&DAYS(A2;B1))))=2;1;0))}
Reply-
where b1 - start date, a2 - end date
Reply
-
-
Paul S. says: December 21, 2015 at 7:08 pm
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))
Reply -
Kcdog says: December 21, 2015 at 7:24 pm
=INT((WEEKDAY($A$1-2)-$A$1+$A$2)/7)
A1 is Start Date A2 is End Date
Reply-
SAURABH SHUKLA says: December 22, 2015 at 7:15 am
not giving correct answer
Reply-
sorry.... my mistake
Reply
-
-
-
GMF says: December 21, 2015 at 8:03 pm
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"))
Reply -
Vishwamitra says: December 21, 2015 at 8:28 pm
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
Reply -
Alex Groberman says: December 21, 2015 at 10:35 pm
How about:
=A2-A1+1-NETWORKDAYS.INTL(A1,A2,12)
Regards,
Alex
Reply -
=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
Reply-
=ROUNDUP((B1-A1)/7,0)+IF(AND(WEEKDAY(A1)=2,WEEKDAY(B1)=2),1,0)
Reply
-
-
Artem says: December 22, 2015 at 7:22 am
=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.
Reply-
Forgot to mention, enter as array formula (Ctrl+Shift+Enter)
Reply
-
-
John Jairo V says: December 22, 2015 at 1:55 pm
Hi!
What about this: A1: Start Date, B1: End Date =NETWORKDAYS.INTL(A1,B1,"0111111")
Blessings!
Reply-
Wow... didn't know you could use binary patterns in NETWORKDAYS.INTL... very cool.. 😎
Reply-
Elias says: December 23, 2015 at 3:41 pm
Check Debra's post
http://blog.contextures.com/archives/2015/12/10/customize-weekends-with-excel-workday-function/
Regards
Reply
-
-
Good work needs appreciation.
Reply -
Chihiro says: December 23, 2015 at 2:30 pm
Awesome! I was trying to manipulate NETWORKDAYS.INTL as well and came up with =(B1-A1)+1-NETWORKDAYS.INTL(A1,B1,12)
But had no idea it took binary.
Reply
-
-
Peter says: December 23, 2015 at 5:42 am
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)
Reply -
AK says: December 23, 2015 at 7:16 am
my solution (limited to dates within the same year)
https://www.dropbox.com/s/8vja3qn5yca7cs2/Between_two_dates.xlsx?dl=0
Reply -
Vangelis M says: December 23, 2015 at 1:54 pm
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!!!!
Reply -
=SUMPRODUCT(IF(WEEKDAY(ROW(INDIRECT(E7&":"&F7)),1)=2,1,0))
Reply-
CTRL+SHIFT+ENTER in the above formula
Reply
-
-
Pedro says: December 23, 2015 at 3:00 pm
=SUM((WEEKDAY(ROW(INDIRECT(M5&":"&M6)))=2)*1)
Press Ctrl + Shift + Enter
Where M5 anda M6 are the dates
Reply -
BL says: December 23, 2015 at 3:25 pm
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
Reply -
Mohd Mukeet says: December 23, 2015 at 3:41 pm
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)
Reply -
Haz says: December 23, 2015 at 6:12 pm
=SUMPRODUCT(0+(TEXT(ROW(INDIRECT(A1&":"&A2)),"DDD")="MON"))
Reply -
Eric L. says: December 23, 2015 at 9:53 pm
=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
Reply-
=IF(WEEKDAY(B9,2)>1,ROUNDDOWN(DAYS(B10,B9)/7,0), ROUNDDOWN(DAYS(B10,B9)/7,0)+1)
Reply-
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)
Reply
-
-
-
Sabeesh says: December 25, 2015 at 8:10 am
=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=2))
Where Celll A1contains the start date and A2 contains the end date
Reply -
Jon Valz says: December 27, 2015 at 8:52 am
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.
Reply -
Hi, found this on the Web. The formula was written by Laurent .
=INT((Ed-MOD(Ed- daynumber,7)-SD+7)/7)
Reply-
Written by Laurent Longre.
Reply
-
-
Anant Jain says: January 4, 2016 at 12:51 pm
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
Reply -
Chirayu says: January 4, 2016 at 5:02 pm
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
Reply -
INT((end-start+WEEKDAY(start;12))/7)
Reply -
=ED-SD-NETWORKDAYS.INTL(SD,ED,12)+1, try this formula
Reply -
MichaelCH says: January 11, 2016 at 3:56 pm
=INT((ED-SD+MOD(SD-3,7)+1)/7)
Reply -
Philip Stevenson says: January 22, 2016 at 3:51 pm
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
CountADayBetwen = x
End Function
Reply -
mukesh says: April 13, 2016 at 3:12 pm
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))
Reply -
Jogo do Texto says: June 10, 2016 at 12:15 pm
Man, this article it's what I was looking for in the last couple weeks! Congratulations for this great text
Reply -
Junaid Azhar doga says: December 28, 2016 at 10:01 am
=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}
Reply -
@To whom it may concern, Just a short update shown by the linked picture: https://s29.postimg.org/hfxvuuz13/NONAME.png
Reply -
H8 = SUMPRODUCT(IF(WEEKDAY(ROW(INDIRECT(E8&":"&F8)))=2,1,0))
by putting starting date in E8 & ending date in F8
CTRL+SHiFT+ENTER
Reply-
@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))
Reply
-
-
Duc Thanh Nguyen says: January 13, 2017 at 4:51 am
=INT((A2-A1+WEEKDAY(A1,16))/7)
Reply
Leave a Reply
Click here to cancel reply.Name (required)
Mail (will not be published) (required)
Website
Notify me of when new comments are posted via e-mail
Tag » How Many Mondays In A Year
-
How Many Mondays Are In 2021? - Information News - India Today
-
How Many Mondays In A Year? 2022 - Online Calculator
-
How Many Mondays Come In A Year? - Quora
-
How Many Mondays In 2021 - Convert Units
-
How Many Mondays In 2021? - Calendar Maniacs
-
How Many Mondays Are There In A Particular Month Of A ... - Toppr
-
How Many Mondays So Far In 2022? - Calcudater
-
How Many Mondays Until New Year?
-
[Solved] If 1st February Is A Monday, How Many Mondays Occur In That
-
How Many Mondays Left This Year - Weeks Until
-
Solved A Non-leap Year Has 365 Days. Assume That January 1 - Chegg
-
How Many Mondays Will Be There In A Leap Year, If The First Day Of The ...