To deal with date-related calculations in Google Sheets, you should know the available date functions. Because there are many date functions in Google Sheets. In this post, you can learn how to find the total number of working and non-working days in Google Sheets.
As a reference please just open the following tutorial on a new tab that covers the complete date functions in Google Sheets.
Reference Guide: Google Sheets Date Functions at a Glance
Actually find the total working days between a start and end date is easy. There is a built-in function for this in Google Doc Sheets.
The suitable function to find working days between two given dates is the NETWORKDAY function. But then how to find the non-working days?
In this Google Sheets tutorial, I am going to provide you with a flexible solution. You can learn how to find the number of working and non-working days from a single date.
Suppose you want to find the working days from just today’s date. You can find that. Similarly, just provide me with a date in any month, I can help you to find the working and non-working days in that month!
How to Find the Total Number of Working and Non-Working Days in Google Sheets
As I’ve mentioned above, I am going to use the NETWORKDAY Google Sheets function for this purpose.
First I will tell you how to find the working days in a month from just today’s date.
Find the Total Number of Working Days in Google Sheets
If you check the NETWORKDAY function syntax, you can understand the following things.
NETWORKDAYS(start_date, end_date, [holidays])
The arguments start_date and end_date is mandatory to find the total network days in that period.
What we want is the networking days of the current month or the month of a provided date.
Formula 1
=NETWORKDAYS(eomonth(today(),-1)+1,eomonth(today(),0))
Just enter this formula in a cell in your Google Sheets file. This would return the total number of working days in the current month.
To find the total working days of any month, just replace the today() function with the date you want.
For example, I want to find the total working days in June 2018. Just enter any date in that month. See the formula.
=NETWORKDAYS(eomonth("22/06/2018",-1)+1,eomonth("22/06/2018",0))
Formula Logic:
The secret lies in the EOMONTH function. From a provided date we can find the end of the month date with this function. This we can use in NETWORKDAY as the end_date.
The same function we can use to find the starting date of any month of the given date. How?
=eomonth("22/06/2018",-1)
This formula can return the end of the month date of the previous month. Just add one (one day) to this to get the starting date of the current month or the provided date’s month.
This we can use in NETWORKDAY function as start_date. Now how to find the non-working days?
Find the Total Number of Non-Working Days in Google Sheets
We have already the total number of working days. Now you only want to minus this number from the end of the day of current month. Needless to say, if the end of the day is 31, that’s the total days in that month.
Formula 2
=day(eomonth(today(),0))-NETWORKDAYS(eomonth(today(),-1)+1,eomonth(today(),0))
See the first part of the formula. It’s as below.
=day(eomonth(today(),0))
This formula can return the total days in the current month. From this, you only want to deduct the total working days. That means you only want to deduct the Formula 1 from this just above provided day based tiny formula.
Similar to the formula 1, you can use any custom date to replace the today() from the formula 2.
That’s all. This way you can find the number of working and non-working days in Google Sheets.
Related Formulas:
1. Increment Months Between Two Given Dates in Google Sheets
2. How to Auto Populate Dates Between Two Given Dates in Google Sheets
3. Array Formula to Lookup Date Between Two Dates in Google Sheets
4. Lookup Earliest Dates in Google Sheets in a List of Items
5. How to Lookup Latest Dates in Google Sheets [Array Formula]
