How to Calculate Business Days Left in Year in Excel: 5 Best Ways

Are you still manually calculating the remaining business days of a year? Let’s skip this hectic method and learn how to calculate business days left in a year in Excel.

Suppose it’s only the month of March, and you need to work for a particular company or client for the entire year. Now, you would definitely want to know how many business days are left in the year to find out the exact number of days you have to work more.

What will you do in this case? Will you open your calendar and start manually calculating the business days of all the remaining months? What if it’s a foreign company and follows a different weekend schedule and holiday calendar compared to your country?

Sounds complex, isn’t it? Well, stop worrying as I’ll tell you how to calculate business days left in a year in Excel.

I’ll discuss multiple methods so that you can easily calculate your remaining business days of a year from any given month. Also, these methods save you time and effort by eliminating manual calculation techniques.

Reasons to Find Out Business Days Left in a Year

You may need to find out the remaining business days in a year for different reasons. The most common reason is somewhat personal. 

It lets you find out how many days you need to work by counting the business days and excluding the weekends and holidays. Many people want to know the actual number of days they have to work in that year at the beginning of the year.

Apart from this, companies also want to find out the business days left in a year to calculate certain employee benefits that depend on the working days and business days. They may want to calculate this to know the number of business days available to complete a project or resolve a ticket.

Find Business Days Left in Year Using NETWORKDAYS Function

NETWORKDAYS is the easiest function you can use on Excel to get the business days left in a year. It automatically excludes the weekends like Saturdays and Sundays. Furthermore, the function can accommodate holidays (floating, federal, and state holidays) and deduct the same from the net workdays. The holiday argument is optional, but you can include it if you want in any of the following ways:

• A range of cells like A1:A10 as the list of holidays.
• An individual cell reference like A1.
• Serial numbers of holiday dates in an array constant.

Let’s find below the steps to use the NETWORKDAYS function in a worksheet:

1. Ensure you’ve got the following data in your Excel worksheet for the remaining business days calculation:
   • Start date: which is Project Start Date in the example
   • End date: which is Year End Date in the current example
   • A holiday list: cell range reference B7:B10 in this tutorial
2. Highlight a cell where you want to perform the calculation. It’s D2 in this tutorial.
3. Double-click the cell again and enter the following formula:

=NETWORKDAYS(B2,C2,B7:B10)

4. Hit the Enter key and you get the remaining working days in a flash.

Calculate Business Days Left in Year in Excel Using the nETWORKDAYS.INTL Function

The problem with the original NETWORKDAYS is Saturdays and Sundays are the default weekends. But, what if your organization has weekends on Friday and Saturday? Also, what if there’s just one weekend which is Sunday? In these scenarios, you must utilize NETWORKDAYS.INTL function. Find below the steps you should try:

1. Double-click the cell where you want the remaining business days and type an equal sign (=).
2. Now, type NET and double-click the NETWORKDAYS.INTL function that shows up as a suggestion.
3. Choose the start date, like B2. Also select the end date, which is C2.
4. Now, Excel shows you a drop-down menu for the weekend selection.
5. Find the weekend combination you need in the list. For example, number 7 is for Friday and Saturday.
6. Double-click that code or just type 7.
7. Choose the cell ranges for the holidays in your organization.
8. Don’t forget to delimit each of the above data inputs by a comma.
9. Close the formula by typing a closing parenthesis.
10. Hit Enter and you get the business days remaining in the year minus custom weekends and holidays.

If you need to exclude only Sundays from the remaining business days calculation, then put the numeric code 11 in the above formula. The number 11 in the NETWORKDAYS.INTL formula represents the Sunday only weekend.

In some multinational organizations, you may come across non-consecutive weekends or rotational weekends. Here, you won’t be able to use a weekend pair from NETWORKDAYS.INTL‘s weekend selection drop-down list. You must use a code to indicate the weekend.

The codes are made up of the numbers 0 and 1, where 0 is a working day and 1 is a non-working day. So, if you need to input Monday and Thursday as a weekend for an employee in the business days calculation, you can type “1001000” (Monday is always at the beginning of the code).

Thus, the above-shown NETWORKDAYS.INTL formula becomes as follows:

=NETWORKDAYS.INTL(B2,C2,"1001000",B7:B10)

Using the WEEKDAY function

You can also use the WEEKDAY function along with SUM, IF, ROW, and INDIRECT to calculate the remaining working days in a year from a start date and end date. The final formula is an array function so you must hit Ctrl + Shift + Enter to get the weekdays between two dates. If you’re using this formula, you’re considering all weekdays as working days.

You can also deduct the organization’s holidays from the remaining business days by using the COUNT function. Find below the step-by-step instructions along with the final formula to be used:

