Difference in an Excel Formula: Numbers, Dates, Percent
The difference in an Excel formula means three different jobs — numbers, percentages, dates — worked through one example each, and the check each one needs.
"Different in Excel formula" almost always means one of three separate jobs: the plain gap between two numbers, a percentage change between them, or the span between two dates. People reach for the same mental model — subtract one cell from another — and it works for the first job, silently misleads on the second, and fails outright on the third. Each one gets a worked example below, and the check it needs before the result goes into a report.
The plain number difference
This one is exactly what it looks like. If B2 holds this month’s figure and A2 holds last month’s, =B2-A2 returns the difference, positive if B2 is larger, negative if it fell. The only real decision is whether a negative number should read as a fall or as an error, which is a formatting choice, not a formula one — a custom number format or a wrapping IF can show a negative gap in red or with a down arrow instead of a bare minus sign.
Percentage difference — where the mental model breaks
Percentage difference is not "subtract, then move the decimal." The formula is =(B2-A2)/A2, formatted as a percentage — the new value minus the old, divided by the old. Divide by the wrong side and every number in the column reads backwards: a rise reads as a fall of the same size only when the swing is small, and diverges sharply once it is not.
The formula breaks outright when A2 is zero — division by zero returns #DIV/0!, which at least fails loudly. The quieter problem is a negative A2: the formula still returns a number, and that number has the wrong sign for what most readers expect "percentage change" to mean. Wrapping the denominator in ABS(A2) fixes the sign but changes what the figure means, so decide which behaviour the report actually needs before applying it, rather than after someone questions a number that looks wrong.
Date difference — the one that needs a different function entirely
Subtracting one date from another (=B2-A2, where both cells hold dates) does return a correct count of days, because Excel stores a date as a serial number and the subtraction is genuinely just arithmetic on two integers. Where it falls short is months and years: there is no clean way to subtract two dates and get "14 months" out of a plain minus sign, because calendar months are not a fixed length.
That is what DATEDIF is for: =DATEDIF(A2,B2,"d") for days, "m" for complete months, "y" for complete years. It is a real, working function, just one Excel’s own formula autocomplete does not suggest as you type it — it was left out of the function picker years ago and never added back, which is why plenty of long-time Excel users have never typed it on purpose. One unit is worth avoiding: "md" (days ignoring months and years) has documented edge-case bugs around month-end dates and can return a negative or simply wrong number. Stick to "d", "m" and "y" and compose them if you need "1 year, 2 months, 3 days" style output.
A worked example, side by side
- Plain difference: A2 = 480, B2 = 552 → =B2-A2 returns 72.
- Percentage difference: same cells → =(B2-A2)/A2 returns 0.15, formatted as 15%.
- Date difference: A2 = 2026-01-10, B2 = 2026-10-23 → =DATEDIF(A2,B2,"m") returns 9 complete months; =DATEDIF(A2,B2,"d") returns 286 days.
All three start from the same two-cell layout and the same instinct to subtract. Which formula is correct depends entirely on what the number is for, not on what the data looks like — a spreadsheet cannot tell you that from the cells alone.
Where AI actually helps, and where it does not
Naming the job precisely is the part AI tools are genuinely good at: "write a formula for the percentage difference between A2 and B2, where B2 is the newer value" comes back correct far more often than "show me the difference in Excel," because the second phrasing leaves the three jobs above completely unresolved. OpenAI’s and Anthropic’s own prompting guidance both make the same point for any task, not just formulas: state the exact inputs and what "correct" means, rather than describing the goal in general terms and hoping the gap gets filled in correctly.
Pasting in the actual two cells you are working from — a real number, a real date, not a description of the column — narrows a drafted formula down to the right one noticeably faster than describing the data from memory. What a drafting tool will not do is notice that A2 is sometimes zero three rows down, or that half the date column is stored as text rather than an actual date. A widely cited survey of hallucination in large language models covers why a model can describe a formula as correct with complete confidence without ever having run it against your actual sheet — a drafted formula and a verified one are not the same claim, however fluent the explanation sounds.
Ask AI to write the difference formula. Let the formula run against your real cells. Never trust a result you have not tested on a row where you already know the right answer.
Checks before the number goes anywhere
- For a percentage difference, confirm which cell is the "old" value before deciding the denominator — swapping it flips every sign in the column.
- Check the denominator column for a zero or a negative value before trusting a percentage formula at scale; either one changes what the result means.
- For dates, confirm both cells are genuine dates rather than text that merely looks like one — DATEDIF on a text string returns an error rather than a silently wrong number, which is the easier failure to catch.
- Test any formula on one row where you already know the answer by hand before copying it down a column of hundreds.
- Avoid DATEDIF’s "md" unit; use "d", "m" and "y" and build up from those instead.
Deciding in advance what a formula should do with a zero, a negative number or a text value — rather than discovering the gap once a wrong figure has already gone into a report — is the same habit behind NIST’s AI Risk Management Framework, scaled down here to one cell instead of a deployed system.
What to do Monday
Find the one report in regular use that shows a percentage change, and check which cell its formula actually divides by — it is the single most common place this goes quietly wrong. Excel Lookup Formula covers the other everyday formula choice, VLOOKUP versus XLOOKUP versus INDEX/MATCH, with the same worked-example structure. Excel Errors is worth reading alongside this one for what #DIV/0! and the other six codes are actually telling you, rather than just that something broke.
If the difference needs to run automatically across a whole workbook rather than sit in one column you check by hand, Code for Excel covers which of VBA, Office Scripts or Python actually fits that job. Excel Advanced Functions goes further into letting AI draft the formula and you test it, which is the same discipline this article applies to just three functions. Naming the exact inputs and the exact definition of "correct" before you ask — covered in full in How to Write a Prompt That Works on the First Try — is most of the gap between a formula that is right and one that merely looks right.
Coursium teaches that same habit — naming the job precisely, then checking the result before it goes anywhere. Stay ahead of AI by learning the tools on your phone.