Calculate the Number of Days Between Two Dates in Google Sheets

There are a couple of useful functions in Google Sheets that will quickly calculate the number of days between the two specified dates.

This kind of calculation can be useful if you’re creating a schedule for your students or while creating project plans that have the start and end dates.

In this tutorial, I will show you how to calculate the number of days, number of weekdays, and number of weekends between two dates in Google Sheets.

Calculate the Total Number of Days Between Two Dates

Suppose you have the two dates as shown below:

Calculate the Total Number of Days Between Two Dates -Data

The easiest way to calculate the number of days between the two specified dates is to subtract the start date from the end date.

Below formula would work in this case:

=B2-B1

Calculate the Total Number of Days Between Two Dates -subtraction

Another way to calculate the same is to use the DAYS function.

Below formula would also give the same result:

=DAYS(B2,B1)

Using DAYS function to calculate days between dates

Note that the result here would not include both the start and end date in the calculation.For example, if the start date is January 1, 2018, and end date is January 2, 2018, the result would be 1.

For example, if the start date is January 1, 2018, and end date is January 2, 2018, the result would be 1.

If you want both the start and end date included in the result, you need to add 1 back to the result.

Calculate the Number of Working Days Between Two Dates in Google Sheets

If you’re creating a project plan, it’s likely that you are interested in the working days only.

Now to calculate the working days, you need to exclude all the weekend days and all the holidays occurring on weekdays.

Here are the two functions we will use to calculate the number of working days:

  • NETWORKDAYS function – Use this function when the weekend days are Saturday and Sunday (both included).
  • NETWORKDAYS.INTL function – Use this function when the weekend days are days other than Saturday and Sunday.

Let’s first have a look at the syntax of NETWORKDAYS in Google Sheets.

NETWORKDAYS Function – Syntax & Arguments

=NETWORKDAYS(start_date, end_date, [holidays])

  • start_date – a date value that represents the start date.
  • end_date – a date value that represents the end date.
  • [holidays] – (this is an Optional argument) This argument specifies the dates that are holidays and need to be excluded when calculating net working days. These could be public/national holidays. You can have these holidays in a range of cells and use the reference in this argument or create a named range for these cells and use the named range in the formula.

Now let’s see an example where we calculate the number working days between two dates.

Suppose you have a data set as shown below and you want to calculate the number of working days for each activity (the result should exclude holidays and Saturdays/Sundays).

Calculating Working Days using NETWORKDAYS formula in Google Sheets

Below is the formula that will give you the total number of working days:

=NETWORKDAYS(B2,C2,$F$2:$F$5)

Using NETWORKDAYS Formula to get number of working days

In most cases, the weekend days are Saturday and Sunday.

However, it may not always be the case. For example, in some countries, the weekend days are Friday and Saturday, and in some jobs, you may have a working Saturday and only Sunday is the weekend holiday.

In such cases, you need to use the NETWORKDAYS.INTL function.

Let’s first have a look at the syntax of the NETWORKDAYS.INTL function in Google Sheets.

NETWORKDAYS.INTL Function – Syntax & Arguments

NETWORKDAYS.INTL stands for Networkdays International function.

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

  • start_date – a date value that represents the start date.
  • end_date – a date value that represents the end date.
  • [weekend] – (Optional) Here, you can specify the weekend. There are two ways you can specify the weekend. It could be a string of numbers or a numeric value. If you omit this, Saturday and Sunday are taken as the weekend.
    • Using String of numbers – in this function, seven days are represented by seven numbers (each number being a 0 or a 1). A ‘0’ would mean a working day a ‘1’ would mean a non-working day. So the string for a week with Saturday and Sunday as weekend (non-working days) would be “0000011”, where the first number represents Monday, second represents Tuesday and so on.
    • Using a Number – 1 represents Saturday and Sunday as weekends, 2 represents Sunday and Monday and weekend as so on. You can use a number between 1 to 7 for all combinations of 2 consecutive weekend days. If you only have one single day as weekend day, use numbers from 11-17. 11 means that only Sunday is the weekend, 12 means that only Monday is the weekend and so on.String and Numeric values for NETWORKDAYS INTL function
  • [holidays] – (this is an Optional argument) This argument specifies the dates that are holidays and need to be excluded when calculating net working days. These could be public/national holidays. You can have these holidays in a range of cell and use the reference in this argument or create a named range for these cells and use the named range in the formula.

Now let’s see an example where we need to calculate the number of working days between two dates and the weekend days are Friday and Saturday.

Calculating Working Days using NETWORKDAYS formula in Google Sheets

Below is the formula that will give us the number of working days for each activity:

=NETWORKDAYS.INTL(B2,C2,7,$F$2:$F$5)

calculating working days in Google Sheets - weekends are friday and saturday

Note that the third argument of the formula is 7, which is the number that represents Friday and Saturday and weekend days.

If you want to use the string method of specifying weekends, you can use the below formula and it will give the same result:

=NETWORKDAYS.INTL(B2,C2,"0000110",$F$2:$F$5)

string method to specify weekens in NETWORKDAYS INTL function in Google Sheets

Remember the 0 in the string represents a working day and 1 represents a non-working day. “0000110” means that Friday and Saturday are non-working days.

Calculate the Number of Weekends Days Between Two Dates in Google Sheets

To calculate the number of weekends days between two dates, we can use the NETWORKDAYS function along with the DAYS function.

Suppose you have the data set as shown below:

calculating the number of weekend days in Google Sheets

Here is the formula that will give you the total number of weekend days between two specified dates in Google Sheets:

=DAYS(B2,A2)+1-NETWORKDAYS(A2,B2)

calculating weekend days using DAYS and NETWORKDAYS formula Google Sheets

Note that here we have assumed that the weekend days are Saturday and Sunday. In case you want to calculate it for days other than Saturday and Sunday, use the NETWORKDAYS.INTL function.

Calculating the Number of Mondays Between Two Dates

Sometimes, when scheduling classes for student or creating project plans, you may want to know exactly how many Mondays, or Tuesdays, or any other specific days are there between two dates.

You can use the NETWORKDAYS.INTL function to get the number of any specific day(s) in Google Sheets. The trick here is to specify only Monday as the working day and all the remaining days as the non-working days.

Here is the formula that will give the number of Mondays between two dates:

=NETWORKDAYS.INTL(A2,B2,"0111111")

number of mondays beyween two dates in google sheets

Note that the string used in the formula is “0111111”. This string means that Monday is a working day and all other days are non-working days.

Hence this formula only returns the number of Mondays.

You can use the same concept to find the number of any other weekday or combinations of weekdays. A situation where this could be useful is when you want to calculate the number of working days in a part-time job (where let’s say you work only on Mondays and Wednesdays).

You May Also Like the Following Google Sheets Tutorials:

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X