How to subtract dates in excel

Answer

To subtract dates in Excel, place the later date in one cell and the earlier date in another cell, then subtract the earlier from the later (later − earlier). For example, if A2 contains the start date and B2 contains the end date, enter =B2-A2 in C2 to get the number of days between them.

Quick rules

1. Excel stores dates as serial numbers; subtracting two dates returns the difference in days.

2. Use cell formatting to display the result as a number, or convert the difference into years, months, or workdays with functions.

3. Confirm the workbook’s date system, because Excel can use the 1900 or 1904 date system; the 1904 system shifts serial numbers by 1,462 days.

What formulas give the difference in days, months, years, or workdays?

To get days: enter =EndDate - StartDate.
To get days using a function: use =DAYS(EndDate, StartDate).
To get full years, months, or days between two dates: use =DATEDIF(StartDate, EndDate, "unit").
To get workdays excluding weekends: use =NETWORKDAYS(StartDate, EndDate, [holidays]).
To get workdays with custom weekend rules: use =NETWORKDAYS.INTL(StartDate, EndDate, [weekend], [holidays]).

Concrete Excel examples (use these directly in cells)

1. Days difference (simple):

   1. =B2-A2

2. Days difference (function):

   1. =DAYS(B2, A2)

3. Years, months, days using DATEDIF:

   1. Years: =DATEDIF(A2, B2, "Y")

   2. Months ignoring years: =DATEDIF(A2, B2, "YM")

   3. Days ignoring months and years: =DATEDIF(A2, B2, "MD")

4. Total months:

   1. =DATEDIF(A2, B2, "M")

5. Workdays excluding weekends and a holiday list in E2:E5:

   1. =NETWORKDAYS(A2, B2, E2:E5)

6. Workdays with custom weekend pattern (Saturday only weekend code = 7):

   1. =NETWORKDAYS.INTL(A2, B2, 7, E2:E5)

Mathematical expression (raw LaTeX)

$\text{Hours}=(\text{EndDateTime}-\text{StartDateTime})\times 24$

How to show the result as years, months, and days in a readable sentence

To create a human-readable result like “X years, Y months, Z days,” use DATEDIF parts and concatenate them. Example formula:

1. =DATEDIF(A2,B2,"Y") & " years, " & DATEDIF(A2,B2,"YM") & " months, " & DATEDIF(A2,B2,"MD") & " days"

How to handle time with dates (date & time subtraction)

To get elapsed days, including fractional days, from times, subtract the earlier datetime from the later datetime and format the result as a number or custom time:

1. =B2 - A2
2. To express the result as total hours: =(B2 - A2) * 24
3. To express the result as total minutes: =(B2 - A2) * 24 * 60

Mathematical expressions (raw LaTeX)

         $\text{Hours}=(\text{EndDateTime}-\text{StartDateTime})\times 24$

What common pitfalls should I avoid?

1.      Date stored as text: convert with DATEVALUE or use Text to Columns.

2.      Wrong cell format: format result cell as General or Number to see days.

3.      1904 date system shift: check File → Options → Advanced → When calculating this workbook.

4.      DATEDIF quirks: DATEDIF is undocumented in some Excel versions; the function works but may not appear in function lists.

5.      Negative results: ensure the end date is later than the start date, or wrap with ABS() if you want absolute difference.

Are there differences between Windows and Mac Excel?

Yes.

1.      The 1904 date system is more common as a default in older Mac workbooks; check and switch if dates appear shifted.

2.      Function names and behaviors remain the same across platforms; DATEDIF, DAYS, NETWORKDAYS, and NETWORKDAYS.INTL work on both platforms.

3.      Keyboard shortcuts and menu locations can differ between Windows and Mac.

How to convert text that looks like a date into a real date?

1.      Use =DATEVALUE(textDate) to convert a text date to a serial number, then format the cell as a Date.

2.      Use Text to Columns: select column → Data tab → Text to Columns → Delimited/Fixed width → set date format → Finish.

3.      Use =DATE(year, month, day) when you can extract parts with LEFT, MID, RIGHT, or VALUE.

How to show only business days between dates, excluding specified holidays

Use NETWORKDAYS(StartDate, EndDate, HolidaysRange). Place holiday dates in a range, for example E2:E10, then:

1.      =NETWORKDAYS(A2, B2, E2:E10)

Summary of recommended formulas

1.      =B2 - A2 — basic days difference.

2.      =DAYS(B2, A2) — days using built-in function.

3.      =DATEDIF(A2, B2, "Y") / "M" / "D" / "YM" / "MD" — component differences.

4.      =NETWORKDAYS(A2, B2, Holidays) — business days.

5.      =NETWORKDAYS.INTL(A2, B2, WeekendCode, Holidays) — business days with custom weekends.

Where to check authoritative guidance

Consult your Excel help or Microsoft’s official documentation for DATEDIF, DAYS, NETWORKDAYS, and NETWORKDAYS.INTL for edge cases and locale-specific date parsing rules.

 

$\text{Hour}$

$\text{s}$

Yes · 0 No · 0 Add Comment · 0

Add ·

Report