Learn how to quickly count working days in Excel functions. Take a look at these two functions!

In the previous article "Excel Function Learning: The Dichotomy Principle of the LOOKUP Function", we learned about the dichotomy principle of LOOKUP. Today we are going to learn two working day functions. As the name suggests, they are specially used to calculate the number of working days. They are so easy to use!

In daily office work, we often encounter the need to calculate how many working days there are between two dates. The simpler way is to count the days against the calendar, but What about more days? This is when you need to use excel functions!

1. Open the following table and need to calculate how many working days there are between two different dates;

2. Enter in cell C2 Formula =NETWORKDAYS(A2,B2), press Enter to confirm and you will see the result, that is, there are a total of 7 working days from July 1st to July 10th. Double-click to fill in the formula and you will see column C. All results.

Comparing the calendar on the right, we can see that there are three weekends from July 1st to July 10th. These non-working days on weekends have been automatically excluded. The details are explained below. What does this formula mean:

3. In the working day calculation formula =NETWORKDAYS(A2,B2), the first parameter indicates the start date, which is July 1, and the second parameter Indicates the end date is July 10th. If there are specific dates that need to be excluded, you can also enter a third parameter in the formula.

For example, July 5th is an exceptional day off for the company and needs to be subtracted. You can enter these specific dates separately in column D and modify the formula to =NETWORKDAYS(A2,B2,D2) That's it, so that the specific day is subtracted from the original 7 working days to become 6 working days, as shown in the figure below.

Of course, you can also subtract the statutory holidays of this year (note that the adjusted dates are not weekends). For example, October 1st happens to be a Saturday. It is possible. Adjusted to be closed on September 30, then the special rest day should be September 30.

Note:

If the third parameter is a given Saturday or Sunday, then the function return result itself is subtracted from this day, and will not be subtracted repeatedly. Go.

4. We can also use the NETWORKDAYS function to calculate the number of weekend days within a specified time period:

Enter the formula in D2

=B2-A2-NETWORKDAYS(A2,B2) 1

The function returns 3, indicating that there are three weekends between July 1st and July 10th.

The meaning of this formula is to subtract the working days from the total number of days to get the weekend days. Because the value of B2-A2 is one day less than the actual number of days, 1 is added. In case you encounter the problem of counting weekend days, you might as well try this formula, it’s super easy to use!

Through the above explanation, we can see that this function can bring us great convenience in terms of statistics on issues related to working days. However, there is also a problem. What should we do if we are not closed on Saturdays and Sundays? I believe this is a problem faced by most human resources and finance companies.

do not be afraid! In the Excel 2010 version, there is an upgraded version of the working day function: NETWORKDAYS.INTL. The function is a bit long. I guess not many people can remember it. It doesn’t matter, as long as you remember it net, there will be a prompt in Excel:

We noticed that this function uses "custom weekend" to determine the number of working days. The previous NETWORKDAYS function Weekends cannot be modified (must be Saturday or Sunday). This difference makes it more flexible and widely used.

Still looking at the example above, the formula is modified to: =NETWORKDAYS.INTL(A2,B2,11). When entering the third parameter, a prompt will appear:

This is to let us choose which day to rest on. If we only have Sunday off, just choose 11. After completing the formula input, the result is:

The number of working days has changed from the original 7 days to 8 days, and Saturdays are also counted as working days.

(Only closed on Sundays, although it is a bit sad, but many people are like this~~~)

The most useful parameter in this function is the third parameter, in total There are 17 options:

If none of the 17 types of rest days provided meet the actual situation, you can also specify the rest days yourself, and you need to enter a 7-digit number. The first digit represents Monday and the last digit represents Sunday. Only 0 and 1 can be written in each position. 0 represents work and 1 represents rest. If only Tuesday is closed, the custom parameter is 0100000.

Same as the first function, if there are one or two special vacations in a month that need to be deducted, write them directly in the fourth parameter.

Okay, that’s it for today’s tutorial. I believe that with these two functions, human resources and finance MMs no longer have to worry about counting working days!

Related learning recommendations: excel tutorial

The above is the detailed content of Learn how to quickly count working days in Excel functions. Take a look at these two functions!. For more information, please follow other related articles on the PHP Chinese website!