How to calculate the difference between dates on Google Sheets

If you are tasked with working with a spreadsheet containing a large number of dates, calculating the difference between multiple dates can be very frustrating. While the easiest option is to rely on an online date calculator, it may not be the most convenient as you may have to enter the dates one by one into the online tool and then manually copy the results into a spreadsheet.

For large numbers of dates, you need a tool that gets the job done more conveniently. Fortunately, Google Sheets allows users to locally calculate the difference between two dates in a spreadsheet. In this article, we will help you calculate the number of days between two dates on Google Sheets using some built-in functions.

How to calculate the difference between dates on Google Sheets

If you want Google Sheets to calculate all the days between two different dates in the calendar, including working days and holidays, then you can Use the following two functions to accomplish it.

Method #1: Use the DAYS function

DAYS is a function in Google Sheets that determines the difference between two dates and returns the difference in days. The function will include all days between two dates and will automatically take into account leap days in the year. The DAYS function on Google Sheets looks like this: DAYS(end_date,start_date).

To calculate the difference using the DAYS function, launch Google Sheets on your web browser and open the spreadsheet you want to work on. In the worksheet, click a blank cell and type =DAYS("06/01/2022", "01/01/2022")if you want to find June 1, 2022 and the year 2022 The number of days between January 1st. You can replace the date with your own format: MM/DD/ YYYY if you use the US format; or ## if you live in #In the UK, is DD/MM/YYYY.

As long as you press the

Enter key on your keyboard, this function will display the number of days between the two dates you enter.

One drawback when using the DAYS function in this way is that you have to manually enter the end and start dates each time you want to change the relevant dates. To avoid this time consuming task, you can use the DAYS function with a cell reference.

Before we do that, let’s first enter all the start and end dates in the spreadsheet into two separate columns. In the example below, we have specified the start date in column

D and the end date in column E.

If you want to keep column F to find the difference between two dates, click on any cell in column F where you entered the start and end dates (most Good is to add the date to the first cell). In this example, we selected cell

F3.

In this cell, enter

“=DAYS(D3,E3)” and press the Enter key on your keyboard .

After pressing Enter, the spreadsheet will display the output, which are the two listed in cells D3 and E3 difference between dates. In addition to the difference between the two dates, the table will also preview the difference between the dates you enter in other cells in columns D and E. These suggestions will be marked in light green.

#To apply these suggestions, click the

checkmark in the Suggested Autocomplete box.

When you apply suggested autocomplete, the differences between all dates you provide will be entered under column F.

#You can use this type of spreadsheet to get the difference in days between multiple dates at once.

Method #2: Use the DATEDIF function

The DATEDIF function is very similar to the DAYS function in that it calculates the difference between two dates in days, but also provides A combination of different values ​​for units and other quirks. This means, date differences can be calculated in multiple units and you can use this function to find differences in multiple units at once.

The DATEDIF function is used in Sheets as:

=DATEDIF(start_date, end_date, unit). As you can see from this syntax, the output of the function can have different units if you specify it. These units include:

• D: The total number of days between two dates
• M : The number of months between two dates, expressed as an integer.
• Y : The number of whole years between two dates.
• MD: This is the number of days between two dates minus the whole month.
• YM: This is the number of months between two dates minus the full year.
• YD: This is the number of days between two dates, if they are one year apart.

Once you understand how this feature works, you can start using it in Google Sheets. To do this, open a spreadsheet in Sheets and enter the start and end dates in two different cells. As you can see from the example below, we entered the start and end dates of the Genesis spacecraft mission in cells B3 and B4.

#You can now calculate the difference between two dates using the six different units we explained above. So we created another table under "Task Duration" to calculate the difference in all these units listed in cells B7 – B12.

To calculate the difference in days, select cell B7 and enter " =DATEDIF(B3, B4,"D") ". We enter "D" as the unit to calculate the difference in days. To get results in other units, type any unit within quotation marks.

After entering, press Enter, you will see the task duration (in days) is 1127 days .

