Dates in Excel look like dates, but underneath the surface they are just numbers. January 1, 1900 is the number 1. May 14, 2026 is the number 46,156. When you subtract one cell from another, Excel does not perform any calendar magic. It just subtracts those underlying serial numbers, then tries to guess how to display the result.
That guess is where most users get burned. You enter =B2-A2, expecting to see something like 14 or 30. Instead Excel hands back a date in 1900, or worse, a string of pound signs. The math is correct. The format is not. Once you understand that gap, every other date-subtraction problem becomes solvable.
This guide walks through every realistic scenario: days between two dates, full months, complete years, working days only, fractional years, and time-based subtraction down to the second. We will cover the core date formulas behind each method and show what to do when the result looks wrong.
The fastest way to find the number of days between two dates is plain subtraction. Put the later date in B2, the earlier date in A2, and type =B2-A2 into C2. If A2 holds 01/01/2026 and B2 holds 05/14/2026, the answer is 133. Done.
One catch. If C2 displays something odd like 5/13/1900 instead of 133, the cell is formatted as a date. Select the cell, press Ctrl+1, and pick General or Number from the format dialog. The result switches to the integer you expected.
Direct subtraction is brutally efficient. No function calls. No volatile recalculation. It scales to a million rows without slowdown. Use it whenever you only need raw days and you trust both inputs are real dates rather than text.
Right-align is a tell. Excel right-aligns numbers and dates by default, and left-aligns text. If a date sits on the left side of a cell, it is text masquerading as a date, and subtraction will return #VALUE!. Wrap the inputs in DATEVALUE or fix the source data before you try to subtract.
Days are easy. Months are trickier because months are not all the same length. February has 28 days. March has 31. Saying "three months between these dates" is more of a calendar question than a math question.
The function you want here is DATEDIF. It is the hidden tool inside Excel that handles calendar-aware differences. Syntax is =DATEDIF(start_date, end_date, unit) where unit is a code in quotes. Use "M" for complete months.
If A2 is 01/15/2026 and B2 is 04/20/2026, =DATEDIF(A2, B2, "M") returns 3. It is counting whole months that have fully elapsed. From the 15th of January to the 20th of April, three full months have passed. The fourth has started but not finished.
Switch the unit to "YM" and you get months remaining after removing the years. Use "MD" and you get days remaining after removing the months. Combine them and you build a human-readable age string like "33 years, 4 months, and 12 days."
DATEDIF does not appear in Excel's function autocomplete. Microsoft inherited it from Lotus 1-2-3 decades ago and never officially documented it. The function still works in every version of Excel, including Excel 365, but you have to type it from memory. There is no helper dialog. If you forget the unit codes, the formula returns #NUM! with no hint why.
For complete years, use =DATEDIF(A2, B2, "Y"). The function returns the number of full years between the two dates, exactly the way you would calculate someone's age on a birthday.
For a decimal answer, switch to YEARFRAC. Syntax is =YEARFRAC(start_date, end_date, basis). The basis argument controls how Excel counts days in a year. Basis 1 uses actual days. Basis 0, the default, uses a 30/360 banking convention. For most general calculations basis 1 is the safer choice because it reflects how a real calendar works.
YEARFRAC is the go-to for financial models, employment tenure, and prorated billing. Need to bill a client for 18 months of service in years? =YEARFRAC(start, end, 1) returns 1.5, and you multiply by your annual rate. The p-value approach for statistical analysis takes a different math route, but YEARFRAC is the right hammer for time-based proration.
DATEDIF does not appear in Excel's function list and is undocumented by Microsoft, yet it still works in every modern version. You have to type it from memory because there is no autocomplete help. Worth memorizing — it solves problems no other function can. The function survives because too many financial and HR systems depend on it. Microsoft has tried twice to deprecate it and reversed course both times when enterprise customers pushed back.
Use =B2-A2 for raw days between two dates. Fastest method, scales to millions of rows. Format result cell as General or Number. Best choice when you only need integer days and you trust your inputs.
Calendar-aware differences in Y, M, D, YM, YD, or MD units. Handles month and year lengths correctly. The undocumented workhorse for ages, tenure, and any human-readable time span.
Counts only business days between two dates. Optional holiday range skips company holidays too. Use NETWORKDAYS.INTL when your weekend is not Saturday and Sunday.
Decimal years between dates. Critical for financial calculations, employment tenure, and proration. Use basis 1 for actual-day counting, basis 0 for 30/360 banking convention.
Calendar days are not the same as business days. If you need to know how many workdays sit between two dates, NETWORKDAYS is the function. Syntax is =NETWORKDAYS(start_date, end_date, [holidays]).
Say A2 is 05/04/2026, B2 is 05/14/2026. Direct subtraction gives you 10. But two of those days are a weekend. NETWORKDAYS returns 9 because it counts the start date, the end date, and every weekday in between, while skipping Saturdays and Sundays.
The optional third argument accepts a range of holiday dates. Build a list of company holidays in column E, then use =NETWORKDAYS(A2, B2, E2:E20). Any of those holidays that fall on a weekday inside the range get excluded too. This is how HR systems calculate PTO accrual and how project managers plan deliverables that respect the office calendar.
In some industries, or some countries, the weekend is not Saturday and Sunday. Retail managers might have Tuesday and Wednesday off. The Middle East often uses Friday and Saturday. NETWORKDAYS.INTL takes a fourth argument that lets you define which days are weekend days. Pass 11 to mean Sunday-only weekends, or use a seven-character text string of 1s and 0s where 1 marks a weekend day.
Times are stored the same way dates are, just as fractions instead of whole numbers. Noon is 0.5 because it is halfway through a day. 6:00 AM is 0.25. When you subtract two time values, Excel hands back a fraction that can be formatted as elapsed time.
Type a start time in A2 and an end time in B2. The formula =B2-A2 gives the elapsed time. Format the result with the custom format [h]:mm:ss. The square brackets matter. Without them, Excel rolls over after 24 hours and your 30-hour total displays as 6:00. With the brackets, it shows 30:00 properly.
Need just the difference in hours as a number? Multiply the result by 24. So =(B2-A2)*24 converts the fraction to hours. For minutes, multiply by 1440. For seconds, multiply by 86400. This is the standard trick for converting elapsed time to a number you can use in further calculations.
Type =B2-A2 where B2 is the later date. Format the result cell as General or Number if Excel insists on showing a date. Works on any size dataset with no performance penalty. This is the workhorse formula for everything from invoice aging to days-on-market reports.
Use =DATEDIF(A2, B2, "M") for complete elapsed months. Combine with "YM" and "MD" units to break out remaining months and days for a full age string. Note that DATEDIF requires the start date to be earlier — swap arguments if you get a #NUM! error.
For whole years use =DATEDIF(A2, B2, "Y"). For a decimal answer like 1.5 years, use =YEARFRAC(A2, B2, 1) where basis 1 counts actual calendar days. Pick DATEDIF for ages, pick YEARFRAC for any financial proration.
Use =NETWORKDAYS(A2, B2, E2:E20) where the third argument is your holiday list. For non-standard weekends, switch to NETWORKDAYS.INTL with a custom weekend code. Required for any deadline calculation that needs to respect the office calendar.
The #VALUE! error is the one you will see most often. It means at least one of your inputs is not a real date. Excel cannot do math on the text "March 15." Either retype the date in a format Excel recognizes, or wrap the input in DATEVALUE: =DATEVALUE(A2)-DATEVALUE(B2).
The #NUM! error from DATEDIF usually means the start date is later than the end date. The function refuses to count backwards. Either swap your arguments or wrap the whole thing in ABS to get an unsigned result.
The pound sign display, ######, is not an error. The column is just too narrow to display the date or number. Widen the column. Double-click the right border of the column header and it auto-fits to the widest value.
Negative-date displays in 1900 are a formatting issue, not a math one. Excel does not handle negative date values in date format. If your subtraction produces a negative number and the cell is formatted as a date, you get a useless result. Change the cell format to General or Number and the real value appears.
The reverse case comes up just as often. You have a date and you want to know what the date was 30 days ago, or what it will be 60 days from now. Just subtract or add the number directly. =A2-30 goes back 30 days. =A2+60 jumps forward 60 days.
For business days only, use WORKDAY. =WORKDAY(A2, -10) returns the date 10 working days before A2, skipping weekends. Add a holiday range as the third argument to skip those too. This is exactly how courts calculate filing deadlines and how compliance teams set notice periods.
Want to calculate someone's age in years, months, and days from a birth date? Combine three DATEDIF calls into a single formula:
=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months, " & DATEDIF(A2, TODAY(), "MD") & " days"
The first DATEDIF gives whole years. The second strips the years and counts remaining months. The third strips both and gives remaining days. Concatenate them with the ampersand operator and you get output like "47 years, 3 months, 18 days." The TODAY function updates every time the sheet recalculates, so the age stays current.
Date subtraction shows up in nearly every business spreadsheet. Accounts receivable teams calculate days outstanding on invoices. HR systems track tenure for vesting and PTO accrual. Project managers compute slack and lag between milestones. Real estate agents calculate days on market. Healthcare administrators measure length of stay.
In each case the underlying math is identical. The only thing that changes is whether you need raw days, business days, calendar months, or something more exotic. Pick the right function from the toolkit and the answer drops out.
If you are subtracting dates across thousands or millions of rows, function choice matters. Direct subtraction is fastest. DATEDIF is next. NETWORKDAYS and NETWORKDAYS.INTL are slower because they have to evaluate each calendar day inside the range. YEARFRAC sits in the middle.
For very large data, consider converting your date logic to a helper column once, then referencing the cached result instead of recalculating. Or push the work to Power Query, where date math runs once during the refresh rather than on every cell change. Power Query handles date subtraction with a single Duration.Days call after a subtraction step.
Often you do not just want the raw difference. You want a yes/no answer. "Is this invoice overdue?" "Has the warranty expired?" "Did the employee complete probation?" Wrap your subtraction inside an IF statement.
=IF(TODAY()-A2>30, "Overdue", "Current") flags any invoice dated more than 30 days ago. =IF(DATEDIF(A2, TODAY(), "Y")>=1, "Eligible", "Not yet") checks one-year tenure. Combine date math with conditional formatting and a status column lights up in red the moment a deadline passes.
One of the more frustrating sources of broken date subtraction is regional format mismatch. A spreadsheet built in the US with MM/DD/YYYY dates gets opened in the UK, where the system expects DD/MM/YYYY. Suddenly 03/05/2026 is interpreted as May 3 instead of March 5, and every date subtraction in the workbook drifts by months.
Excel handles this through the operating system's regional settings, not the workbook file. The dates themselves are stored as serial numbers, but the way they were originally typed depends on whatever locale Excel was using at input time. If you receive a file from a different region and the math looks wrong, check the underlying serial numbers by formatting a cell as Number. The integer tells you what date Excel actually thinks it has.
For shared workbooks, the safer practice is to enter dates using the DATE function: =DATE(2026, 5, 14). This forces the year-month-day order regardless of regional settings. Every date subtraction downstream then produces the same result no matter who opens the file.
Date subtraction works the same when the two dates live on separate sheets. Just reference each cell with its sheet name prefix: =Summary!B2-Details!A2. The exclamation mark separates the sheet from the cell. Excel performs the same subtraction whether the cells are on the same tab or across a workbook with twenty sheets.
If the dates live in a structured table, use table references instead. A formula like =Invoices[@DueDate]-Invoices[@IssueDate] reads cleaner than cell coordinates and survives row additions without breaking. This is the approach professional Excel modelers prefer because the formulas remain readable months later when someone else has to debug them.
Date subtraction in Excel is not one technique. It is a toolkit. Direct subtraction for raw days. DATEDIF for calendar-aware units. NETWORKDAYS for business days. YEARFRAC for decimals. WORKDAY for offsets. Custom time formats for hours, minutes, and seconds.
The trick is matching the function to the question. Once you start asking "what unit do I need, and does it care about weekends or holidays," the right formula becomes obvious. Practice with a few real scenarios and the muscle memory takes over. After that, date math is just another flavor of arithmetic.
The functions covered here have not changed in over a decade. DATEDIF still does not appear in autocomplete. NETWORKDAYS still skips weekends. YEARFRAC still defaults to the 30/360 banking basis if you forget to specify a basis. Knowing them by name and unit code pays dividends every time you open a workbook with date columns. Bookmark this page and come back to it whenever a date formula throws an error you cannot decode at a glance.