How to calculate days between dates in Google Sheets

One of the most common uses of Google Sheets is to create calendars and manage date information, such as timesheets or vacation schedules. Many users creating a spreadsheet that deals with dates need to calculate how many days there are between two dates; that is, they need to find how many days there are between (for example) July 1, 2018 and January 31, 2019.

You could just look at a calendar and count the days by hand, and that would work fine if the dates were close together, but for a large number of dates or dates that are far apart, a little computer help would surely be helpful. nice. Fortunately, Google Sheets has several ways to find the number of days between two dates.

Let’s take a look at the functions you can use to calculate days between dates in Google Sheets.

How to calculate days between dates in Google Sheets

Before we begin, it is important to note that these methods only work when using the American date format. If you’re not in the United States, you can go to Google Sheets and change your locale and time zone if you want to use these methods.

That being said, let’s take a look at how to find the number of days between two dates in Google Sheets.

How to calculate days between dates in Google Sheets using the LESS function

Unlike Excel, Google Sheets It has a subtraction function that is very useful for calculating simple date differences. LESS is Google Sheets’ subtraction feature, and because of the way dates are stored internally (as integers describing the number of days since a certain date in the past), it works well for deducing one date from another. That is, as long as both dates have the same format. The syntax of LESS is: = LESS (value 1, value 2).

1. Open a blank Google Sheets spreadsheet in your browser and enter (as an example) 4/5/2017 and 05/15/2017 in the cells B3 and C3.
   
2. Now, select cell D3, which is where we will place the LESS function. Click inside the fx slash and then type = LESS (C3, B3) and press Get into. Cell D3 will now return the value 40, as shown directly below.
   

This means there are 40 days between 4/5/2017 and 05/15/2017.

You can also find the difference between the dates by simply entering the cell references and not bothering with the LESS function.

1. For example, click the cell E3 and entrance =C3-B3 in the function bar, as shown in the snapshot directly below. That will also return 40. Although, since you are subtracting dates directly without LESS, the value in cell E will probably be displayed in date format and will look very strange.

You can convert the cell format to display an integer value by selecting Format > Number and Number.

You can also enter cell references with the previous date first. If you entered =B3-C3 In the function bar, the cell would contain the value -40. This highlights that 4/4/2017 is 40 days late compared to 05/15/2017.

How to calculate days between dates in Google Sheets using the DATEDIF function

DATEDIF is a function that helps you find the total days, months or years between two dates. You can find the total days between two dates entered in the spreadsheet or include the dates within DATEDIF.

The DATEDIF syntax is:

DATEIF(start_date, end_date, unit). The unit of the function can be D (days), M (months) or Y (years).

1. To find the difference between 4/4/2017 and 05/15/2017 with DATEDIF, you need to select a cell to add the function (F3, in our case) and enter =DATEIF(B3, C3, “D”) in it fx bar.

DATEDIF will also work if you put the date information directly into the formula.

1. Click on spreadsheet cell to add DATEDIF and then type =DATEIF(“4/5/2017”, “5/15/2017″”,D”) in it fx bar.

As you can see, the results are the same.

How to calculate days between dates in Google Sheets using the DAYS360 function

Google Sheets includes DAY360, which calculates the difference between dates for a 360-day year. The 360-day calendar is primarily used for financial spreadsheets where interest rate calculations may need to be performed.

The DAYS360 syntax is:

=DAYS360(start_date, end_date, [method]). He [method] is an optional indicator that you can include for the day count method.

1. As an example, enter 1/1/2016 in cell B4 as start date and then enter 1/1/2017 in C4 as the end date of the function.
   
2. Now select the cell D4enter function =DAYS360(B4, C4) in it fx bar and press Get into.
   

Cell D4 will include a total of 360 days between your selected dates. Note that the only real use for this particular feature is if you work with interest rates.

How to calculate days between dates in Google Sheets using the NETWORKDAYS function

NETWORKDAYS also calculates the number of days between dates, but it is not quite the same as the others. This function only counts weekdays, so it leaves weekends out of the equation. (Read this as “Net Business Days” instead of “Network Days.”)

As such, you can find the total number of business days between a pair of dates with NETWORKDAYS and you can also specify additional holidays to exclude other dates.

The NETWORKDAYS syntax is:

NETWORK DAYS(start_date, end_date, [holidays])

1. Using dates from the previous example, 4/4/2017 and 05/15/2017 entered in cells B3 and C3.
2. Select a cell and enter =NETWORK DAYS(B3, C3)and press the Get into.
3. To add a holiday date to the function, first enter 04/17/2017 in cell A3 and then modify the function by adding cell reference A3 to it. Then the function would be =NETWORK DAYS(B3, C3, A3)which will return on the 28th with the extra holiday also discounted from the total number of days.
   

Other important date-related functions

There are a number of date-related functions in Sheets that you should be familiar with if you will be working with dates a lot.

• He DATE The function converts a given year, month, and day to a date. The format is DATE (year, month, day). For example, DATE(2019,12,25) returns 12/25/2019.
• He VALUE DATE The function converts a properly formatted date string to a date integer. The format is DATEVALUE(date string); the date string can be any appropriate string, such as “12/25/2019” or “1/23/2012 8:5:30”.
• The DAY function returns the day of the month on which a specific date falls, in numeric format. The format is DAY (date). For example, DAY(“12/25/2019”) returns 25.
• The DAYS function returns the number of days between two dates. The format is DAYS (end date, start date). For example, DAYS(“12/25/20189”, “8/31/2019”) returns 116.
• The EDATE function returns a date a specific number of months before or after the given date. The format is EDATE (start date, number of months). For example, EDATE(“8/31/2019”, -1) returns “7/31/2019”.
• The MONTH function returns the month of the year in which a specific date falls in numeric format. The format is MONTH(date). For example, MONTH(“8/30/2019”) returns 8.
• The TODAY function returns the current date as a date value. The format is TODAY(). For example, at the time of writing, TODAY() would return “08/31/2019”.
• The WEEKDAY function returns a numeric value that displays the day of the week for the given date. The format is WEEKDAY(date, type) and the type can be 1, 2 or 3. If the type is 1, the days are counted from Sunday and Sunday has a value of 1. If the type is 2, the days are counted from Monday and the value of Monday is 1. If the type is 3, days are counted from Monday and the value of Monday is 0. For example, 4/30/2019 is Tuesday and DAY OF THE WEEK ( “4/30/2019”,1) would return 3, while WEEKDAY(“4/30/2019”,2) would return 2 and WEEKDAY(“4/30/2019”,3) would return 1.
• He YEAR The function returns a numeric value showing the year of the given date. The format is YEAR(date). For example, YEAR(“12/25/2019”) would return 2019.

Calculate day off

Google Sheets is an incredibly powerful program, especially for an app that is completely free. While it may not be as powerful as Microsoft Excel, it can still handle a wide range of tasks, including this one.