Number of days of week between two date range by month

[guilherme88]

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]

Try the following

Excel 2010
	A	B	C
2	20-Mar-20	28-Mar-20	4
1c

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))={2,4,6}))

 

[Tetra201]

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]

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]

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]

You can use this with the help of UDF:

Book1
	A	B	C	D
2	Start Date	End Date	Day
3	27-03-2020	20-07-2020	_2_____	16
4	27-03-2020	20-07-2020	_2__5__	33
Sheet1

Cell Formulas
Range	Formula
D3	=Datebet(A3,B3,C3)

Code for UDF:

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]

Or you can use this:

Book1
	A	B	C	D
2	Start Date	End Date	Day
3	27-03-2020	20-07-2020	_2_____	16
4	27-03-2020	20-07-2020	_2__5__	33
Sheet1

Cell Formulas
Range	Formula
D3	{=SUM(--ISNUMBER(MATCH(WEEKDAY(ROW(INDIRECT(A3&":"&B3)),11),(MID(SUBSTITUTE(C3,"_",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(C3,"_","")))),1)+0),0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[guilherme88]

You can use this with the help of UDF:

	A	B	C	D
Start Date	End Date	Day
_2_____
_2__5__

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]27-03-2020[/TD]
[TD="align: right"]20-07-2020[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]16[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]27-03-2020[/TD]
[TD="align: right"]20-07-2020[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]33[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=Datebet(A3,B3,C3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Code for UDF:

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]

Thanks you so much

 

[guilherme88]

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.[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Day[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]27-03-2020[/TD]
[TD="align: right"]20-07-2020[/TD]
[TD]_2_____[/TD]
[TD="align: right"]16[/TD]
[/TR]
</tbody>[/TABLE]
MAR20 APR20 MAY20
1 4 4


Thanks a lot