Excel Date Subtraction #VALUE! Error: Causes and Fixes

A #VALUE! error when subtracting dates in Excel usually means that at least one cell is text Excel cannot use as a date or number. If the start date is in A2 and the end date is in B2, use =B2-A2; if that still returns #VALUE!, check whether both cells contain real Excel dates rather than text that only looks like a date.

What the #VALUE! error means

Excel does not subtract calendar labels as words. It stores a valid date as a serial number and subtracts the underlying numbers. For example, if A2 contains March 1, 2025 and B2 contains March 8, 2025 as genuine dates, subtracting the earlier date from the later one gives 7 days. The displayed date format does not change that arithmetic.

The #VALUE! error generally appears when Excel encounters text where it expects a numeric value. A cell might contain a date typed or imported as text, a hidden leading or trailing space, an apostrophe, or a formula that returns text. One text value is enough to make a direct subtraction fail. The dates can look identical on screen, so appearance alone is not a reliable test.

Check the order of subtraction as well. Subtracting a later date from an earlier one can produce a negative number, but that is not normally a #VALUE! error. Depending on the cell’s date format and workbook date system, Excel may show hash marks or an unexpected display for negative date results. That is a different issue from text preventing the calculation.

Check whether the cells contain real dates

Select each date cell and look at the formula bar. A date entered as a valid Excel date will generally appear as a date there, while text may retain an apostrophe or other characters. This clue is not definitive, because formatting and regional settings affect how values appear. A more useful test is to ask Excel whether the contents are numeric.

With the suspected start date in A2, enter =ISNUMBER(A2) in an empty cell. For a genuine Excel date, the result is TRUE; for a text date, it is FALSE. Repeat the test for B2. Excel dates and times are numeric values, so TRUE indicates that the cell is a number, although it does not by itself prove that the number represents the intended date.

Also look for small green warning indicators and use the error-checking options if they appear. A cell formatted as Text before entry can preserve a typed date as text. Pasting from a website, a CSV file, an accounting system, or another spreadsheet can also bring in text values, spaces, or inconsistent date formats.

Convert text dates into Excel dates

For a small range, one option is to select the text-date cells and use Data > Text to Columns. In the wizard, choose the appropriate delimiter settings for the data, then select Date and the order that matches the source, such as MDY or DMY, before finishing. The date order matters: 04/05/2025 means April 5 in MDY but 4 May in DMY. Choose based on the source data, not on a guess.

If Excel already recognizes a consistent text date format, selecting the cells and changing the number format may appear to fix the display, but number formatting alone does not convert text into numbers. After conversion, repeat the ISNUMBER check or try the subtraction again. On Excel for Mac, Text to Columns is also available from the Data tab, though the wizard’s appearance can differ slightly. In Excel for the web, available import and conversion controls can differ from the desktop app; opening the workbook in Excel for Microsoft 365 on Windows is a practical option if the desktop wizard is needed.

A formula can convert a text date when its format is understood by Excel’s regional settings. With a recognizable text date in A2, use =DATEVALUE(TRIM(A2)). If A2 contains 3/1/2025 and Excel interprets that input as March 1, the formula returns the serial value for March 1, which you can display as a date by applying a date number format. TRIM removes ordinary extra spaces at the start or end of text, but DATEVALUE cannot reliably interpret every format or every kind of invisible character.

For a full column, fill the conversion formula down, inspect the results, and then use the converted values in the subtraction. If the source dates are ambiguous or use a format Excel does not recognize, do not rely on automatic conversion. Confirm the date order with the source system and use Text to Columns with the matching order, or rebuild the date from its year, month, and day components.

Fix spaces, apostrophes, and imported characters

Some text dates contain more than a normal space. Data copied from web pages or exported systems may contain a nonbreaking space, which TRIM does not remove, or a hidden character such as a line break. An apostrophe entered before a value can also force it to be treated as text. Inspect the formula bar and compare a failing cell with one that works. If the source is a pasted dataset, reimporting or cleaning the source can be safer than trying to repair one symptom at a time.

For ordinary leading and trailing spaces, the DATEVALUE and TRIM combination may be enough. With text in A2, =DATEVALUE(TRIM(A2)) returns a numeric date when Excel recognizes the cleaned text. If the cell includes extra labels or characters, first isolate the date portion or remove the unwanted characters using a suitable cleanup step. Do not delete punctuation blindly: slashes and hyphens may be necessary parts of the date.

