Shajratul Alam Towhid
If you are looking for how to calculate working days in Excel excluding Sundays, then you are in the right place. In every office and other organization, we need to keep a complete document of working days. Different organizations have different weekends. In this article, we’ll try to discuss how to calculate working days in Excel excluding Sundays.
Download Practice Workbook
2 Ways to Calculate Working Days in Excel Excluding Sundays
Excel offers a couple of limited ways to calculate working days excluding specific weekends. We need to follow some simple steps in the methods.
Before jumping into the methods let’s get to know the dataset we are going to use. Suppose, we have a dataset below where the column headers are Start Date, End Date, Total Days. We need to find the Working Days in Column E.
1. Using Weekend Numbers in Formula with NETWORKDAYS.INTL
Using the NETWORKDAYS.INTL function is the main method here. This function calculates working days between two dates excluding weekends according to our requirements.
We can use weekend numbers in the NETWORKDAYS.INTL function. Different weekends have different numbers. To use this function, we need to use the following dataset of Weekends and Weekend Numbers.
Steps:
- Firstly, write the formula in the D5 cell to find the Total Days between the Start Date and the End Date like this. We can use the DAYS function
=DAYS(C5,B5)Here, B5 and C5 refer to the first Start Date and first End Date respectively.
- Secondly, press ENTER to get the output as 270.
- Thirdly, use the Fill Handle to get all the outputs like this.
- Fourthly, write the formula in the E5
=NETWORKDAYS.INTL(B5,C5,11)Here, 11 refers to Sunday only. This formula will give the output of Total Workdays between two dates excluding Sunday.
- Fifthly, press ENTER and use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the E5
- Eventually, the output of Total Working Days will be like this.
Read More: How to Calculate Working Days in a Month in Excel (4 Easy Ways)
2. Utilizing Codes Inside NETWORKDAYS.INTL Function
We can use codes which are 0 and 1 in the NETWORKDAYS.INTL function to calculate Working Days between two dates. In the formula, the code starts from Monday. Here, 0 represents a Working Day, and 1 represents a Weekend or Holiday.
Steps:
- Firstly, find the Total Days in Column D by using the Days function as before.
- Secondly, write the following formula in the E5 cell like this.
=NETWORKDAYS.INTL(B5,C5,"0000001")Here, 0000001 refers 6 consecutive Working Days and 1 refers Weekend. The starting 0 is Monday and the last digit 1 is Sunday.
- Thirdly, press ENTER.
- Fourthly, use the Fill Handle.
- Eventually, the output is like this.
Read More: How to Calculate Working Capital Days in Excel (with Easy Steps)
Similar Readings
- How to Add Weeks to a Date in Excel (4 Simple Methods)
- Calculate Average Tenure of Employees in Excel
- How to Calculate Number of Days Between Two Dates in Excel
- Count Date Occurrences in Excel (3 Suitable Examples)
- How to Calculate Age on a Specific Date with Formula in Excel
How to Calculate Working Days Excluding Weekends and Holidays
We can calculate Working days between two dates excluding both weekends and holidays. Excel offers two functions to calculate these. The functions are the NETWORKDAYS function and the NETWORKDAYS.INTL function.
1. Using NETWORKDAYS.INTL Function
Suppose, we have the following dataset where we need to calculate Working Days in Column E excluding weekends and holidays. Here, the Holidays and their corresponding dates are also included.
Steps:
- Firstly, use the DAYS function to find Total Days in Column D.
- Secondly, use the NETWORKDAYS.INTL function in the E5 cell to write the following formula.
=NETWORKDAYS.INTL(B5,C5,2,$D$13:$D$15)Here, $D$13:$D$15 refers to the Dates of Holidays. 2 refers to Sunday and Monday as weekends and this formula will calculate the Working Days excluding Sunday and Monday.
- Secondly, press ENTER.
- Thirdly, use the Fill Handle to get all the outputs like this.
Read More: How to Find Number of Weeks Between Two Dates in Excel
2. Utilizing NETWORKDAYS Function
We can also use the NETWORKDAYS function to calculate Working Days excluding Weekends and Holidays.
To do this, similarly, as before, use the NETWORKDAYS function to write the following formula in the E5 cell.
=NETWORKDAYS(B5,C5,$D$13:$D$15)
- Secondly, press ENTER.
- Lastly, use the Fill Handle to get the output.
Read More: How to Add Days to a Date in Excel Excluding Weekends (4 Ways)
Conclusion
That’s all about today’s session. And these are the ways to calculate Working Days in Excel excluding Sundays and other weekends and holidays too. We strongly believe this article would be highly beneficial for you. Don’t forget to share your thoughts and queries in the comments section and explore our website Exceldemy, a one-stop Excel solution provider.
Related Articles
- Calculate Tenure in Years and Months in Excel
- How to Calculate Days Outstanding in Excel (With Easy Steps)
- Calculate Years and Months between Two Dates in Excel (6 Approaches)
- How to Count Days from Date to Today Automatically Using Excel Formula
- Use DateDiff Function in Excel VBA (5 Examples)
- How to Use COUNTIFS with Date Range in Excel (6 Easy Ways)
- Excel Formula to Count Days from Date (5 Easy Methods)
