Table of Contents
Calculate Working Days in MS Excel
Introduction:
Greetings to all welcome back to Twinkle Talks. In this article we have the solution for “How to Calculate working days in MS Excel”
Handling data in Excel always brings some challenges and excitement. If you know the technique, it will be your “excitement,” but if you don’t, it will become a “challenge” for you. In this blog post, you will learn and get clarification on some Excel works. Scroll down for more details.
Story – Twinkle Talks Pvt. Ltd
- Let’s assume you are working in an office or organisation called “Twinkle Talks Pvt. Ltd” as an data management department.
- In that office, we have multiple levels of employees, like office staff, supervisors, and workers.
- Different levels of staff have different weeks off. Let’s assume office staff have two weeks off, like Saturday and Sunday. Supervisors have one week off every Saturday.
- You have to calculate the employees salary based upon their attendance or work days.
- By using the “NETWORKDAYS” and “NETWORKDAYS.INTL” Excel functions, we can easily calculate the number of working days with and without holidays. Scroll down to look into this.
Aim:
To know “How to Calculate Working Days in Excel?”
By using Excel functions:
- “=NETWORKDAYS”
NEWTWORKDAYS Function
How to use NETWORKDAYS Function?
- You must have the starting and ending dates of a month or project.
- Press the ‘Equal’ key in keyboard and start typing NETWORKDAYS. Even before completing the text, you will get the function, like in the above picture.
- In between the open and close brackets, we have to select the starting date and ending date and (mentioning the holidays are optional) hit the enter button.
- Within a fraction of second, you will get the total number of working days by following some conditions.
- Readers may give an expression like What? Under some conditions? Yes my dear friends.
What are the Conditions? [Saturday and Sunday]
- This NETWORKDAYS function calculate the working days by keeping Saturdays and Sundays as holidays by default.
- If we don’t have the Saturday or Sunday as a holiday, then what should we do?
- For such cases, we have another co-related excel function which called as NETWORKDAYS.INTL
- Scroll down to get more information about that Excel function.
Example with Explanation
| S.no | Name | Type | Week Off | Starting Date | Ending Date |
| 1 | Swapna | Office Staff | Sat, Sun | 01-01-2024 | 31-01-2024 |
| 2 | Jagadeesh | Office Staff | Sat, Sun | 01-01-2024 | 31-01-2024 |
| 3 | Kaja Basha | Office Staff | Sat, Sun | 01-01-2024 | 31-01-2024 |
| 4 | David Paul | Office Staff | Sat, Sun | 01-01-2024 | 31-01-2024 |
| 5 | Kiranmayi | Office Staff | Sat, Sun | 01-01-2024 | 31-01-2024 |
- We have the Office staff details, we need to calculate the number of working days for the month January, 2024.
- There are no holidays in that month in this example.
- After applying the formula you will get the results like the picture below.
NETWORKDAYS.INTL Function
NETWORKDAYS.INTL Format/Structure
=NETWORKDAYS.INTL(start_date, end_date, [Weekend], [Holidays])
| S.no | Name | Position | Week off | Starting Date | Ending Date |
| 1 | Joshna | Supervisors | Only Saturday | 01-01-2024 | 31-01-2024 |
| 2 | Kausalya | Supervisors | Only Saturday | 01-01-2024 | 31-01-2024 |
| 3 | Prathyusha | Supervisors | Only Saturday | 01-01-2024 | 31-01-2024 |
| 4 | Anil | Supervisors | Only Saturday | 01-01-2024 | 31-01-2024 |
| 5 | Joel | Supervisors | Only Saturday | 01-01-2024 | 31-01-2024 |
What are the Conditions? [Selective Holidays and Weekends]
- As we discussed in the above example, the NETWORKDAYS excel function takes auto automatically Saturday and Sunday as the weekend.
- But in this NETWORKDAYS.INTL excel function, we have an option to choose the weekend.
- For example, if you have a weekend only on Saturday, we can select that day as the weekend, but in some companies and offices, the rotational shifts will be there. In such conditions, employees might have different weekends like Sunday-Monday, Monday-Tuesday, only Saturday, etc.,
- So just select the weekends that employees have and follow the same procedure as we discussed in the above function, and we will get the working days.
