Number of days of week between two date range by month

guilherme88

New Member
Joined
Aug 1, 2018
Messages
16
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
 

Some videos you may like

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
Joined
Feb 15, 2002
Messages
3,859
Office Version
2010
Platform
Windows
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
Joined
Oct 14, 2016
Messages
3,502
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
Joined
Aug 1, 2018
Messages
16
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
Joined
Aug 1, 2018
Messages
16
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
Joined
May 8, 2015
Messages
507
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
Joined
May 8, 2015
Messages
507
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
Joined
Aug 1, 2018
Messages
16
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
Joined
Aug 1, 2018
Messages
16
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 :)
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top