Are you tired of calculating workdays manually? Microsoft Excel’s got your back! Today, I’ll show you how to calculate workdays in Excel.

Excel is good with numbers, and since dates are stored as numbers in this spreadsheet app, it’s no doubt the best tool to calculate workdays for payroll processing. However, it doesn’t always automatically calculate holidays and weekends. You must use certain functions smartly to exclude off days and holidays from workdays.

Follow along with me as I show you the best and proven ways to count workdays in different ways. Let’s get started!

Using Various Excel Functions

Find below a couple of Excel functions that you can use to calculate workdays effortlessly:

WORKDAY Function

The WORKDAY function in Excel calculates a date that is a specified number of working days before or after a given start date. It excludes the default weekends and optional holidays.

It’s useful to calculate project deadlines, expected delivery dates, invoices due, or work schedules by automatically skipping off days.

Suppose you’ve got a dataset as shown in the above screenshot where you need to calculate the day when a project is due for each contractor you’re hiring for a fixed number of days.

Here, you can use the WORKDAY function to calculate the project deadline, incorporating workdays and excluding weekends and holidays.

Create the Project Deadline or Project Due column in your dataset.

Select the first cell in this column and enter the following formula into it:

=WORKDAY(B2,C2,$F$2:$F$6)

In the above formula, B2 is the start date of the project, C2 is the number of days for which you wish to hire a contractor, and $F$2:$F$6 is the cell range that contains the holidays you want to exclude. The formula will, by default, exclude all Saturdays and Sundays.

Don’t forget to modify the cell range references of the above formula syntax according to your own dataset.

Hit Enter to calculate the future date after a specific number of workdays.

Select this cell and drag the fill handle down the column to populate the future dates for the rest of the cells in column Project Deadline.

Now, select the entire output data in the Project Deadline column and apply the Short Date number formatting from Home > Number > General dropdown menu > Short Date.

That’s it! You’ve calculated the project end date using the WORKDAY function after a given number of working days.

WORKDAY.INTL Function

Now, if you wish to change the weekends manually, you can use the WORKDAY.INTL function.

Select the destination cell in your worksheet and enter the following formula into it:

=WORKDAY.INTL(B2,C2,3,$F$2:$F$6)

In this formula, B2 refers to the start date, C2 refers to the number of days of the contract or project, the number 3 represents weekends consisting of Mondays and Tuesdays, and $F$2:$F$6 refers to the holidays to be excluded.

If you wish to choose a different pair of weekends, enter the numerical value for the corresponding weekend combinations shown below in place of number 3 in the above formula:

Hit Enter to calculate the formula cell.

Use the fill handle and drag it down to generate the project deadlines for the rest of the cells.

Apply the Short Date number formatting style from the Home > Number commands block to get the actual date value instead of a serial number of the date.

NETWORKDAYS Function

The NETWORKDAYS function in Excel calculates the number of working days between two dates. It automatically excludes weekends (Saturday and Sunday). You can also specify holidays to exclude.

It’s useful for tracking employee workdays. It helps calculate deadlines. Also, it measures business processing times without counting weekends.

This function simplifies workday calculations. It eliminates manual counting errors. Also, it ensures accurate business date tracking.

The ideal dataset where you can use this Excel function should contain a start date and an end date. The holiday list is optional.

For example, find above the sample dataset that I used to create this tutorial.

Select the first cell below the Workdays column and enter the following formula into it:

=NETWORKDAYS(B2,C2,$F$2:$F$6)

Here are the references used in the above formula for the function arguments:

• B2: Start date
• C2: End date
• $F$2:$F$6: Holidays

Make sure you modify the formula according to your own dataset.

Hit Enter to calculate the formula cell.

Now, use the fill handle to replicate the same formula down the column by dragging down the fill handle.

Congratulations! You’ve successfully calculated workdays in Excel using the NETWORKDAYS function.

NETWORKDAYS.INTL Function

Do you want to exclude a custom weekend from the start and end date when calculating workdays? You need to use the NETWORKDAYS.INTL function. Let me show you how in easy steps.

Select the destination cell where you want to calculate the first workday and enter the following formula syntax into it:

=NETWORKDAYS.INTL(B2,C2,4,$F$2:$F$6)

The arguments in the above formula are exactly the same as those of the NETWORKDAYS function, except for the numerical 4, which is the weekend combination code for the Tuesday and Wednesday week off.

Ensure you customize the references in the formula arguments accordingly.

