If you need to calculate the working days of a given period, that is, the number of days that exist from one date to another, excluding weekends, you can do this in Excel using the NETWORKDAYS (TOTALWORKDAYS) function. Through it, it is possible to make this calculation accurately, including excluding holidays, if they exist. See below the step by step of this command and learn how to replicate it in your file.
Before explaining how to use the NETWORKDAYS formula in Excel, it is important that you check that your worksheet contains all the information necessary to use this function. They are the start date of the interval that will be analyzed; the end date of that period and the date of holidays that exist in the analyzed period if any.
Each of this information must appear in an isolated cell of the worksheet and be formatted as follows: DAY/MONTH/YEAR, with the complete year, without abbreviations. Also, in case there is more than one holiday in the analyzed period, each of these dates must appear in a cell by itself.
How to calculate working days in Excel
That said, check out below how to calculate in Excel the working days of a given period, first without taking into account your holidays:
- Choose the cell and enter the formula: Click on the cell where the final calculation should appear and paste the formula =TOTALWORKDAYS(A2; A3). In this formula, the value A2 replaces the location of the cell that contains the beginning of the analyzed period, while the value A3 replaces the location of the cell that contains the end of the analyzed period;
- Adjust your number: In the formula =NETWORKDAYS(A2, A3) replace the value A2 with the cell with the start date of the range. In the case of our example, the date 10/04/2021 is located on A4. The value A3 must be replaced by the cell with the end date of the range. In the case of our example, the date 12/04/2021, located in B4. The formula, therefore, should look like this = NETWORKDAYS(A4; B4);
- Enter: Once that’s done, hit the Enter key and see the correct number appear. In the case of the example, it will be 45, since this is the number of working days that exist between 10/04/2021 and 12/04/2021, not excluding holidays.
How to calculate working days with holidays
- If you want to know the number of working days, excluding holidays in the period, click on the cell where the final calculation should appear and paste the formula =TOTALWORKDAYS(A2;A3;A4:A6), where A4 and A6 replace the row and column location of cells containing holiday dates.
Note that, unlike being separated by semicolons, as well as the start and end date of the evaluated period, they are separated by colons. In the formula presented there are only two values for holidays, but you can add as many as you need (always separating them with a colon) according to the number of cells with holidays you have.
- After pasting the formula, replace the values with the cells at the beginning of the evaluated period; end of the evaluated period and holidays from your spreadsheet. In the case of our example, the formula should look like this: =TOTALWORKDAYS(A4;B4;C2:C3).
- Hit the Enter key and see the correct number appear. In the case of the example, it will be 43, since this is the number of working days that exist between 10/04/2021 and 12/04/2021, taking into account the holidays in the interval.
