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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Try the following


Excel 2010
ABC
220-Mar-2028-Mar-204
1c
Cell Formulas
RangeFormula
C2=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))={2,4,6}))
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
You can use this with the help of UDF:


Book1
ABCD
2Start DateEnd DateDay
327-03-202020-07-2020_2_____16
427-03-202020-07-2020_2__5__33
Sheet1
Cell Formulas
RangeFormula
D3=Datebet(A3,B3,C3)


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
 
Upvote 0
Or you can use this:


Book1
ABCD
2Start DateEnd DateDay
327-03-202020-07-2020_2_____16
427-03-202020-07-2020_2__5__33
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top