Hit Enter to calculate the cell.

Use the fill handle to copy and paste the formula in the rest of the cells of the Workdays column.

Excel will calculate workdays automatically using the NETWORKDAYS.INTL function.

Using Excel VBA

If you wish to automatically calculate workdays in Excel with visual guidance and prompts, this Excel VBA macro will help.

Firstly, find out the steps to create a VBA macro using a VBA script from this effortless Excel tutorial:

📒 Read More: How To Use The VBA Code You Find Online

If you’re ready, use this VBA script to create your own VBA macro:

Sub CalculateWorkdays()
    Dim startRange As Range
    Dim endRange As Range
    Dim weekendString As String
    Dim holidayRange As Range
    Dim outputRange As Range
    Dim holidays() As Date
    Dim holidayCount As Integer
    Dim startDate As Date
    Dim endDate As Date
    Dim result As Long
    Dim i As Integer

    ' Prompt to select start date
    On Error Resume Next
    Set startRange = Application.InputBox("Select Start Date(s):", Type:=8)
    If startRange Is Nothing Then Exit Sub

    ' Prompt to select end date
    Set endRange = Application.InputBox("Select End Date(s):", Type:=8)
    If endRange Is Nothing Then Exit Sub

    ' Prompt to input weekend pairs
    weekendString = Application.InputBox("Enter weekend days (comma separated, e.g., Saturday,Sunday):", Type:=2)
    If weekendString = "" Then Exit Sub

    ' Prompt to select holiday list
    Set holidayRange = Application.InputBox("Select Holiday List:", Type:=8)
    If holidayRange Is Nothing Then Exit Sub

    ' Store holidays in an array
    holidayCount = holidayRange.Cells.Count
    ReDim holidays(1 To holidayCount)
    For i = 1 To holidayCount
        holidays(i) = holidayRange.Cells(i).Value
    Next i

    ' Prompt to select output destination
    Set outputRange = Application.InputBox("Select Output Destination:", Type:=8)
    If outputRange Is Nothing Then Exit Sub

    ' Calculate workdays for each start and end date pair
    For i = 1 To startRange.Cells.Count
        startDate = startRange.Cells(i).Value
        endDate = endRange.Cells(i).Value

        result = CalculateCustomWorkdays(startDate, endDate, weekendString, holidays)
        outputRange.Cells(i).Value = result
    Next i

    MsgBox "Workdays calculation complete!"
End Sub

Function CalculateCustomWorkdays(startDate As Date, endDate As Date, weekendString As String, holidays() As Date) As Long
    Dim currentDate As Date
    Dim isWeekend As Boolean
    Dim isHoliday As Boolean
    Dim weekendDays() As String
    Dim workdaysCount As Long
    Dim i As Integer

    weekendDays = Split(weekendString, ",")

    workdaysCount = 0
    For currentDate = startDate To endDate
        isWeekend = False
        isHoliday = False

        ' Check if currentDate is a weekend
        For i = LBound(weekendDays) To UBound(weekendDays)
            If VBA.Format(currentDate, "dddd") = Trim(weekendDays(i)) Then
                isWeekend = True
                Exit For
            End If
        Next i

        ' Check if currentDate is a holiday
        For i = LBound(holidays) To UBound(holidays)
            If currentDate = holidays(i) Then
                isHoliday = True
                Exit For
            End If
        Next i

        ' Count as workday if it's not a weekend or holiday
        If Not isWeekend And Not isHoliday Then
            workdaysCount = workdaysCount + 1
        End If
    Next currentDate

    CalculateCustomWorkdays = workdaysCount
End Function

When you’re done creating the macro, press Alt + F8 to bring up the Macro dialog box.

Select the CalculateWorkdays macro and hit Run.

Excel will show a dialog box so you can select the Start Date column.

Then, you’ll also see another dialog box to select the End Date column.

Now, use the next input box to type in the weekend combination separated by a comma.

There will also be a dialog box to select a list of holidays.

Finally, select the destination column to populate the workdays.

That’s it! You’ve used an Excel VBA macro to calculate workdays in a few steps.

Conclusions

So, now you know how to calculate workdays in Excel using various Excel functions. Also, you learned about the automated method using Excel VBA.

Use any of the above methods that suit your style of using Microsoft Excel.

If you liked this guide, share an acknowledgment in the comment box below. If you’ve got any suggestions or feedback, please don’t hesitate to comment.