How to Count Back From a Date in Google Sheets From a Different Field

One of the most common uses for Google Sheets is to create calendars and to handle information about dates, such as timesheets or vacation schedules.

Many users who create a spreadsheets dealing with dates find themselves needing to calculate how many days there are between two dates; that is, they need to find how many days there are between (as an example) July 1, 2018, and January 31, 2019.

You could just look on a calendar and count the days up by hand, and that would work fine if the dates were very close together, but for large numbers of dates or dates that are far apart, a little help from the computer would sure be nice.

Fortunately, Google Sheets has a number of 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 getting started, it's 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 into 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.

The MINUS Function

Unlike Excel, Google Sheets has a subtraction function which is very handy for calculating simple date differences. MINUS is Sheets' subtraction function 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 just fine for deducting one date from another. That is, as long as the dates are both in the same format. The syntax for MINUS is: =MINUS(value 1, value 2).

To use MINUS, open a blank Google Sheets spreadsheet in your browser. Enter (as an example) '4/4/2017' and '5/15/2017' in cells B3 and C3.

Now, select cell D3, which is where we'll put the MINUS function. Click inside the 'fx' bar, and then input '=MINUS(C3, B3)' and press Enter. Cell D3 will now return the value 40, as shown directly below.

This means there are 40 days between 4/5/2017 and 5/15/2017. You can also find the difference between the dates just by entering the cell references and not bothering with the MINUS function.

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

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

You might also input the cell references with the earlier 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 behind 5/15/2017.

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 on the spreadsheet or include the dates within DATEDIF instead.

The syntax for DATEDIF is:

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

To find the difference between 4/4/2017 and 5/15/2017 with DATEDIF, you should select a cell to add the function to (F3, in our case) and input '=DATEDIF' in the 'fx' bar. Then, expand the function with brackets that include the start date and end date cell references B3 and C3.

The unit days, otherwise "D," should also be at the end of the function. So the full function is '=DATEDIF(B3, C3, "D"),' which returns the value 40, as shown below.

DATEDIF will also work if you put the date information directly into the formula. Click a spreadsheet cell to add DATEDIF to, and then input '=DATEDIF("4/5/2017", "5/15/2017″,"D")' in the fx bar.

That will return 40 in the selected cell as shown below.

The DAY360 Function

Google Sheets includes DAY360, which calculates the difference between dates for a 360-day year. The 360-day calendar is used primarily for financial spreadsheets in which interest rate calculations might be required.

The syntax for DAYS360 is:

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

To use this function to your Google Sheets spreadsheet for the dates 1/1/2016 and 1/1/2017, enter '1/1/2016' in cell B4 as the start date, and then input '1/1/2017' in C4 as the end date for the function.

Now, select cell D4, input the function '=DAYS360(B4, C4)' in the 'fx' bar and press Enter. Then cell D4 will include a total of 360 days between the selected dates. Note that the only real use for this particular function is if you are working with interest rates.

The NETWORKDAYS Function

NETWORKDAYS also calculates the number of days between dates, but it's not entirely the same as the others. This function only counts the weekdays, so it leaves weekends out of the equation. (Read it as "Net Workdays" rather than "Network Days".)

As such, you can find the total number of weekdays between a couple of dates with NETWORKDAYS, and you can also specify extra holidays so that it excludes other dates.

The syntax for NETWORKDAYS is:

NETWORKDAYS(start_date, end_date, [holidays]).

You can add this function to your spreadsheet with the example dates 4/4/2017 and 5/15/2017 entered in cells B3 and C3. Select a cell to include the day total in, and click in the 'fx' bar to insert the function.

Input '=NETWORKDAYS(B3, C3)' and press the Enter key to add the function to whichever spreadsheet cell you've chosen for it. The NETWORKDAYS cell will include the total 29 for the number of days between the dates.

To add a holiday date to the function, first, enter '4/17/2017' in cell A3. Select the NETWORKDAYS cell, click the fx bar, and modify the function by adding the cell reference A3 to it. So the function would then be =NETWORKDAYS(B3, C3, A3), which will return 28 with the extra bank holiday also deducted from the total days.

Other Important Date-Related Functions

There are a number of date-related functions in Sheets that you should be familiar with if you are going to be doing a lot of work with dates.

• The DATE function converts a provided year, month, and day into a date. The format is DATE(year,month,day). For example, DATE(2019,12,25) returns "12/25/2019".
•  The DATEVALUE function converts a properly-formatted date string into 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 that a specific date falls on, 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 that a specific date falls on, 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 this writing, TODAY() would return "8/31/2019".
• The WEEKDAY function returns a numeric value showing the day of the week of the date provided. The format is WEEKDAY(date, type) and type can be 1, 2, or 3. If type is 1, days are counted from Sunday and Sunday has a value of 1. If type is 2, days are counted from Monday and the value of Monday is 1. If type is 3, days are counted from Monday and the value of Monday is 0. For example, 4/30/2019 is a Tuesday, and WEEKDAY("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.
• The YEAR function returns a numeric value showing the year of the date provided. The format is YEAR(date). For example, YEAR("12/25/2019") would return 2019.

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

How to Count Back From a Date in Google Sheets From a Different Field

Source: https://www.alphr.com/calculate-days-between-dates-google-sheets/

Share this post