Finding the total number of working days between two dates in Excel is a frequently needed function. Usually, we overlook the weekends and holidays when calculating this. To exclude weekends and holidays from the count of workdays, Excel offers two distinct functions. In this article, we will discuss 2 ways of how to calculate working days in Excel, excluding weekends and holidays. Show
Download Practice Workbook2 Effective Ways to Calculate Working Days in Excel Excluding Weekends and HolidaysIn this article, we will discuss 2 handy ways to calculate working days in Excel, excluding weekends and holidays. Firstly, we will use the NETWORKDAYS function to calculate the workdays for two cases, one considering only the weekends and the other considering both weekends and holidays. Then, we will use the NETWORKDAYS.INTL function to calculate workdays for both cases mentioned earlier. 1. Using NETWORKDAYS FunctionThe NETWORKDAYS function calculates the number of workdays between two dates considering both weekends and holidays. This function assumes that the weekend is on Saturday and Sunday. We will use it to calculate the total number of workdays between two dates, considering weekdays as well as holidays. 1.1 Excluding Only WeekendsIn this method, we will use the NETWORKDAYS function and consider only the weekends. Steps:
1.2 Excluding Both Weekends and HolidaysIn this instance, we will consider weekends and holidays when calculating the net workdays. Steps:
2. Applying NETWORKDAYS.INTL FunctionIn this method, we will count the workdays using the NETWORKDAYS.INTL function. Here, we will consider weekends other than the regular Saturday and Sunday weekends. 2.1 Excluding Only WeekendsIn this instance, we will calculate the net workdays excluding only the weekends. Steps:
In this case, the third argument is 7 which denotes a Friday and Saturday weekend. The following is the list of numbers that denote different weekends. 2.2 Excluding Both Weekends and HolidaysIn this case, we will use the NETWORKDAYS.INTL function to get the values of total workdays between two dates. In this case, we will keep in mind not only the weekends but also the holidays. Steps:
ConclusionThis article is about counting the number of working days in Excel excluding the weekends and holidays. As you can see, there are two functions which are used to calculate the working days. The procedure is very simple and doesn’t require too many options. How do I count the number of days between two dates in Excel excluding weekends?The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates.
How do I ignore Saturday and Sunday in Excel?The WORKDAY. INTL function is based on the WORKDAY function, which is designed to add work days to a date. WORKDAY automatically excludes Saturday and Sunday, and optionally can exclude a list of custom holidays.
How do you calculate hours between two dates and times in Excel excluding weekends?Click on the cell where you want your result to appear, then enter "=NETWORKDAYS(A1,B1)-1-MOD(A1,1)+MOD(B1,1)", without the quotes. Press "Enter" to complete the function.
|