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
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
3,745
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,448
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 :)
 

Forum statistics

Threads
1,078,521
Messages
5,340,942
Members
399,399
Latest member
SravanaSandhya

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top