# How to fix #VALUE! when subtracting dates in Excel

The dates are text in day/month/year order. Rebuild them with DATE: =C2-DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2)).

**Excel:** `=C2-DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Booking | Booked (text) | Arrival | Days ahead |
| BK-1 | 15/09/2026 | 2026-10-02 |  |
| BK-2 | 03/09/2026 | 2026-09-20 |  |
| BK-3 | 28/08/2026 | 2026-10-15 |  |
| BK-4 | 07/10/2026 | 2026-10-30 |  |
| BK-5 | 21/09/2026 | 2026-09-25 |  |

Formula in D2, filled to D6: `=C2-DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2))`

Result: 17; 17; 48; 23; 4

## How it works

The booking dates came from a system that writes day/month/year, and they are stored as text. A workbook set to US dates can't read "15/09/2026" (there is no month 15), so the subtraction returns #VALUE!. Worse, "03/09/2026" is read as March 9 and gives a wrong answer with no error. DATE builds the real date from the year, month and day pieces, whatever the computer's settings.

## Other ways

- `=C2-DATEVALUE(MID(B2,4,2)&"/"&LEFT(B2,2)&"/"&RIGHT(B2,4))`: Rearranges the text into month/day/year for DATEVALUE. This one depends on the computer's date settings.
- `=DAYS(C2,DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2)))`: DAYS(end, start) gives the same count and reads clearly.

## Common mistakes

- Day/month text with a day of 12 or less converts silently to the wrong date. Fix the whole column, not just the rows that error.
- For a permanent fix, select the column and use Data, Text to Columns, with the column type Date: DMY.
- Check with `=ISTEXT(B2)`: real dates are numbers, not text.

## Questions

**What if the dates use dots, like 15.09.2026?**

The characters sit in the same positions, so the same formula works.

**What if the dates include a time?**

The difference includes a fraction of a day. Wrap it in INT for whole days.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/value-error-when-subtracting-dates