.png)
How to Subtract Dates in Google Sheets (Best Method!)
It is not uncommon for spreadsheet users to find themselves in a situation where they need to subtract dates in Google Sheets. Thankfully Google Sheets has multiple methods available to subtract or find the time difference between two dates. In this article we will cover all of them.
Click here to follow the examples below with our sample data
Minus Sign METHOD
Syntax
=final_date - initial_date
Where initial_date and final_date use one of the following formats:
- In YYYY/MM/DD format enclosed in double quotes
- References to the cells storing initial and final dates, respectively
Step 1: Identify the initial and final date
The initial date is at column A while the final date is at column B.
Step 2: Apply the formula final_date - initial_date
Format 1: April 8, 2022 should be written as “2022/04/08” and June 1, 2022 as “2022/06/01”:
="2022/06/01"-"2022/04/08"
Format 2: For Row 2, the initial date is at A2 while the final date is at B2. The formula becomes:
=B2-A2
Step 3: Press Enter
DAYS Method
Syntax
=DAYS(final_date,initial_date)
Where initial_date and final_date use one of the following formats:
- In YYYY/MM/DD format enclosed in double quotes
- References to the cells storing initial and final dates, respectively
Step 1: Identify the initial and final date
The initial dates are at column A while the final dates are at column B.
Step 2: Apply the formula =DAYS(final_date,initial_date)
Format 1: April 8, 2022 should be written as “2022/04/08” and June 1, 2022 as “2022/06/01”:
=DAYS("2022/06/01","2022/04/08")
Format 2: For Row 2, the initial date is at A2 while the final date is at B2. The formula becomes:
=DAYS(B2,A2)
Step 3: Press Enter
DATEDIF Function Method
The DATEDIF function does the same thing as the Days function but detects whether you have incorrectly set the order of initial and final dates.
Syntax
=DATEDIF(initial_date,final_date,”D”)
Where initial_date and final_date use one of the following formats:
- In YYYY/MM/DD format enclosed in double quotes
- References to the cells storing initial and final dates, respectively
The “D” option in the function ensures that the output is counted in days. You can change it to other options; “M” sets the output in terms of months.
Step 1: Identify the initial and final date
The initial dates are at column A while the final dates are at column B.
Step 2: Apply the formula =DATEDIF(initial_date,final_date,”D”)
Format 1: April 8, 2022 should be written as “2022/04/08” and June 1, 2022 as “2022/06/01”:
=DATEDIF("2022/06/01","2022/04/08",”D”)
Format 2: For Row 2, the initial date is at A2 while the final date is at B2. The formula becomes:
=DATEDIF(B2,A2,”D”)
Step 3: Press Enter
The function detects when you accidentally switched the initial and final dates in the formula, giving out the #NUM! error.
DAYS360 Function Method
The DAYS360 function is designed to accommodate financial calculations that define a year as having 360 days.
Syntax
=DAYS360(initial_date,final_date)
Where initial_date and final_date use one of the following formats:
- In YYYY/MM/DD format enclosed in double quotes
- References to the cells storing initial and final dates, respectively
Step 1: Identify the initial and final date
The initial dates are at column A while the final dates are at column B.
Step 2: Apply the formula =DAYS360(initial_date, final_date)
Format 1: April 8, 2022 should be written as “2022/04/08” and June 1, 2022 as “2022/06/01”:
=DAYS360("2022/06/01","2022/04/08",”D”)
Format 2: For Row 2, the initial date is at A2 while the final date is at B2. The formula becomes:
=DAYS360(B2,A2)
Step 3: Press Enter
NETWORKDAYS Method
This function counts only the workdays between two dates, essential for estimating the number of days for business transactions.
Syntax
=NETWORKDAYSinitial_date,final_date,holidays)
Where initial_date and final_date use one of the following formats:
- In YYYY/MM/DD format enclosed in double quotes
- References to the cells storing initial and final dates, respectively
Besides weekends, the function can also deduct holidays as long as you specify it in the holidays. The list can be either stored in a range or added to the formula by wrapping it in DATEVALUE function. For this article, we will store the list in another range.
Step 1: Identify the initial and final date
The initial dates are at column A while the final dates are at column B.
Step 2: Apply the Formula
Format 1: April 8, 2022 should be written as “2022/04/08” and June 1, 2022 as “2022/06/01”:
=NETWORKDAYS("2022/06/01","2022/04/08")
The list of holidays is stored in E2:E5:
=NETWORKDAYS("2022/06/01","2022/04/08",E2:E5)
Format 2: For Row 2, the initial date is at A2 while the final date is at B2. The formula becomes:
=NETWORKDAYS(B2,A2)
The list of holidays is stored in E2:E5:
=NETWORKDAYS(B2,A2,E2:E5)
Step 3: Press Enter
