Share via

Date Calculation in Excel

Selina M 0 Reputation points
2026-03-15T22:32:41.2266667+00:00

I keep running into a problem with finding the difference between dates in a excel spreadsheet. The start date is 3/1/2026 to 3/15/2026.

I've tried the simple =startdate-enddate

And for some reason the end result is 1/0/1900, when I'm looking for 14.

I've also tried DATEDIF and adding "d" to the formula with no difference or a error message. Is there a solution of this?

Microsoft 365 and Office | Excel | For education | Windows
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Darren-Ng 10,375 Reputation points Microsoft External Staff Moderator
    2026-03-15T23:46:23.4166667+00:00

    Dear @Selina M,

    Thank you for posting your question in the Microsoft Q&A forum.

    Based on your description, you’re trying to calculate the number of days between two dates in Excel (3/1/2026 and 3/15/2026), but subtraction returns 1/0/1900 instead of 14.

    Here are some suggestions you can refer to:

    Use the right subtraction order and format the result as a number

    =EndDate - StartDate

    In case your start date is 3/1/2026 and end date is 3/15/2026

    Kindly note: If the answer cell is formatted as Date, Excel may try to display the numeric result as a date. Change the result cell format to General or Number: Home > Number format dropdown > General/Number.

    If DATEDIF errors: your “dates” are probably stored as text

    When dates are stored as text (often after copy/paste/import, or because the cell was formatted as Text), Excel formulas like subtraction or DATEDIF can return errors or unexpected results.

    For more information, you can refer to this article Convert dates stored as text to dates

    Correct DATEDIF

    If Start = A2 and End = B2:

    =DATEDIF(A2, B2, "d")

    User's image

    I hope information above meet your expectations and can help you resolve the issue. Feel free to reach out or leave the comment below if you have any other questions.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

  2. AI answer

    2026-03-15T22:32:56.2666667+00:00

    The result 1/0/1900 means Excel is treating the formula result as a date instead of a number.

    To get 14 as the difference between 3/1/2026 and 3/15/2026:

    1. Make sure both cells really contain dates, not text:
      • Widen the date column. If the dates are right‑aligned, they are true dates; if left‑aligned, they are text and must be converted.
    2. Use a subtraction formula with the earlier date subtracted from the later date, for example: 
         =B1 - A1
      where A1 = 3/1/2026 and B1 = 3/15/2026.
    3. Format the result cell as a Number, not a Date:
      • Select the result cell.
      • Press Ctrl+1.
      • Choose Number and set Decimal places as desired.

    This will display 14 instead of 1/0/1900.

    Alternatively, DATEDIF can be used if both cells are valid dates:

    =DATEDIF(A1,B1,"d")

    This returns the number of days between the two dates. If start_date is greater than end_date, DATEDIF returns #NUM!, so ensure the earlier date is the first argument.

    If a #VALUE! error appears, at least one of the “dates” is stored as text; convert it to a real date before using subtraction or DATEDIF.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.