Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the Wrong date error
Excel & Google SheetsWhat Wrong date means
Date arithmetic relies on serial numbers: subtracting two dates gives the number of days between them. Wrong results occur when one or both values are text strings masquerading as dates, when time-of-day components add fractional days, or when DATEDIF arguments are in the wrong order.
Common causes
- One or both dates are stored as text strings, so the subtraction operates on text rather than serial numbers and returns #VALUE! or a wrong number.
- Cells contain date-time values (serial numbers with a fractional time component), so subtraction returns a decimal (e.g., 29.75 days) instead of a whole number.
- The start date and end date arguments to DATEDIF are accidentally reversed — DATEDIF requires start_date < end_date or it returns #NUM!.
- The wrong DATEDIF unit is used: 'M' counts complete months, 'D' counts total days, 'YM' counts months ignoring years — mixing these up gives unexpected results.
- A date was manually typed in a text format (e.g., 'Jan 2024' without a day) that the formula engine cannot parse, causing it to fall back to a text comparison.
Example fix
Broken
=DATEDIF(B2,A2,"D")Fixed
=DATEDIF(A2,B2,"D")DATEDIF requires the start date as the first argument and the end date as the second. If they are reversed and end_date < start_date, DATEDIF returns #NUM!. Swapping the arguments so start_date comes first fixes it.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Verify both cells contain real date serial numbers: =ISNUMBER(A2) should return TRUE. If it returns FALSE, the value is text — use DATEVALUE() to convert it first.
- 2For a simple day count between two dates: =end_date - start_date. Make sure the result cell is formatted as Number, not Date (otherwise the result shows as a date serial).
- 3If the values include a time component, round down to the date only: =INT(end_date) - INT(start_date). INT strips the fractional time portion.
- 4For DATEDIF: confirm the argument order is =DATEDIF(start_date, end_date, unit) with start_date <= end_date. Swap if necessary or use ABS(end-start) for a simple day count.
- 5For month and year differences, use DATEDIF with the correct unit: 'Y' for complete years, 'M' for complete months, 'D' for complete days.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every Wrong date and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
Why does my date subtraction return a decimal like 30.5 instead of 30?
One or both cells contain a date-time value — the integer part is the date, the fractional part is the time. Use =INT(end_date) - INT(start_date) to subtract dates only, or =DAYS(end_date, start_date) which works on the integer date portion.
What is the difference between DATEDIF(A2,B2,"M") and DATEDIF(A2,B2,"YM")?
"M" returns the total number of complete months between the two dates across all years (e.g., 14 months). "YM" returns the number of complete months in the partial year only, ignoring the full years (e.g., 2 months of the extra partial year). Use "YM" only when constructing a '2 years, 2 months' display.
DAYS() vs. subtraction — which should I use?
=DAYS(end_date, start_date) and =end_date - start_date give the same result when both cells contain real date serial numbers. DAYS() is slightly more readable and ignores time components in Google Sheets. Use simple subtraction when both values are confirmed date serials.