Do you want to quickly and easily calculate working days in a particular range (including and/or excluding holidays)? In this blog piece, we show you how to do this. You can use the Excel NETWORKDAYS function to compute the working days of a particular month range, that is, the number of days that exist from one date to the next, excluding weekends. It allows for correct computation, including the exclusion of holidays, if they exist. See the steps below to discover how to reproduce this command in your excel file. This function can be used for both Google Sheets and your offline Excel Sheets.
Before demonstrating how to use the NETWORKDAYS formula in Excel, ensure that your worksheet includes all the information required to perform this function.
This information includes the start and end dates of the month(s) that you want to be analyzed. Here is an example below.
Related:
How To Lock Cells With Formulas In Excel
Here’s how to Create a Dropdown List in Google Sheets
NOTE: The format to use for the Dates is as follows: DAY/MONTH/YEAR (i.e Saturday, January 1, 2022).
How to calculate working days in Excel, including holidays
Here’s how to calculate the working days in a specific month range in Excel, without taking into account your holidays. We will be basing our formula on the example in the screenshot above.
To calculate the working days, Select the cell you want the number to be computed and insert the following formula below.
The formula is “=NETWORKDAYS (cell_with_startdate, cell_with_enddate)” OR, as per the example above: “=NETWORKDAYS (C2, D2)“. You can copy and paste it into the cell where the final calculation should appear.
NOTE: The value C2 replaces the position of the cell containing the beginning of the analyzed month, while the value D2 replaces the location of the cell containing the end of the examined period.
In this example, the date “Saturday, January 1, 2022” is written on C2. The value D2 must be replaced by the cell containing the range’s end date. In this example, the date “Monday, February 28, 2022” is situated in D2. As a result, the formula for as was = NETWORKDAYS (C2, D2) as you can see below.
After that, press the Enter key to see the proper number displayed. In this example, it will be 41. This because that is the number of working days between Saturday, January 1, 2022, and Monday, February 28, 2022, not including holidays.
How to calculate working days in Excel, excluding holidays
To find the number of working days in the period (excluding holidays), click on the cell where the final calculation should appear. Then, go ahead and paste the formula in that cell.
The formula is “=NETWORKDAYS (D2, E2, C2:C4)“, where C2:C4 indicate the range of cells containing holiday dates. There are just three values for holidays in the formula provided. You can add as many as you want (always separated by a colon) based on the number of cells containing holidays you have.
After pasting the formula, replace the cell values based on your spreadsheet (Start date, End date & Holidays cells respectively). In our example, the formula is =NETWORKDAYS (D2, E2, C2:C4).
When you press the Enter key, the proper number will appear. In our example, it will be 18. This is because that is the number of working days that occur between Saturday, January 1, 2022, and Monday, January 31, 2022 excluding the holidays.
That is how you are able to calculate working days in Excel Sheets. Feel free to share with us if this blog was helpful.