When you’re tasked with a spreadsheet that involves a lot of dates, calculating the difference between multiple dates can be quite frustrating. Fortunately, Google Sheets allows users to calculate the difference between two dates inside a spreadsheet natively.
In this post, we’ll explain all the ways you count the number of days between two dates on Google Sheets.
How to calculate the difference between dates on Google Sheets
If you want Google Sheets to count all the days between two separate dates in a calendar including weekdays and holidays, then you can use the following two functions to get it done.
Method 1: Using the DAYS function
DAYS is a function inside Google Sheets that determines the difference between two dates and returns the difference value in days. The function will include all days between the two dates and will take into account leap days in a year automatically. 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 a spreadsheet you want to work on. Inside the Sheet, click on an empty cell and type =DAYS(“06/01/2022”, “01/01/2022”) if you wish to find the number of days between June 1, 2022, and January 1, 2022. You can replace the dates with your own in this format: MM/DD/YYYY if you’re using the US format or DD/MM/YYYY if you reside in the UK.
- As soon as you press the Enter key on your keyboard, the function will show the difference in days between the two dates you entered.
- When using the DAYS function this way, the one downside is that you’ll have to manually enter the end and start dates every time you want to change the dates in question. To avoid consuming time, you can use the DAYS function with cell references.
- Before we do that, let’s first enter all the start and end dates inside the spreadsheet in two separate columns. In the example below, we’ve specified the Start dates in Column D and End dates in Column E.
- If you want to reserve Column F for finding the difference between the two dates, click on any cell in Column F where you’ve entered the start and end dates (preferably the first cell where the dates have been added). In this instance, we selected cell F3.
- In this cell, enter type “=DAYS(D3,E3)” and press the Enter key on your keyboard.
- As soon as you press Enter, the spreadsheet will show you the output, that’s the difference between the two dates listed inside cells D3 and E3. Along with the difference between the two dates, Sheets will also preview the difference between dates you entered inside other cells in Columns D and E. These suggestions will be marked in light green.
- To apply these suggestions, click on the tick mark inside the Suggested autofill box.
- When you apply the suggested autofill, the differences between all the dates you provided will be entered under Column F.
You can use this type of spreadsheet to get the day difference between multiple dates at once.
Related: How to Highlight Duplicates in Google Sheets
Method 2: Using the DATEDIF function
DATEDIF function is pretty similar to the DAYS function as it calculates the difference between two dates in days but also provides the different values in months, years, and a combination of other quirks. This means, the difference in dates can be calculated in more than one unit and you can use this function to find the difference in multiple units at once.
The DATEDIF function is used inside 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 them. These units include:
- D: Total number of days between two dates
- M: Number of months between the two dates in whole numbers.
- Y: Number of whole years between the two dates.
- MD: This is the number of days between two dates where whole months are subtracted.
- YM: This is the number of months between two dates where whole years are subtracted.
- YD: This is the number of days between two dates if they were a year apart.
Once you’ve understood how this function works, you can start using it inside Google Sheets.
- Open a spreadsheet inside Sheets and enter the start and end dates on two different cells. As you can see from the example below, we’ve entered the start and end dates of the Genesis spacecraft mission inside cells B3 and B4.
- You can now calculate the difference between the two dates in six different units we explained above. So, we created another table under “Mission duration” to count the difference in all of these units listed inside cells B7 – B12.
- To calculate the difference in days, select cell B7 and type “=DATEDIF(B3, B4,”D”)“. We entered “D” as the unit to find the difference in days. To get your result in other units, type any of the units inside the quotes.
- Once entered, press the Enter key and you’ll see the mission duration in days is 1127 days.
- You can get the difference in months by replacing “D” with “M” as the unit in question. You can type the required syntax in cell B8 to see the number of months the Genesis mission ran for.
- Sheets will now tell you that the mission lasted 37 months in total.
- Similarly, the function will yield the following results across other units. We’ve mentioned the units we’ve used for each of the cells from B7 to B12 inside their adjacent rows under Column A [inside (“brackets”) under the “Mission duration” section].
- If you don’t wish to use cell references when playing with the DATEDIF function, you can directly calculate the difference between two dates by typing “=DATEDIF(“8/8/2001″,”9/8/2004″,”D”)” to get the result in days. You can replace the dates with your own as well as the unit in which it’s calculated.
- The result as you can see in cell B15 is the same as the value in B7 where we entered the dates from cell references (B3 and B4).
How to calculate workdays between two dates
Besides calculating the total number of days between two dates, Google Sheets also offers a function that lets you calculate all the workdays between two separate dates. This will be helpful when keeping an eye on the number of workdays or business days available for a given month and in keeping tabs on employee salary and leaves.
For this, we’re using the NETWORKDAYS function which counts only weekdays, omitting weekends (Saturdays and Sundays) in the process. Similar to DATEDIF, NETWORKDAYS requires you to input the story date first and the end date next for a valid answer.
The syntax for the NETWORKDAYS function looks like this – =NETWORKDAYS(“start date”, “end date”). The answer will be in days since we’re calculating the number of working days between two dates and it wouldn’t make sense to calculate them in years or months.
- Once you’ve understood how this function works, you can start using it inside Google Sheets. For that, open a spreadsheet inside Sheets and enter the start and end dates on two different cells. As you can see in the example below, we’ve created a spreadsheet to count the business days for whole months, as is evident from the start and end dates listed under columns A and B.
- To calculate the number of workdays between the given dates starting with Row 3, click on cell C3.
- Inside this cell, type “=NETWORKDAYS(A3,B3)“. You can replace this data with cell references of your own.
- Once you press the Enter key, Sheets will show you the day count of all working days between the said dates.
- You may or may not be shown autofill suggestions for other dates you have entered on the worksheet. To use the same function to find workdays between other dates, click on the blue square dot at the bottom right corner of cell C3 and drag it downwards.
- When you do that, the cells under column C will show the workdays between two dates from their respective rows.
- If you don’t wish to use cell references but want to calculate workdays between two dates by manually entering them, you can type “=NETWORKDAYS(“01/01/2022″,”02/01/2022”)“. You can replace these dates with ones of your own to get the desired results.
- When you press the Enter key after typing the above function, you should see the difference in days without counting Saturdays and Sundays in the count. As you can see from the example below, the difference in cell C9 is the same as in cell C3.
How to calculate the number of days since your birthday
If you’re interested in knowing how long you’ve been alive on this planet and how long until you get your birthday again from today, then you do that using two functions – DATEDIF and TODAY.
- To get started, create a sheet with your birthdate and a cell dedicated to show the current date and leave the latter empty until later. For example, we entered the birth date inside cell A2.
- In cell B2, type “=today()” which is a function that inputs the current date to the worksheet.
- When you press the Enter key after typing the above text, the current date should appear inside cell B2.
- We now created another table to calculate different kinds of data between your birthday and current date. As you can see in the example below, you can calculate the number of days, months, and years since the day you were born as well as the days between your last birthday and your next birthday. To calculate these numbers, we’ll 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 desired values. - Number of days since birth: =DATEDIF(A2,B2,”D”)
- Number of months since birth: =DATEDIF(A2,B2,”M”)
- Number of years since birth: =DATEDIF(A2,B2,”Y”)
- Number of days since last birthday: =DATEDIF(A2,B2,”YD”)
- Number of days to wait for next birthday: =365-B8
- Depending on the spreadsheet you’ve created, you may have to change the cell ranges accordingly. The result will look something like this:
You can check up on this spreadsheet at a later time to get updated values based on that day.
That’s all you need to know about calculating the difference between two dates on Google Sheets.
RELATED
