Excel: No. of Weekdays in a given week

Question

My Data

    A               B       C
1   Created Date    Week    No. of WorkDays
2   6/20/2018 11:36 06-w4   5
3   6/26/2018 12:56 06-w5   5
4   7/6/2018 23:01  07-w1   5
5   6/18/2018 18:11 06-w4   5
6   6/15/2018 12:01 06-w3   5
7   6/1/2018 13:31  06-w1   1
8   6/8/2018 12:17  06-w2   5
9   6/1/2018 13:32  06-w1   1
10  7/30/2018 13:32 07-w5   2

I have a week function that says whether an issue was created in Week 1 or 2 or so on. But for my calculation I need to consider workdays in that week, how do I do it. I'm calculating the no. of weekdays in that week manually now.

For example: June Week 1: No. of Weekdays is 1, because June 1 is Friday and June 2 is Saturday. Similarly for July Week 5 it is 2 days because July 29 is Sunday, July 30 & 31 are the weekdays.

My current formula for Week is

=CONCATENATE(TEXT(A1,"MM"),"-w",WEEKNUM(A1,1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),1)+1) 

I'm trying out to use Workday function, but it does not provide my desired result.

Kindly help me out with this.

Answer 1

Wrote my own VBA Formula - Results are as below

    A               B       C               D
1   Created Date    Week    No. of WorkDays No. of Days - Formula
2   6/20/2018 11:36 06-w4   5               5
3   3/2/2018 12:56  03-w1   2               2
4   7/6/2018 23:01  07-w1   5               5
5   6/18/2018 18:11 06-w4   5               5
6   6/15/2018 12:01 06-w3   5               5
7   6/1/2018 13:31  06-w1   1               1
8   6/8/2018 12:17  06-w2   5               5
9   6/1/2018 13:32  06-w1   1               1
10  7/30/2018 13:32 07-w5   2               2

Formula

=CalculateWorkdaysInWeek(A2)

VBA Code

Function CalculateWorkdaysInWeek(WeekRange As Range) As Variant
    'Assume Week 2, 3 & 4 will always have 5 days
    Dim WeekNo As Double
    'Check if Week is 1 or 5
    WeekNo = (Application.WorksheetFunction.WeekNum(WeekRange, 1) - _
    Application.WorksheetFunction.WeekNum(DateSerial(Year(WeekRange), Month(WeekRange), 1))) + 1

    Dim NoOfWeekDays As Integer
    If WeekNo = 1 Then
        FirstWeekDay = 7 - Weekday(DateSerial(Year(WeekRange), Month(WeekRange), 1), vbSunday)
        If FirstWeekDay > 5 Then
            FirstWeekDay = 5
        End If
        NoOfWeekDays = FirstWeekDay
    ElseIf WeekNo = 5 Then
        'Check the last day of the month as Monday as Start
        LastWeekDay = Weekday(DateSerial(Year(WeekRange), Month(WeekRange) + 1, 0), vbMonday)
        If LastWeekDay > 5 Then
            LastWeekDay = 5
        End If
        NoOfWeekDays = LastWeekDay
    Else ' Week 2,3 & 4 Return 5
        NoOfWeekDays = 5
    End If

    CalculateWorkdaysInWeek = NoOfWeekDays
End Function

Answer 2

If the date in the A column is always a weekday, you can use this: If that date can also be a weekend day, it will take the working days of the previous week. If you want to take the workinf days of the next week, you have to fiddle around still a bit.

=MIN(5,IF(MONTH(A2-WEEKDAY(A2,3))<MONTH(A2),7-WEEKDAY(DATE(YEAR(A2),MONTH(A2),1),1),IF(MONTH(A2+5-WEEKDAY(A2,2))>MONTH(A2),WEEKDAY(DATE(YEAR(A2),MONTH(A2)+1,0),2),5)))

• First MIN: restrict to max 5 working days
• First IF(): check if monday before or on date in A2 is in previous month
• If so: take 7 minus weekday of first of month (sunday being 1)
• If not so: second IF: check if friday this week is in next month
• If so: take the weekday of the last of this month (monday being 1)
• If not so: week in the middle of month, return 5

This of course does not take into account public holidays, only weekends.

Answer 3

For an inclusive # or workdays (e.g. Friday is 1 workday) try,

=NETWORKDAYS.INTL(A2, A2+5-WEEKDAY(A2,2), 1)

NETWORKDAYS.INTL allows for an optional holiday list if you want to create one.