1. Double-click the target cell and enter the following formula:

=SUM(IF((WEEKDAY(ROW(INDIRECT(B2&":"&C2))))<6, 1, 0))-COUNT(B7:B10)

2. In the above formula, modify the input values as explained below:
   • B2 is the start date
   • C2 is the end date
   • B7:B10 is for the custom holidays like federal or floating holidays
3. Once done editing the formula, hit Ctrl + Shift + Enter.

4. You get the remaining workdays in the year to complete the project.

If Excel shows the output in a date format instead of an integer, follow these steps:

1. Select the cell again and hit Ctrl + 1 to bring up the Format Cells dialog box.
2. There, double-click the General category.

That’s it! You should get an integer as shown in the example above.

Use This Custom VBA Script to Find Business Days Left in a Year

Here is a VBA script you can use to automatically calculate the remaining working days in a year from a start date and end date. The script also includes a code element to exclude custom holidays. Follow these steps:

1. On the Excel worksheet where you need the remaining business days value, hit Alt + F11 to access VBA Editor.
2. Click Insert and select Module on the VBA Editor tool.
3. Into the blank Module, copy and paste this VBA script:

Sub CalculateRemainingWorkingDays()
    Dim startDate As Date
    Dim endDate As Date
    Dim holidaysRange As Range
    Dim holiday As Range
    Dim remainingDays As Long
    
    ' Read start date from cell B2
    startDate = Range("B2").Value
    
    ' Read end date from cell C2
    endDate = Range("C2").Value
    
    ' Set the holidays range as B7:B10
    Set holidaysRange = Range("B7:B10")
    
    ' Initialize remaining days as the total networkdays between start and end dates
    remainingDays = Application.WorksheetFunction.NetworkDays(startDate, endDate)
    
    ' Subtract the count of holidays falling between start and end dates
    For Each holiday In holidaysRange
        If holiday.Value >= startDate And holiday.Value <= endDate Then
            If Application.WorksheetFunction.Weekday(holiday.Value) < 6 Then
                remainingDays = remainingDays - 1
            End If
        End If
    Next holiday
    
    ' Display the result in cell D2
    Range("D2").Value = remainingDays
End Sub

4. In the above script, you should modify these code elements according to your own dataset:
   • B2: start date
   • C2: end date
   • B7:B10: range of holiday list
   • D2: target cell where you want the output
5. Click the Save button on the toolbar and close the VBA Editor.

6. Now, press Alt + F8 to open up the Macro dialog box.
7. There, click the CalculateRemainingWorkingDays macro and select the Run button to execute it.
8. You should see the working day calculation in the cell D2 or the one you selected.

Use Office Scripts to Calculate Business Days Left in a Year

Excel Automate is an advanced automation tool for Excel for Microsoft 365 and Excel on the web app. You get this feature when you buy a Microsoft 365 Business Standard subscription or above. If you see the Automate tab on the Excel ribbon, you can use this feature. In Excel Automate, you need to write Office Scripts codes.

If you’re already using this Excel coding feature and looking for a script to automate the remaining business days calculation in Excel, try these steps:

1. Click the Automate tab and then hit the New Script button inside the Scripting Tools block.
2. Now, go to the Code Editor panel and enter the following Office Scripts code:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range D2 on selectedSheet
	selectedSheet.getRange("D2").setFormulaLocal("=NETWORKDAYS(B2,C2,B7:B10)");
}

3. In the above code, you must modify the following code elements:
   • D2: cell reference where you want the final value
   • B2: start date
   • C2: end date
   • B7:B10: custom holiday list
4. Now, click the Save script button.
5. When ready, hit the Run button to perform the calculation.
6. You should see the remaining working days count in the cell D2 or the one you selected.

Need a way to modify the weekend in the above calculation? Use the following Office Scripts code instead:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range D2 on selectedSheet
	selectedSheet.getRange("D2").setFormulaLocal("=NETWORKDAYS.INTL(B2,C2,11,B7:B10)");
}

The above script will derive the business days left in a year from a start date to an end date minus all Sundays and custom holidays. Other codes you can use to denote weekends in the above script are:

Conclusions

Regardless of the reason behind your need to calculate the business days left in the year, you can easily get it done in Excel using the approaches mentioned above. Don’t know which one to start with? Let me help you with that.

If you’ve got basic or intermediate-level expertise in running Microsoft Excel, you can try any of the methods that use different functions, like NETWORKDAYS, NETWORKDAYS.INTL, and WEEKDAY to easily calculate the remaining business days. 

Using techniques that involve custom VBA scripts and Office Scripts is ideal for expert Excel users.

If you have any thoughts or comments on these methods, share that in the comment section.