Convert date range into sum of work days per week

[Hamitoco]

Hi,

If I have a date range in excel, lets say for example 8th Feb 2021 - 25th Feb 2021 am I able to create a formula that works out what weeks of the year that date range falls in and how many workdays (excluding weekends) per week.
Heres how I'm hoping it will work:
8th Feb-25th Feb falls in weeks 7-9 in the year (using excels weeknum funtion) for this particular date range there is a total of 5 workdays in week 7, 5 workdays in week 8 and 4 workdays in week 9.

Thanks.

 

[jtakw]

Hi,

You didn't specify how you want the results presented, maybe:

Book3.xlsx
	A	B	C	D
1	From	To	Week Numbers	Workdays
2	2/8/2021	2/25/2021	7 to 9	14
Sheet747

Cell Formulas
Range	Formula
C2	C2	=WEEKNUM(A2,1)&" to "&WEEKNUM(B2,1)
D2	D2	=NETWORKDAYS(A2,B2)

For the Week numbers, I'm using Begins with Sunday, change it to what you want.

 

[Hamitoco]

Hi,

Thanks for your reply. A bit of context for what I'm trying to achieve here. I am working with a labour forecast file where I would like to know how many days of annual leave is booked on any given week of the year. the examples below is kind of how i would like it to work or something similar if possible.

Thanks

 

[Phuoc]

Try this

Public Function CountDaysOfWeek(ByVal rngData As Range, Week As Long) As Long
Dim i As Long, j As Long, temp As Long, arrData As Variant
arrData = rngData.Value2
For i = 1 To UBound(arrData, 1)
    For j = arrData(i, 1) To arrData(i, 2)
        If Application.WeekNum(j) = Week Then
            If VBA.Weekday(j, vbMonday) < 6 Then temp = temp + 1
        End If
    Next j
Next i
CountDaysOfWeek = temp
End Function

Use:

=CountDaysOfWeek($B$2:$C$4,G2)