When a whole column comes from an external source, check several rows before applying a bulk fix. A column can mix actual dates, text dates in different orders, blanks, and error values. One conversion method may handle some rows but misread others. Preserve a copy of the original data until the converted dates have been checked against the source.

Check locale, date order, and two-digit years

Excel’s interpretation of typed or imported date text can depend on regional settings. In one locale, 7/8/2025 may mean July 8; in another, it may mean 7 August. A string that looks valid to a person can therefore be interpreted incorrectly or remain text. For dates exchanged between systems, a clear, consistent format and an explicit import date order reduce this risk.

Four-digit years are safer than two-digit years because they avoid uncertainty about which century is intended. If a file mixes formats such as 2025-03-08 and 08/03/25, standardize the source or use a deliberate import setting before calculating. DATEVALUE follows Excel’s date interpretation rules; it does not know the author’s intended day and month when the text is ambiguous.

Excel workbooks can also use different date systems, notably the 1900 and 1904 systems. This can affect the serial numbers associated with dates when workbooks move between systems, though it is not the usual cause of a #VALUE! result in a simple subtraction. If dates shift by a consistent number of days after copying a sheet or moving data between workbooks, check the workbook date system and the import process rather than treating the issue as text conversion.

Distinguish #VALUE! from other date problems

Not every incorrect-looking result is a #VALUE! problem. If the formula returns a number such as 7 but the cell displays a date, change the result cell’s number format to General or Number to see the day count. Use Home > Number Format to select a suitable format, or choose a number format from the Number group on the Home tab. The subtraction result is a number of days, so a date display format may make it look confusing.

If the result shows hash marks, the column may be too narrow for the formatted value, or the result may be a negative date that the current date system cannot display in that format. Widen the column or use General or Number to inspect the result. If Excel returns #NAME? or a formula appears as literal text, investigate a misspelled function, missing equals sign, or Text-formatted formula cell instead.

When a calculation produces a negative duration intentionally, decide how it should be represented. A numeric result can show the signed number of days, while a date-style format may not display it as expected. For elapsed time, confirm the intended subtraction order and consider displaying the result as a number rather than as a calendar date.

Prevent date subtraction errors in future files

Consistent input and a quick validation step make date calculations more dependable. When setting up a worksheet, format the destination cells as dates before entering data, use four-digit years, and avoid mixing date conventions within the same column. Formatting cells before typing does not repair text that has already been entered, but it can help Excel interpret subsequent entries as dates.

Use one documented date order throughout an imported or shared dataset.
Check a few rows from the top, middle, and bottom of a pasted column for text, blanks, or mixed formats.
Test suspicious values with ISNUMBER before building calculations around them.
Keep the original imported column until conversion results have been checked.
Use a clear subtraction order: end date minus start date for a positive elapsed-day count.

For recurring imports, standardize dates at the source or use a repeatable import and conversion process. That is more reliable than manually correcting dates whenever a report is refreshed. If coworkers use different regional settings, agree on an unambiguous source format and confirm the import settings rather than assuming that everyone’s Excel interprets the same text identically.

Frequently Asked Questions

Why does Excel show #VALUE! when both cells look like dates?
At least one cell may contain text, a hidden space, an apostrophe, or a formula result stored as text. Check each cell with ISNUMBER and convert text dates using a method that matches their actual date order.

Does changing the cell format convert text to a date?
No. A number format changes how a numeric value is displayed; it does not generally turn text into a numeric date. Convert the value first, for example with Text to Columns or DATEVALUE where the text is recognizable, then apply a date format if needed.

Why does subtracting dates return a number instead of a date?
The result is the number of days between the two dates. For example, March 8 minus March 1 returns 7. A result cell formatted as General or Number displays that day count directly.

Why do I see hash marks instead of a negative date result?
The result may be negative, or the column may be too narrow. Widen the column and change the result format to Number or General to inspect the value. A negative result often means the dates were subtracted in the opposite order from the one intended.

Can DATEVALUE fix every text date?
No. It works when Excel can interpret the text using the current regional settings. Ambiguous dates, unusual separators, extra characters, or nonbreaking spaces may require a deliberate import setting or additional cleanup.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.