To subtract dates in Google Sheets, click an empty cell and type the later date’s cell minus the earlier one, such as =B2-A2, then press Enter. The answer is the number of days between them. For months, years or business days, use DATEDIF or NETWORKDAYS instead. These steps use Google Sheets in a web browser on a Windows PC, Mac or Chromebook.
What to know before you subtract
Google Sheets keeps every date as a plain number behind the scenes, called a serial number. It uses the 1900 date system and counts days from December 30, 1899, so a date such as March 2, 2026 is really the number 46083 with date formatting on top. Because each whole number is one day, subtracting one date from another gives you a count of days, and subtracting a number from a date moves the date backward.
A few terms used below:
- Start date: the earlier date, such as the day a project begins.
- End date: the later date, such as a deadline.
- Cell reference: a column letter and row number, like
B2, that points a formula at a cell’s value. - Real date: a value Sheets recognizes as a date. A value that only looks like a date but is stored as text will not subtract correctly.
New to formulas in Sheets? The basics of the minus sign and cell references are covered in this walkthrough of subtraction in Google Sheets, and everything there applies to dates too.
Subtract two dates with the minus sign
This is the fastest method and the right one whenever you want total calendar days.
- Type the start date in one cell, for example
3/2/2026in cell A2. Sheets should align it to the right of the cell, which is a quick sign it was read as a real date. - Type the end date in another cell, for example
6/19/2026in cell B2. - Click the cell where you want the answer, such as C2.
- Type
=B2-A2. Put the end date first so the result is positive. - Press Enter. The cell shows
109, the number of days from March 2 to June 19, 2026. - If the cell shows a date such as 4/18/1900 instead of a number, select it and click Format > Number > Number (or Automatic) to see the day count.
- To repeat the formula for more rows, click C2, then drag the small square at the bottom-right corner of the cell down the column.
Plain subtraction counts the days between the dates, so the start day itself is not included. If a booking runs from Monday through Friday and you want 5 days rather than 4, add one: =B2-A2+1.
Which date function should you use?
The minus sign covers most needs, but several built-in functions answer more specific questions. Pay attention to argument order, because it is not the same in every function.
| You want | Formula | Argument order |
|---|---|---|
| Total days | =B2-A2 | End, then start |
| Total days, self-explaining | =DAYS(B2,A2) | End, then start |
| Total days as a function | =MINUS(B2,A2) | End, then start |
| Whole months or years | =DATEDIF(A2,B2,"M") | Start, then end |
| Weekdays only | =NETWORKDAYS(A2,B2) | Start, then end |
| Workdays with a custom weekend | =NETWORKDAYS.INTL(A2,B2,7) | Start, then end |
| Days on a 360-day finance calendar | =DAYS360(A2,B2) | Start, then end |
DAYS: the same answer with clearer intent
The DAYS function uses the syntax DAYS(end_date, start_date) and returns the number of days between the two dates, accounting for leap days. With the dates above, =DAYS(B2,A2) returns 109, the same as the minus sign. Many people prefer it in shared sheets because the function name tells the next reader what the formula is for, and the same function exists in Excel.
MINUS: the operator as a function
MINUS(value1, value2) subtracts the second value from the first and works exactly like the - operator, so =MINUS(B2,A2) also returns 109. It has no special date logic. The details of its arguments are in this explainer on the MINUS function.
DATEDIF: months, years and leftover days
Dividing a day count by 30 or 365 gives a rough figure at best. DATEDIF counts complete calendar units instead. Its syntax is DATEDIF(start_date, end_date, unit), and the unit goes inside double quotation marks.
| Unit | Returns |
|---|---|
"D" | Total days between the dates |
"M" | Whole months between the dates |
"Y" | Whole years between the dates |
"YM" | Whole months left over after removing whole years |
"MD" | Days left over after removing whole years and months |
"YD" | Days between the dates as if they were less than a year apart |
A month only counts once the end date reaches the matching day of the month. From September 30, 2015 to February 28, 2016, for example, DATEDIF reports 4 whole months, not 5. The "YM", "MD" and "YD" units return partial pieces, not totals, so use them to build a label rather than as a full duration.
To show a length of time in words, join the pieces with the & operator:
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"
For a start date of 10/16/2023 and an end date of 11/18/2026, that formula returns 3 years, 1 months, 2 days. Wrap each piece in an IF if the singular and plural wording matters to you.
NETWORKDAYS: business days only
NETWORKDAYS(start_date, end_date, [holidays]) counts working days and treats Saturday and Sunday as the weekend. Unlike plain subtraction, it counts both the start and end dates when they fall on weekdays: Monday, March 2, 2026 through Friday, June 19, 2026 returns 80. To skip holidays, list their dates in a range and add it as the third argument, for example =NETWORKDAYS(A2,B2,E2:E10). A listed holiday that lands on a weekend should not be subtracted twice, since that day is already left out.
NETWORKDAYS.INTL: when the weekend isn’t Saturday and Sunday
NETWORKDAYS.INTL adds a weekend argument before the holidays: NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]). You can pass a number or a seven-character text string.
- Numbers 1 to 7 set two-day weekends: 1 is Saturday and Sunday (the default), 2 is Sunday and Monday, and so on up to 7 for Friday and Saturday.
- Numbers 11 to 17 set one-day weekends: 11 is Sunday only, 12 is Monday only, up to 17 for Saturday only.
- A string of seven 0s and 1s runs Monday through Sunday, where 1 marks a day off.
"0000011"is a normal Saturday and Sunday weekend, and"0000110"gives Friday and Saturday off. Keep the string in quotation marks.
DAYS360: for finance work only
DAYS360(start_date, end_date, [method]) counts days as if every month had 30 days and every year had 360. It is meant for fixed-income and interest calculations. Leave the method out (or use 0) for the US convention, or use another value such as 1 for the European convention. For ordinary schedules, stick with subtraction or DAYS.
Subtract days, months or years from one date
Sometimes the goal is a new date rather than a gap, such as a reminder two weeks before a deadline.
- Days: subtract a plain number.
=B2-14returns June 5, 2026 when B2 holds June 19, 2026. Keep the cell formatted as a date. - Months: use
EDATE(start_date, months)with a negative number.=EDATE(B2,-1)returns May 19, 2026. Decimal months are cut off, so-2.6acts like-2. - Years: rebuild the date with
DATE, for example=DATE(YEAR(B2)-1,MONTH(B2),DAY(B2))for one year earlier.
Subtracting 30 is not the same as going back one month. Thirty days before June 19, 2026 is May 20, while EDATE lands on May 19. Use a day count when a rule says “30 days” and EDATE when it says “one month.”
Count the days since or until today
TODAY() takes no arguments and returns the current date without a time. Use it as either side of a subtraction:
=TODAY()-A2gives days elapsed since the date in A2.=B2-TODAY()gives days remaining until the date in B2. A negative result means the date has passed.=DATEDIF(A2,TODAY(),"Y")gives whole years since A2, which is handy for ages or anniversaries.
TODAY is a volatile function. It shows the date of the last recalculation, so the answer changes on its own from one day to the next, and many TODAY formulas can slow a large sheet. If you need a fixed date that never moves, press Ctrl + ; (or ⌘ + ; on a Mac) to type today’s date into a cell as a static value, then subtract from that cell.
Worked example: a small project tracker
Here is a fictional sheet with three team members. Column B holds start dates and column C holds end dates. The formulas in row 2 were filled down to rows 3 and 4.
| A: Name | B: Start | C: End | D: =C2-B2 | E: =DATEDIF(B2,C2,"M") | F: =NETWORKDAYS(B2,C2) |
|---|---|---|---|---|---|
| Alex | 3/2/2026 | 6/19/2026 | 109 | 3 | 80 |
| Jordan | 1/15/2026 | 2/27/2026 | 43 | 1 | 32 |
| Priya | 10/16/2023 | 11/18/2026 | 1129 | 37 | 808 |
A few things to notice. Jordan’s project runs 43 calendar days but only counts as 1 whole month, because a second month would not be complete until March 15. Alex’s 80 working days include both the first Monday and the last Friday. If May 25, 2026 is a company holiday and that date sits in cell H2, =NETWORKDAYS(B2,C2,$H$2) drops Alex’s count to 79. The dollar signs keep the holiday reference fixed as the formula is copied down.
To subtract a whole column in one step instead of filling down, type =ARRAYFORMULA(C2:C4-B2:B4) in D2. Pressing Ctrl + Shift + Enter (⌘ + Shift + Enter on a Mac) while editing a formula adds the ARRAYFORMULA( wrapper for you. Leave the cells below D2 empty so the results have room to spill.
Fixing wrong or strange results
The answer looks like a date from 1900
The result cell picked up date formatting from the cells it refers to, so 109 days is drawn as April 18, 1900. Select the cell and choose Format > Number > Number. The full list of options is on the number formatting help page.
#VALUE! or a date that won’t subtract
At least one cell probably holds text that looks like a date, which is common after pasting or importing data. Signs include left alignment in the cell. Test it with =ISNUMBER(A2): a real date returns TRUE because it is a number underneath. If it returns FALSE, retype the date, or convert it with =DATEVALUE(A2) and format the result as a date. DATEVALUE only accepts text, and recognized formats depend on the spreadsheet’s locale.
A typed date inside a function gives a tiny or odd number
Inside a function, 6/19/2026 without quotes is read as division, not a date. Use DATE(2026,6,19) or a cell reference instead. =DATE(2026,6,19)-DATE(2026,3,2) returns 109.
The result is negative or off by one
A negative number means the earlier date came first. Swap the references, or wrap the formula in ABS() if you only care about the size of the gap. DATEDIF does not return negative numbers; if the start date is later than the end date, expect an error instead, so check the order there first. If a count is one short, remember that subtraction leaves out the start day, while NETWORKDAYS counts both ends.
Day and month are swapped
A date such as 3/2/2026 means March 2 in a US locale but February 3 in many other regions. Open File > Settings, check the Locale menu under General, and click Save settings after any change. Typing dates as 2026-03-02 avoids the confusion.
How to check the result and undo a mistake
- Test with a pair you can count in your head, such as 1/1/2026 and 1/8/2026, which should return 7.
- Compare two methods side by side.
=C2-B2and=DAYS(C2,B2)should always match. - Click the result cell and read the formula bar to confirm which cells the formula points to.
- Press Ctrl + Z (⌘ + Z on a Mac) to undo a formula or format change you didn’t mean to make.
Questions people ask about date math in Sheets
Can I subtract dates that are on different tabs?
Yes. Put the tab name and an exclamation point before the cell, as in =Schedule!B2-Sheet1!A2. If a tab name has spaces, wrap it in single quotes: ='Q3 plan'!B2-A2.
How do I get the difference in weeks?
Divide the day count by 7. =(C2-B2)/7 returns about 15.57 for Alex’s project, and =INT((C2-B2)/7) returns 15 whole weeks.
Are leap years handled automatically?
Yes. February 29 is simply one more serial number, so subtraction and DAYS both include it. From 2/1/2028 to 3/1/2028, for example, either method returns 29.
What if my cells hold a date and a time?
The time is stored as a fraction of a day, so subtraction returns a decimal such as 2.5 for two and a half days. Wrap it in INT() for whole days, or follow the approach in the guide to subtracting time in Google Sheets to show hours and minutes.
Does this work the same way in Excel?
Mostly. The minus sign, DAYS, DATEDIF and NETWORKDAYS all exist in Excel, but the MINUS function does not, and menu paths differ. The Excel version of these steps is in how to subtract dates in Excel.
Start with =B2-A2 on your own data, check one row by hand, and then switch to DATEDIF or NETWORKDAYS only if you need months, years or business days.