# Number of days of week between two date range by month

#### guilherme88

##### New Member
Hello,

I am looking to get the following result:
So basically I need to figure out how many turnaround( airplane comes in gets services and departs with another flight number to different destination) I have between two dates and week days.
For example:
Start End
20MAR20-28MAR20
Days of week
1___4_6_ (Monday, Thursday and Saturday) = 4 days (1monday,1Thursday and 2 Saturdays)

That means, between this period I will have 4 rotations
If my period is 27MAR20-20JUL20 for day _2_____(Tuesday) i know there are 16Tuesdays between these two dates, therefore for that given flight number I will have 16 rotations on Tuesdays for the given period. Now I am trying to figure out by month. Looking at the calendar I know March2020 has 1 Tuesday, April has 4, may has 4, june has 5, and July has 2=TTL16.

That is basically where I am trying to get I have created formulas to break down the days of week and the months in the period, now I need to figure out a way to know how many by month between the periods taking into account the days of the week in that period.

Also if my start date is 11feb20 and my end date is 11feb20 my day of week is D2(tuesday) this should return number 1 as there is only 1 tuesday between these two dates.

Really would appreciate some feeback.

Thank you very much

Guilherme

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Dave Patton

##### Well-known Member
Try the following

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">20-Mar-20</td><td style="text-align: right;;">28-Mar-20</td><td style="text-align: right;;">4</td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">1c</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">WEEKDAY(<font color="Green">ROW(<font color="Purple">INDIRECT(<font color="Teal">A2&":"&B2</font>)</font>)</font>)={2,4,6}</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

#### Tetra201

##### MrExcel MVP
Another approach:

=NETWORKDAYS.INTL(A2,B2,"0110101")

The "0110101" string codifies Mon, Thu, Sat; for Tue only, the string would be "1011111"

To get breakdown by month, put the month's start date in cell A2 and/or month's end date in cell B2.

#### guilherme88

##### New Member
I have tried both formulas but they wont work to retrieve the number of turns by month when the start and end date are the same the formula accounts for the entire month thus giving me the wrong number of turns

#### guilherme88

##### New Member
is there a way to adjust the "0110101" automatically by reading the day of week string, ie if the next string s only day 3 and 4 weds and thurs "1100111"

#### Nishant94

##### Well-known Member
You can use this with the help of UDF:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Start Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">End Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Day</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">27-03-2020</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20-07-2020</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">_2_____</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #00B050;;">16</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">27-03-2020</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20-07-2020</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">_2__5__</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #00B050;;">33</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=Datebet(<font color="Blue">A3,B3,C3</font>)</td></tr></tbody></table></td></tr></table><br />

Code for UDF:

Code:
``````Function Datebet(Strtdate As Date, Enddate As Date, tday As String)
Dim testch As String, i As Integer
For i = 1 To Len(tday)
testch = Mid(tday, i, 1)
If testch = "_" Then
Mid(tday, i, 1) = "1"
ElseIf IsNumeric(testch) Then
Mid(tday, i, 1) = "0"
End If
Next i
Datebet = Application.WorksheetFunction.NetworkDays_Intl(Strtdate, Enddate, tday)
End Function``````

#### Nishant94

##### Well-known Member
Or you can use this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Start Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">End Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Day</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">27-03-2020</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20-07-2020</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">_2_____</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #00B050;;">16</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">27-03-2020</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20-07-2020</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">_2__5__</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #00B050;;">33</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">{=SUM(<font color="Blue">--ISNUMBER(<font color="Red">MATCH(<font color="Green">WEEKDAY(<font color="Purple">ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">A3&":"&B3</font>)</font>),11</font>),(<font color="Purple">MID(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">C3,"_",""</font>),ROW(<font color="#FF00FF">INDIRECT(<font color="Navy">"1:"&LEN(<font color="Blue">SUBSTITUTE(<font color="Red">C3,"_",""</font>)</font>)</font>)</font>),1</font>)+0</font>),0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### guilherme88

##### New Member
You can use this with the help of UDF:

ABCD
2Start DateEnd DateDay
327-03-202020-07-2020_2_____16
427-03-202020-07-2020_2__5__33

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
D3=Datebet(A3,B3,C3)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Code for UDF:

Code:
``````Function Datebet(Strtdate As Date, Enddate As Date, tday As String)
Dim testch As String, i As Integer
For i = 1 To Len(tday)
testch = Mid(tday, i, 1)
If testch = "_" Then
Mid(tday, i, 1) = "1"
ElseIf IsNumeric(testch) Then
Mid(tday, i, 1) = "0"
End If
Next i
Datebet = Application.WorksheetFunction.NetworkDays_Intl(Strtdate, Enddate, tday)
End Function``````
That brilliant Nishant it worked! i am still trying to get it by month ultimately i need to get the number of turns by month with the period.
Cheers
Guilherme

#### guilherme88

##### New Member
Thanks you so much

#### guilherme88

##### New Member
Nishant thanks again

How can i sum it by month as it now returns the total. great solutions by the way. instead of showing me 16, i need it to show me how many in a given month within the range, in march i have 1, so for April2020 i have 4 and may also 4.
ABCD
2Start DateEnd DateDay
327-03-202020-07-2020_2_____16

<tbody>
</tbody>
MAR20 APR20 MAY20
1 4 4

Thanks a lot

Threads
1,090,119
Messages
5,412,554
Members
403,432
Latest member
cr2141