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
=final_date - initial_date
Where initial_date and final_date use one of the following formats:
The initial date is at column A while the final date is at column B.
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
=DAYS(final_date,initial_date)
Where initial_date and final_date use one of the following formats:
The initial dates are at column A while the final dates are at column B.
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)
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.
=DATEDIF(initial_date,final_date,”D”)
Where initial_date and final_date use one of the following formats:
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.
The initial dates are at column A while the final dates are at column B.
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”)
The function detects when you accidentally switched the initial and final dates in the formula, giving out the #NUM! error.
The DAYS360 function is designed to accommodate financial calculations that define a year as having 360 days.
=DAYS360(initial_date,final_date)
Where initial_date and final_date use one of the following formats:
The initial dates are at column A while the final dates are at column B.
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)
This function counts only the workdays between two dates, essential for estimating the number of days for business transactions.
=NETWORKDAYSinitial_date,final_date,holidays)
Where initial_date and final_date use one of the following formats:
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.
The initial dates are at column A while the final dates are at column B.
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)
If you enjoyed this article, you might also like our article on how to subtract time in Google Sheets or our article on how to change column width in Google Sheets.
If you want to learn how to set up data validation for email addresses in Google Sheets, we also suggest checking out our detailed guide.
