While NETWORKDAYS function automatically assumes weekend days to be Sat and Sun, this may not be the case for all. Since the NETWORKDAYS formula anyway disregard weekend days when doing the calculation, it will also make sure it doesn’t remove the days twice (when it’s a weekend as well as a holiday) Calculate Workdays When Weekends are Not Sat and Sun For example, in the below holiday dates, there are eight holidays between 1st April and 30th December.īut NETWORKDAYS function only considers six of these and two are on weekend days anyway (Easter and Independence Day). Note that NETWORKDAYS function is smart enough to adjust the holidays and weekends by itself. The range that has the holiday dates – E2:E12 in this example.The below formula will give you the number of working days between the start and end date while not counting the holidays: = NETWORKDAYS ( B1, B2, E2:E12 ) Suppose you have a dataset as shown below: You can specify the range that has the holidays and it will automatically ignore these while calculating the workdays. Thankfully, NETWORKDAYS function already has this feature built in. Now when I am calculating the number of working days between two dates in Google Sheets, I don’t want to count the days that are holidays. Or if you’re a freelancer or small business, you can create this yourself too.īelow is an example of a holiday calendar that I have for 2020. In most companies, they provide a holiday calendar that you can use. Use Google Sheets to Calculate Days Between Dates For Workdays While Excluding Public Holidaysįor this to work, you need to have a list of holidays that you want to exclude. The good news is, Google Sheets can handle all this easily. Or you are from countries where weekend days are Friday and Saturday (and not Saturday and Sunday) For example, you may only get Sunday as the weekend and Saturday is working day for you.

You need to exclude public holidays from the working days.

While this may work well in most of the cases, there are two common scenarios where you may want to tweak this formula: So it counts all the days that are from Mon-Friday.

It considers Saturday and Sunday as the weekend days.

It includes both the start and end date when calculating the number of working days.

There are two things you need to know about the NETWORKDAYS function: The NETWORKDAYS function (as the name suggests), gives you the networking days in between two dates (it takes start date and end date as the input arguments) The below formula will give you the number of working days between these two dates: = NETWORKDAYS ( B1, B2 ) Suppose you have the dataset as shown below and you want to calculate the number of working days between these two dates right!Ĭalculating the number of working days between two dates is not as straightforward as subtracting the values, but it’s not too hard (as Google Sheets has a formula for it). In case the working days are different, you will need to change the formula (covered later in this tutorial).Īfter all, if it’s a project, you can’t expect your team to be working on weekends. While it’s nice and easy to get the total number of days between two dates, in most cases, you just don’t need the total days but the workdays.Īnd in case, I am assuming working days to be Monday to Friday. To get the number of days that include project start and end date both, use the below formula: = B2 - B1 + 1 Google Sheets Days Between Dates – Workdays Only In case you want to include the start date in the result, you need to add 1 to the resulting data. The number of days given in the above example is exclusive of the start date. There is one thing you need to know when subtracting dates. The above formula tells us that there are 274 days between the project start and end date. The below formula will give you the total number of days between these two dates. Since dates are nothing but numbers, you can easily get this by subtraction the start date from the end date. Suppose you have the start date and the end date of a project (as shown below) and you want to quickly know the total number of days between these two dates.

Using the MINUS Function to Calculate Days Between Two Dates in Google SheetsĬalculating Total Google Sheets Days Between Dates.

Calculate Days Between Dates in Google Sheets for a 360 Day Financial Year.

Use the DATEDIF Function to Calculate the Difference Between Two Dates in Google Sheets.

Use Google Sheets To Calculate Time Between Dates Using The DAYS Function.

Use Google Sheets to Calculate Date Difference For Single Weekdays Between Two Dates.

Use Google Sheets to Find the Difference Between Two Dates With Workdays and Non-Consecutive Days Off.

Calculate Workdays When Weekends are Not Sat and Sun.

Use Google Sheets to Calculate Days Between Dates For Workdays While Excluding Public Holidays.

Google Sheets Days Between Dates – Workdays Only.

Calculating Total Google Sheets Days Between Dates.

Date calculator between two dates