How to Calculate Working Days Excluding Sundays in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Calculate Working Days in Excel Excluding Sundays: 2 Ways

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, and Total Days. We need to find the Working Days in Column E.

how to calculate working days in excel excluding sundays


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.

Using Weekend Numbers in Formula with NETWORKDAYS.INTL

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.

how to calculate working days in excel excluding sundays

  • 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.

how to calculate working days in excel excluding sundays

  • 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.

how to calculate working days in excel excluding sundays


2. Utilizing Codes Inside NETWORKDAYS.INTL Function

We can use codes that 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.

Utilizing Codes Inside NETWORKDAYS.INTL function

  • Thirdly, press ENTER.
  • Fourthly, use the Fill Handle.
  • Eventually, the output is like this.

Read More: How to Calculate Working Days between Two Dates 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.

How to calculate Working Days Excluding Weekends and Holidays

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.

how to calculate working days in excel excluding sundays

  • Secondly, press ENTER.
  • Thirdly, use the Fill Handle to get all the outputs like this.


2. Utilizing NETWORKDAYS Function

We can also use the NETWORKDAYS function to calculate Working Days excluding Weekends and Holidays.
To do this, similarly, as shown before, use the NETWORKDAYS function to write the following formula in the E5 cell.

=NETWORKDAYS(B5,C5,$D$13:$D$15)

How to calculate Working Days Excluding Weekends and Holidays

  • Secondly, press ENTER.
  • Lastly, use the Fill Handle to get the output.

how to calculate working days in excel excluding sundays


Download Practice Workbook


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.


Related Articles


<< Go Back to Excel WORKDAY Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo