Number of days of week between two date range by month

guilherme88 · Oct 10, 2019

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

Nishant94 · Oct 11, 2019

Book1

A

B

C

D

E

2

Start Date

End Date

Day

3

27-03-2020

20-07-2020

_2_____

Mar

1

4

Apr

4

5

May

4

6

Jun

5

7

Jul

2

8

9

10

11

Sheet1

Cell Formulas
Range	Formula
D3	=IF(ROWS(D$3:D3)>(MONTH(B$3)-MONTH(A$3)+1),"",TEXT(EOMONTH(A$3,ROWS(D$3:D3)-1),"mmm"))
E3	{=IF(D3="","",SUM(--ISNUMBER(MATCH(WEEKDAY(ROW(INDIRECT(IF(ROWS(E$3:E3)=1,A$3,EOMONTH(A$3,ROWS(E$3:E3)-2)+1)&":"&IF(TEXT(B$3,"mmm")=D3,B$3,EOMONTH(A$3,ROWS(E$3:E3)-1)))),11),(MID(SUBSTITUTE(C$3,"_",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(C$3,"_","")))),1)+0),0))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

guilherme88 · Oct 11, 2019

thank you but i couldnt understand how the formula works. what i am trying to get is to display the sums by month depending on month and year in the range
[TABLE="width: 794"]
<colgroup><col><col><col><col span="2"><col span="6"></colgroup><tbody>[TR]
[TD] Equip[/TD]
[TD]Station[/TD]
[TD] Frequency & Date[/TD]
[TD]from[/TD]
[TD]to[/TD]
[TD]Mar-20[/TD]
[TD]Apr-20[/TD]
[TD]May-20[/TD]
[TD]Jun-20[/TD]
[TD]Jul-20[/TD]
[TD]Aug-20[/TD]
[/TR]
[TR]
[TD]AT7[/TD]
[TD]ABZ[/TD]
[TD]1___567[/TD]
[TD="align: right"]27-Oct-19[/TD]
[TD="align: right"]28-Mar-20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

guilherme88 · Oct 11, 2019

Thanks Nishant the formula is now working

youre brilliant cheers

guilherme88 · Oct 11, 2019

Nishant94 said:
A B C D E
Start Date End Date Day
_2_____

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

[TD="align: right"][/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] "]Mar[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]1[/TD]

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

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

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

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

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

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

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

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/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"]=IF(ROWS(D$3:D3)>(MONTH(B$3)-MONTH(A$3)+1),"",TEXT(EOMONTH(A$3,ROWS(D$3:D3)-1),"mmm"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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] "]E3[/TH]
[TD="align: left"]{=IF(D3="","",SUM(--ISNUMBER(MATCH(WEEKDAY(ROW(INDIRECT(IF(ROWS(E$3:E3)=1,A$3,EOMONTH(A$3,ROWS(E$3:E3)-2)+1)&":"&IF(TEXT(B$3,"mmm")=D3,B$3,EOMONTH(A$3,ROWS(E$3:E3)-1)))),11),(MID(SUBSTITUTE(C$3,"_",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(C$3,"_","")))),1)+0),0))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

One last request Nishant

with different years in the dataset. i need to sum it by month and by origin (ABZ,BHX etc...) below a sample of the dataset.
If i want to display by month and year, would need the formula to be arranges in the following way:
OCT19 NOv19 DEC19 Jan20
ABZ 1 4
BHX 2 22 23 23

DATASET
[TABLE="width: 350"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]ABZ[/TD]
[TD]______7[/TD]
[TD="align: right"]27-Oct-19[/TD]
[TD="align: right"]27-Oct-19[/TD]
[/TR]
[TR]
[TD]ABZ[/TD]
[TD]___4___[/TD]
[TD="align: right"]31-Oct-19[/TD]
[TD="align: right"]26-Mar-20[/TD]
[/TR]
[TR]
[TD]BHX[/TD]
[TD]12__567[/TD]
[TD="align: right"]27-Oct-19[/TD]
[TD="align: right"]28-Mar-20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

How can this be achieved?

Thanks again
Guilherme

Nishant94 · Oct 11, 2019

guilherme88 said:
thank you but i couldnt understand how the formula works. what i am trying to get is to display the sums by month depending on month and year in the range
[TABLE="width: 794"]
<tbody>[TR]
[TD]Equip[/TD]
[TD]Station[/TD]
[TD]Frequency & Date[/TD]
[TD]from[/TD]
[TD]to[/TD]
[TD]Mar-20[/TD]
[TD]Apr-20[/TD]
[TD]May-20[/TD]
[TD]Jun-20[/TD]
[TD]Jul-20[/TD]
[TD]Aug-20[/TD]
[/TR]
[TR]
[TD]AT7[/TD]
[TD]ABZ[/TD]
[TD]1___567[/TD]
[TD="align: right"]27-Oct-19[/TD]
[TD="align: right"]28-Mar-20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Book1

A

B

C

D

E

F

G

H

I

J

K

15

Equip

Station

Frequency & Date

from

to

Mar-20

Apr-20

May-20

Jun-20

Jul-20

Aug-20

16

AT7

ABZ

1___567

27-Oct-19

28-Mar-20

16

17

AT7

ABZ

1______

27-Oct-19

24-May-20

5

4

3

Sheet1

Cell Formulas
Range	Formula
F16	{=IF(F$15="","",IF(SUM(--ISNUMBER(MATCH(ROW(INDIRECT((EOMONTH(F$15,-1)+1)&":"&EOMONTH(F$15,0))),ROW(INDIRECT($D16&":"&$E16)),0)))>0,SUM(--ISNUMBER(MATCH(WEEKDAY(ROW(INDIRECT(MAX($D16,F$15)&":"&MIN($E16,EOMONTH(F$15,0)))),11),(MID(SUBSTITUTE($C16,"_",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE($C16,"_","")))),1)+0),0))),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Nishant94 · Oct 11, 2019

guilherme88 said:
One last request Nishant

with different years in the dataset. i need to sum it by month and by origin (ABZ,BHX etc...) below a sample of the dataset.
If i want to display by month and year, would need the formula to be arranges in the following way:
OCT19 NOv19 DEC19 Jan20
ABZ 1 4
BHX 2 22 23 23

DATASET
[TABLE="width: 350"]
<tbody>[TR]
[TD]ABZ[/TD]
[TD]______7[/TD]
[TD="align: right"]27-Oct-19[/TD]
[TD="align: right"]27-Oct-19[/TD]
[/TR]
[TR]
[TD]ABZ[/TD]
[TD]___4___[/TD]
[TD="align: right"]31-Oct-19[/TD]
[TD="align: right"]26-Mar-20[/TD]
[/TR]
[TR]
[TD]BHX[/TD]
[TD]12__567[/TD]
[TD="align: right"]27-Oct-19[/TD]
[TD="align: right"]28-Mar-20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

How can this be achieved?

Thanks again
Guilherme

Using VBA:

Book1

A

B

C

D

E

1

DATASET

2

ABZ

______7

27.10.2019

3

ABZ

___4___

31.10.2019

26.03.2020

4

BHX

12__567

27.10.2019

28.03.2020

5

6

7

8

9

10

Oct-19

Nov-19

Dec-19

Jan-20

11

ABZ

2

4

5

12

BHX

3

22

23

21

13

Sheet3

Cell Formulas
Range	Formula
B11	=SumDate($A$2:$D$4,$A11,B$10)

VBA Code:

Code:

Function SumDate(rng As Range, st As Range, cr_month As Date)


Dim arr As Variant
Dim i As Integer, d As Long
Dim tday As String, testch As String
Dim tempsum As Integer
Dim Strtdate As Date
Dim Enddate As Date
Dim datearr(1 To 31) As Variant
Dim check As Integer
Dim y As Integer


arr = rng.Value
tempsum = 0
x = 1


For d = cr_month To Application.WorksheetFunction.EoMonth(cr_month, 0)
    datearr(x) = d
    x = x + 1
Next d


i = 0
x = 0


For x = 1 To UBound(arr, 1)
    
    If arr(x, 1) = st Then
        
        For y = 1 To UBound(datearr, 1)
            If datearr(y) >= arr(x, 3) And datearr(y) <= arr(x, 4) Then
                check = check + 1
            End If
        Next y
                
        If check > 0 Then
            tday = arr(x, 2)
            
            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
                
            Strtdate = Application.WorksheetFunction.Max(arr(x, 3), cr_month)
            Enddate = Application.WorksheetFunction.Min(arr(x, 4), Application.WorksheetFunction.EoMonth(cr_month, 0))
            tempsum = tempsum + Application.WorksheetFunction.NetworkDays_Intl(Strtdate, Enddate, tday)
        
            check = 0
            
        End If
        
    End If


Next x


SumDate = tempsum


End Function

Dave Patton · Oct 11, 2019

Notes
1. A variety of formula solutions for your consideration.
2. D1 - M1 are dates such as September 30, 2019 formated at mmm-yy
3. A7 cell is Text.

Excel 2010

A

B

C

D

E

F

G

H

I

J

K

L

M

1

DATASET

Sep-19

Oct-19

Nov-19

Dec-19

Jan-20

Feb-20

Mar-20

Apr-20

May-20

Jun-20

2

ABZ

Sun

27-Oct-19

1

3

ABZ

Thu

31-Oct-19

26-Jun-20

1

4

5

4

5

4

Thu

31-Oct-19

26-Jun-20

1

4

5

4

5

4

5

BHX

M,Tue,F,Sa,Su

27-Oct-19

28-Mar-20

3

22

23

21

20

6

M,Tue,F,Sa,Su

27-Oct-19

28-Mar-20

3

22

23

21

20

7

0011000

M,Tue,F,Sa,Su

27-Oct-19

28-Mar-20

3

22

23

21

20

8

1cc

Cell Formulas
Range	Formula
F3	=NETWORKDAYS.INTL(E1+1,F1,"1110111")
E2	=NETWORKDAYS.INTL(C2,D2,"1111110")
E3	=NETWORKDAYS.INTL(C3,E1,"1110111")
E4	=NETWORKDAYS.INTL(MAX(D1+1,$C$4),MIN($D$4,E1),"1110111")
E5	=NETWORKDAYS.INTL(MAX(D1+1,$C$5),MIN($D$5,E1),"0011000")
E6	=NETWORKDAYS.INTL(MAX(D$1+1,$C6),MIN($D6,E$1),Days5)
E7	=NETWORKDAYS.INTL(MAX(D$1+1,$C7),MIN($D7,E$1),$A$7)

Named Ranges
Name	Refers To	Cells
Days5	="0011000"

Tom.Jones · Oct 11, 2019

@Nishant94,

Excellent job.

guilherme88 · Oct 24, 2019

Amazing Nishant Thank you so so much

guilherme88 · Oct 24, 2019

Hi Nishant i have added another if to the formula and came to conclusion it is not reading the data correctly.
I want to cvount only the 00:25 turns so i put an if with the 00:25 in the formula. ABOVE THE COLUMNS corresponding to line 12. Thank you in advance

The formula is: IF([@Ground]=0.0173611111111111,IF(DC$5="","",IF(SUM(--ISNUMBER(MATCH(ROW(INDIRECT((EOMONTH(DC$5,-1)+1)&":"&EOMONTH(DC$5,0))),ROW(INDIRECT($I12&":"&$J12)),0)))>0,SUM(--ISNUMBER(MATCH(WEEKDAY(ROW(INDIRECT(MAX($I12,DC$5)&":"&MIN($J12,EOMONTH(DC$5,0)))),11),(MID(SUBSTITUTE($H12,"_",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE($H12,"_","")))),1)+0),0))),"")),"") +ctrlshiftenter

