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.
Share this tip with your colleagues
Simple, fun and useful emails, once per week.
Learn & be awesome.
Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME. Read my story • FREE Excel tips book
From simple to complex, there is a formula for every occasion. Check out the list now.
Calendars, invoices, trackers and much more. All free, fun and fantastic.
Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.
Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.
This week, let’s try something new. I have a spreadsheet challenge for you. Solve it, post your answers by leaving a comment. No cheating or
138 Responses
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!
for Excel 2010 & above
=WORKDAY.INTL(SD,ED-SD,12)-ED
SD ED are start date & end date resp.
If I’m not mistake there are 8 Mondays between 01/12/2015 and
31/01/2016.
Your formula retures: 10
=ED-SD-NETWORKDAYS.INTL(SD,ED,12)+1, try this formula
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.
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
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.
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
http://www.emailoffice.com/excel/arrays-bobumlas.html
Here you can learn a lot about array formulas.
Thanks Jan, there are some pretty cool array examples there!
Hé Bob,
It may be a few years ago but this is the most beautiful solution I came accros till now.
Cheers,
Jan
=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(SD&”:”&ED)))=2))
I like that one, Micky. I never saw the “N” Formula, it’s a very useful one.
many thanks!
Hé Mike,
I like your formula too and like the formula of Bob, this is also one of the most beautiful solutions.
Cheers Jan
hi prashant
sd:11/2/2015
ed:11/18/2015
result :2 this is wrong
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
)
)
)
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.
never mind – that doesn’t work for periods less than 7 days
=INT((ED-SD-WEEKDAY(SD,3)-WEEKDAY(ED,3))/7)+1
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)
@Gianluca,
January 2016 has 3 Mondays (1/1/2016-31/1/2016)
Your suggested formula returns: 3
Sorry for the TIPO:
January 2016 has 4 Mondays (1/1/2016-31/1/2016)
@Michael
the formula returns 4 for DS=1/1/2016 and SD=31/1/2016.
The result is correct.
@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)}
How about this one:
=(WEEKDAY(SD,2)=1)+QUOTIENT(ED-SD,7)+(WEEKDAY(ED,2)<WEEKDAY(SD,2))
Btw. the picture above has Sundays marked.
@Harold… thanks mate. Fixed the error now.
=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.
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.
Worked!!
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
=SUMPRODUCT(WEEKDAY(ROW(INDIRECT(VALUE(A1)&”:”&VALUE(A2))))=2)*1)
=IF(MOD(Start,INT(Start/7))=2,1)+INT(End/7)-INT(Start/7)-1
typo, should be
=IF(MOD(Start,INT(Start/7))=2,1)+INT(End/7)-INT(Start/7)-1
=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
=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.
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”))
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
=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
Google gives easy answer thru this website
http://www.easysurf.cc/wdate2.htm
L-O-L !!!
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.
@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
As a better praxis the UDF’s name should be changed to: HowMany_D_Days
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
=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.
=SUMPRODUCT(–(MOD(ROW(INDIRECT(start&”:”&end)),7)=2))
#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)
correction
=SUMPRODUCT(–(TEXT(ROW(INDIRECT(SD&”:”&ED)),”DDDD”)=”Monday”))
=SUMPRODUCT(N(WEEKDAY(ROW(INDEX(A:A,StartDate,):INDEX(A:A,Enddate,)),2)=1))
@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)
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?
=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
FREQUENCY(N(WEEKDAY(ROW(INDIRECT(A1&”:”&B1)),2)),1)
if only for Mondays
SUM(–(TEXT(ROW(INDIRECT(A1&”:”&B1)),”DDD”)=”Mon”))
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.
This appears to work
SUM(N(MOD(ROW(OFFSET(A1,SD-1,,ED-SD+1)),7)=2))
=TRUNC((A2-A1+1)/7;0)+IF((WEEKDAY(A2;2)-MOD(A2-A1+1;7))>0;0;1)
START DATE – A1
END DATE – A2
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.
@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.
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’
mma173,
Sorry, but if you find it difficult to present a FULL FORMULA (without “look something like”) – I have nothing more to say.
Thanks.
@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).
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.
=IF(WEEKDAY(enddate;2)=1; 1+ INTEGER(DAYS(enddate;startdate)/7); INTEGER(DAYS(enddate;startdate)/7))
@mma173,
Please examine the linked picture:
http://screenpresso.com/=EAOEg
Thanks.
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
Sorry, but I gave up.
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
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)
QUOTIENT(End-Start+WEEKDAY(Start,3),7)
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)
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
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.
=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
this doesn’t work so nicely if end and start day is Monday…
((ED+(7-WEEKDAY(ED,12))-(SD+(7-WEEKDAY(SD,12))))/7)+1
=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
= 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.
Best formula to me.
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?
=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
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))
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…
SD = Start Date
ED = End Date
=SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(SD&”:”&ED)))=1))
=Weeknumber (XX,2)-Weeknumbr (YY,2)
Were xx = cell which has end date and yy = cell which has begiate
=IF(WEEKDAY(StartDate,2)=1,1,0)+((EndDate-StartDate-WEEKDAY(EndDate,2)-(7-WEEKDAY(StartDate,2)))/7)+1
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
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
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))
=INT((WEEKDAY($A$1-2)-$A$1+$A$2)/7)
A1 is Start Date
A2 is End Date
not giving correct answer
sorry…. my mistake
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”))
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
How about:
=A2-A1+1-NETWORKDAYS.INTL(A1,A2,12)
Regards,
Alex
=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
=ROUNDUP((B1-A1)/7,0)+IF(AND(WEEKDAY(A1)=2,WEEKDAY(B1)=2),1,0)
=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.
Forgot to mention, enter as array formula (Ctrl+Shift+Enter)
Hi!
What about this:
A1: Start Date, B1: End Date
=NETWORKDAYS.INTL(A1,B1,”0111111″)
Blessings!
Wow… didn’t know you could use binary patterns in NETWORKDAYS.INTL… very cool.. 😎
Check Debra’s post
http://blog.contextures.com/archives/2015/12/10/customize-weekends-with-excel-workday-function/
Regards
Good work needs appreciation.
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.
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)
my solution (limited to dates within the same year)
https://www.dropbox.com/s/8vja3qn5yca7cs2/Between_two_dates.xlsx?dl=0
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!!!!
=SUMPRODUCT(IF(WEEKDAY(ROW(INDIRECT(E7&”:”&F7)),1)=2,1,0))
CTRL+SHIFT+ENTER in the above formula
=SUM((WEEKDAY(ROW(INDIRECT(M5&”:”&M6)))=2)*1)
Press Ctrl + Shift + Enter
Where M5 anda M6 are the dates
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
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)
=SUMPRODUCT(0+(TEXT(ROW(INDIRECT(A1&”:”&A2)),”DDD”)=”MON”))
=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
=IF(WEEKDAY(B9,2)>1,ROUNDDOWN(DAYS(B10,B9)/7,0),
ROUNDDOWN(DAYS(B10,B9)/7,0)+1)
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)
=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(A1&”:”&A2)))=2))
Where Celll A1contains the start date and A2 contains the end date
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.
Hi, found this on the Web. The formula was written by Laurent .
=INT((Ed-MOD(Ed- daynumber,7)-SD+7)/7)
Written by Laurent Longre.
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
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
INT((end-start+WEEKDAY(start;12))/7)
=ED-SD-NETWORKDAYS.INTL(SD,ED,12)+1, try this formula
=INT((ED-SD+MOD(SD-3,7)+1)/7)
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
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))
Man, this article it’s what I was looking for in the last couple weeks! Congratulations for this great text
=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}
@To whom it may concern,
Just a short update shown by the linked picture:
https://s29.postimg.org/hfxvuuz13/NONAME.png
H8 = SUMPRODUCT(IF(WEEKDAY(ROW(INDIRECT(E8&”:”&F8)))=2,1,0))
by putting starting date in E8 & ending date in F8
CTRL+SHiFT+ENTER
@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))
=INT((A2-A1+WEEKDAY(A1,16))/7)