Calculation of the number of working days using built-in Excel functions

ca5fc888a0ba58bbbea1a3b006c0d099

Spreadsheet functionality created in Excel is often used for reporting and project status tracking. This also includes the number of working days, because their calculation is often very important. Built-in formulas allow you to automatically calculate the number of working days for a certain period of time, which I propose to deal with further.

Using the PURE OBJECT function

First, I propose to stop at a simple function called PURE PATIENTS, whose syntax involves counting the number of working days in a date range, taking into account standard weekends (Saturdays and Sundays), as well as manually specified holidays, if any. Use the instructions below to tackle this task.

  1. To begin, refer to the sheet where you keep records of dates. Make sure that the first and last dates in the range are in the same format.Selecting a date range for calculating workdays using the DAYS function in Microsoft Excel

  2. It is advisable to highlight each holiday with a separate color and mark it so as not to get confused when compiling the next formula.Selecting holidays to calculate working days using the HOLIDAYS function in Microsoft Excel

  3. In the empty space, announce the creation of the formula with a sign = and write PURE PATIENTS.Declare a formula for calculating working days using the CHEAPDAYS function in Microsoft Excel

  4. As the first argument, declare the cell where the start of the calculation date is specified.Entering the first argument to calculate workdays using the CHEAPDAYS function in Microsoft Excel

  5. After the sign ; specify the end date.Entering the second argument to calculate workdays using the CHEAPDAYS function in Microsoft Excel

  6. If there are no holidays, the formula can be closed and clicked Enterto apply the changes. As a result, you will see a number that shows the number of working days in the given interval.Viewing the result of the calculation of working days using the CYSTRADBNI function in Microsoft Excel

  7. If there are holidays, put them one after the other as arguments of the formula.Adding a holiday to calculate working days using the HOLIDAYS function in Microsoft Excel

  8. Below you can see that the number of working days has changed to take into account the holidays – so the formula works correctly.Happy holiday result after calculating working days using the HOLIDAYS function in Microsoft Excel

You can use this calculation on date ranges of any scale, specifying an unlimited number of holidays. Unfortunately, you’ll have to manually mark them all, as the calendar can’t count all the religious and public holidays in your region for you, and decide whether your company was open on those days.

The community is now in Telegram

Subscribe and stay up to date with the latest IT news

Sign up

Using the PURE OBJECT function.

As a second example, we will analyze almost the same function, but with an additional setting. It involves manual selection of days off if your company does not operate on a standard schedule of 5/2 with Saturday and Sunday off. Let’s understand how the calculation formula is created in the case.

  1. Instead of the function described above, this time declare CHISTRABDNI.INTER.Declaration of the formula for calculating working days using the function CYSTRABDNI.ZHD in Microsoft Excel

  2. I’ll jump into the arguments window to illustrate the syntax. In the first two fields, you specify the start and end date of the range as shown earlier.Entering a time interval for calculating working days using the CYSTRADBNI.BETWEEN function in Microsoft Excel

  3. The output is specified as a number, where 1 – Saturday Sunday, 2 – Sunday, Monday, 3 – Monday, Tuesday and so on. I will talk about these numbers later.Entering the number of days off for the calculation of working days using the function CYSTRABDNI.MIZH in Microsoft Excel

  4. Holidays are indicated in the same way as in the previous case.Entering holidays for calculating working days using the HOLIDAYS.IN function in Microsoft Excel

  5. As a result, you will get a similar formula, but with one change, which is to independently choose the days off.The result of applying the formula for calculating working days using the CYSTRABDNI.MIZ function in Microsoft Excel

Now let’s talk directly about those numbers that are indicated as days off. Microsoft has its own distribution system, the first part of which I have already described in one of the previous steps. You can specify two days in a row or only a specific day of the week as a weekend. You can find a complete list of numerical designations on the official Microsoft support page.

As you can see, there is nothing complicated in calculating working days from a range of dates, because for this there are two corresponding functions in Excel with a simple syntax. I hope my article helped you understand this, and now you have another formula in your arsenal that you can freely use when working with spreadsheets.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

© 2022 ZoNa365.ru - Theme by WPEnjoy · Powered by WordPress