You can get the difference in months by replacing "D" with "M" as the relevant unit. You can type the required syntax in cell B8 to see the number of months the Genesis task ran.

#Sheets will now tell you that the mission lasted a total of 37 months.

Similarly, this function will produce the following results in other cells. We have mentioned the units we are using for each cell from B7 to B12 which are located in adjacent rows under column A [("brackets" under the "Task Duration" section )Inside].

If you don't want to use cell references when playing with the DATEDIF function, you can do this by typing " =DATEDIF("8/8/2001","9/ 8/2004","D directly calculates the difference between two dates")"Get the result within a few days. You can replace the date with your own date and the unit in which it is calculated.

The result you see in cell B15 is the same value as in B7 where we entered the cell Dates in box references (B3 and B4).

How to calculate the number of working days between two dates

In addition to calculating the total number of days between two dates, Google Sheets also provides a Function that allows you to calculate all working days between two different dates. This can be helpful when keeping an eye on the number of available days or workdays in a given month and keeping tabs on employee pay and time off.

To do this, we use the NETWORKDAYS function that only calculates weekdays, omitting weekends (Saturday and Sunday) in the process. Similar to DATEDIF, NETWORKDAYS requires you to enter the story date first and then the end date to get a valid answer.

The syntax of the NETWORKDAYS function is as follows - =NETWORKDAYS(“start date”, “end date”). The answer will be in days because we are counting the number of working days between two dates and it doesn't make sense to count them in years or months.

Now that you understand how this feature works, you can start using it in Google Sheets. To do this, open a spreadsheet in Sheets and enter the start and end dates in two different cells. As you can see in the example below, we have created a spreadsheet to calculate the number of working days for the entire month, as can be seen from the start and end dates listed under columns A and B.

To calculate the number of working days between given dates starting on row 3, click cell C3.

In this cell, type "=NETWORKDAYS(A3,B3)". You can replace this data with your own cell reference.

After pressing the Enter key, the table will display the number of working days between the above dates.

#You may or may not see Autofill suggestions for other dates you enter in the worksheet. To use the same feature to find weekdays between other dates, click the blue square dot in the lower right corner of cell C3 and drag it down.

#When you do this, the cells under column C will display the working days between the two dates in their respective rows.

If you don’t want to use cell references, but want to calculate the working days between two dates by entering them manually, you can enter " =NETWORKDAYS("01 /01/2022","02/01/2022") ". You can replace these dates with your own to get the desired results.

When you press Enter after entering the above function, you should see the difference in the number of days without counting Saturday and Sunday . As you can see from the example below, the difference in cell C9 is the same as the difference in cell C3.

How to Calculate the Number of Days Since Your Birthday

If you want to know how long you have been alive on this planet and how long you have until today To celebrate birthday again, you can use two functions - DATEDIF and TODAY. First, create a worksheet that contains your date of birth and a cell dedicated to displaying the current date, leaving the latter blank until later. For example, we entered the date of birth in cell A2.

In cell B2, type " =today()", which is a method to enter the current date into the work Table functions.

When you press Enter after entering the above text, the current date should appear in cell B2.

#We have now created another table to calculate different types of data between your birthday and the current date. As you can see in the example below, you can calculate the number of days, months, and years since the date you were born, as well as the number of days between your last birthday and your next birthday. To calculate these numbers, we will use the DATEDIF function from Method #2 above.

If you want to create a similar table with other dates, you can use these codes to get the required values.

• Days after birth：=DATEDIF(A2,B2,”D”)

• ##Months after birth:=DATEDIF(A2,B2,”M”)

##After birth Number of years
• :=DATEDIF(A2,B2,”Y”)

Number of days since last birthday
• ：=DATEDIF(A2,B2,”YD”)

##Number of days to wait for the next birthday

：=365- B8

#Depending on the spreadsheet you create, you may need to change the cell ranges accordingly. The final result will look like this:

#You can view this spreadsheet later to get updated values ​​based on the current day.

The above is the detailed content of How to calculate the difference between dates on Google Sheets. For more information, please follow other related articles on the PHP Chinese website!