A B C D E F G H I J K DA DB DC[TABLE="width: 982"]
<tbody>[TR]
[TD="width: 60"] Equip[/TD]
[TD="width: 66"]Station[/TD]
[TD="width: 76"] Through[/TD]
[TD="width: 77"]Column1[/TD]
[TD="width: 66"]Arrives[/TD]
[TD="width: 70"]Departs[/TD]
[TD="width: 69"]Ground[/TD]
[TD="width: 142"] Frequency & Date[/TD]
[TD="width: 71"]from[/TD]
[TD="width: 71"]to[/TD]
[TD="class: xl70, width: 22"]Helper1[/TD]
[TD="class: xl67, width: 64"]Oct-19[/TD]
[TD="class: xl67, width: 64"]Nov-19[/TD]
[TD="class: xl67, width: 64"]Dec-19[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 982"]
<tbody>[TR]
[TD="width: 60"]AT7[/TD]
[TD="width: 66"]ABZ[/TD]
[TD="width: 76, align: right"]3242[/TD]
[TD="width: 77, align: right"]3243[/TD]
[TD="class: xl65, width: 66, align: right"]15:05[/TD]
[TD="class: xl65, width: 70, align: right"]15:30[/TD]
[TD="class: xl65, width: 69, align: right"]00:25[/TD]
[TD="width: 142"]__3____[/TD]
[TD="class: xl66, width: 71, align: right"]30-Oct-19[/TD]
[TD="class: xl66, width: 71, align: right"]18-Dec-19[/TD]
[TD="class: xl72, width: 22"][/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"] 5[/TD]
[TD="width: 64, align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]
result shoud be: 1 4 3

Krgrds
Guilherme

Number of days of week between two date range by month

guilherme88

New Member

Nishant94

Well-known Member

Excel Facts

guilherme88

New Member

guilherme88

New Member

guilherme88

New Member

Nishant94

Well-known Member

Nishant94

Well-known Member

Dave Patton

Well-known Member

Tom.Jones

Well-known Member

guilherme88

New Member

guilherme88

New Member

Similar threads

Forum statistics

Share this page

Number of days of week between two date range by month

New Member

Well-known Member

Excel Facts

New Member

New Member

New Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock