The simplest way to find days between dates

To find how many days are between two dates in Excel, subtract the earlier date from the later date. If your start date is in cell A1 and your end date is in cell B1, type =B1-A1 into an empty cell. Excel will show the number of days as a whole number.

This works because Excel stores dates as numbers — January 1, 1900 is 1, January 2, 1900 is 2, and so on. When you subtract one date from another, you get the gap between them in days. The formula works whether your dates are typed directly into cells, entered as formulas, or pulled from other parts of your spreadsheet.

If the result shows a decimal (like 5.5) instead of a whole number, the cells contain times as well as dates. You can round the result with =ROUND(B1-A1,0) to show whole days only, or use =INT(B1-A1) to drop the decimal without rounding.

Key Takeaways

  • Subtracting an earlier date from a later date in Excel returns the number of days between them as a simple number.
  • The DATEDIF function calculates days, months, or years between dates and is useful when you need a specific unit rather than total days.
  • Wrapping a date subtraction in INT or ROUND removes decimals if your dates include time values.
  • When dates are entered as text instead of date values, Excel will not recognize them — you may need to convert them first using the DATEVALUE function.

Using DATEDIF for months and years

If you need the difference in months or years instead of days, use the DATEDIF function. Type =DATEDIF(A1,B1,"D") for days, =DATEDIF(A1,B1,"M") for complete months, or =DATEDIF(A1,B1,"Y") for complete years. The letter in quotes at the end tells Excel which unit to return.

DATEDIF counts only complete units. If your dates are January 15 and February 10, "M" returns 0 because a full month has not passed. The same dates with "D" return 26 days. This is different from simple subtraction, which would show the raw day count without regard to month boundaries.

DATEDIF also accepts "MD" (days ignoring months and years), "YM" (months ignoring years), and "YD" (days ignoring years). These are useful when you want to know, for example, how many days into the current month a date falls, or how many months and days remain in a year.

Handling dates entered as text

If your dates are stored as text instead of date values, subtraction and DATEDIF will not work. You can tell because the dates are left-aligned in their cells instead of right-aligned, or the formula returns an error like #VALUE!.

Convert text dates to real dates using the DATEVALUE function. If your text date is in A1, type =DATEVALUE(A1) in a new cell. Excel will convert it to a date value you can use in calculations. Then use that converted date in your subtraction or DATEDIF formula.

Alternatively, if you have many text dates to convert, select the column, go to the Data tab, and click Text to Columns. Click Next twice, then Finish — Excel will convert the entire column to dates in place. After that, your formulas will work normally.

Calculating business days only

To count only weekdays (Monday through Friday) between two dates, use the NETWORKDAYS function. Type =NETWORKDAYS(A1,B1) where A1 is the start date and B1 is the end date. The result excludes Saturdays and Sundays automatically.

If your workplace observes holidays on specific dates, add them as a third argument. Type =NETWORKDAYS(A1,B1,C1:C10) if your holiday dates are listed in cells C1 through C10. Excel will subtract those dates from the count as well, treating them like weekends.

NETWORKDAYS includes both the start and end dates in the count. If you want to exclude one of them, subtract 1 from the result: =NETWORKDAYS(A1,B1)-1.

Showing the result in a readable format

When you subtract dates, Excel returns a number. If you want to display the result as "X days", "X months", or "X years and Y months", you need to combine your calculation with text.

For a readable days result, type =B1-A1&" days". The ampersand (&) joins the number to the text. For months and years using DATEDIF, try =DATEDIF(A1,B1,"Y")&" years, "&DATEDIF(A1,B1,"YM")&" months". This shows both units in one cell, like "2 years, 3 months".

If the result is 0 or 1, you may want to remove the plural. Use an IF statement: =IF(B1-A1=1,"1 day",B1-A1&" days"). This shows "1 day" when the difference is exactly one, and "X days" for all other numbers.

Common mistakes and how to fix them

The most common error is forgetting that Excel stores dates as numbers. If you see a large number like 44500 instead of a date difference, the cell is formatted as a number. Right-click the cell, select Format Cells, choose Date, and pick a format. The same number will now display as a recognizable date.

Another frequent issue is mixing date formats. If one cell contains 1/15/2024 and another contains 15-Jan-2024, Excel may not recognize them as the same type of data. Convert both to the same format before subtracting. The safest approach is to use DATEVALUE on any date you are unsure about.

If DATEDIF returns #NUM!, the start date is after the end date. Swap them: =DATEDIF(B1,A1,"D") instead of =DATEDIF(A1,B1,"D"). DATEDIF requires the earlier date first.

Frequently Asked Questions

What if I want to include the start date but not the end date in my count?

Subtract 1 from your result. If you use =B1-A1, change it to =B1-A1 (this already includes both dates). To exclude the end date, use =B1-A1 as-is, since the subtraction counts the gap. For DATEDIF, the function already excludes the start date, so =DATEDIF(A1,B1,"D")+1 includes it.

Can I calculate the difference in hours or minutes?

Yes, if your cells contain times. Subtract the dates as usual, then multiply by 24 for hours: =(B1-A1)*24. Multiply by 1440 for minutes: =(B1-A1)*1440. If the result shows decimals, wrap it in INT or ROUND. DATEDIF does not support hours or minutes, so subtraction is your only option for those units.

Why does my formula show a negative number?

The start date is after the end date. Reverse the order of your cells. Change =A1-B1 to =B1-A1. If you want to show negative numbers as positive, wrap the formula in ABS: =ABS(A1-B1).

How do I calculate age from a birth date?

Use DATEDIF with the birth date as the start and today's date as the end: =DATEDIF(A1,TODAY(),"Y"). This returns the person's age in complete years. TODAY() is a function that always returns the current date, so the age updates automatically each day.

What if the dates span across different years?

Subtraction and DATEDIF both work across year boundaries automatically. If A1 is December 31, 2023 and B1 is January 2, 2024, =B1-A1 correctly returns 2 days. You do not need to do anything special — Excel handles the year change internally.