The simplest way to find days between dates

To find the number of days between two dates in Excel, subtract the earlier date from the later date. If your earlier date is in cell A1 and your later date is in cell B1, type =B1-A1 into an empty cell and press Enter. Excel will show you the number of days between them.

This works because Excel stores dates as numbers counting from January 1, 1900. When you subtract one date from another, you are subtracting those numbers, which gives you the day count. The result is always a whole number with no decimal places — you cannot have half a day between dates.

If your result shows as a decimal or as a date instead of a number, you need to format the cell. Right-click the cell, select Format Cells, choose Number from the Category list on the left, and click OK. The cell will then display the correct day count.

Key Takeaways

  • Subtract the earlier date from the later date using a straightforward formula like =B1-A1 to get the number of days between them.
  • Excel stores dates as numbers, so subtraction gives you an exact day count without any manual calculation.
  • If the result shows as a date or decimal instead of a whole number, format the cell as a Number to see the correct output.
  • The DATEDIF function offers an alternative if you need to count days in a specific way, such as excluding weekends or counting only business days.
  • Negative results mean your dates are reversed — the earlier date is in the second cell instead of the first.

When your dates are in different formats

Sometimes dates in Excel appear as text instead of actual dates — this happens when data is imported from another program or typed in a format Excel does not recognize. If you subtract and get an error or a very large number, your dates are probably text, not dates.

To check, click on the cell with the date. Look at the formula bar at the top — if the date is surrounded by quotation marks or appears as plain text, it is stored as text. You can convert it by using the DATEVALUE function. Type =DATEVALUE(A1) in a new cell, and Excel will convert the text to a real date that you can then subtract.

If you have many dates to convert, copy the DATEVALUE formula down the column, then copy the results and paste them back as values only. This replaces the text dates with real dates you can work with.

Using DATEDIF for specific counting methods

The DATEDIF function lets you count days in different ways. The basic structure is =DATEDIF(start_date, end_date, "D"), where "D" means days. This gives the same result as straightforward subtraction, but DATEDIF also accepts other units.

Use "Y" to count complete years between dates, "M" to count complete months, or "MD" to count days ignoring the month and year (useful for birthdays). For example, =DATEDIF(A1, B1, "M") tells you how many full months have passed. If someone was born on March 15, 1990, and today is March 14, 2024, DATEDIF will return 33 years, not 34, because the birthday has not yet occurred this year.

DATEDIF is less common than straightforward subtraction, but it is useful when you need to count in units other than days or when you need to respect calendar boundaries like months or years.

Counting only business days or weekdays

If you need to count only weekdays (Monday through Friday) and skip weekends, use the NETWORKDAYS function. Type =NETWORKDAYS(A1, B1) where A1 is your start date and B1 is your end date. This counts all weekdays between those dates, including both the start and end dates if they fall on a weekday.

NETWORKDAYS also lets you exclude holidays. Add a third part to the formula: =NETWORKDAYS(A1, B1, C1:C10), where C1:C10 is a range containing your holiday dates. Excel will subtract those days from the weekday count, even if they fall on a Saturday or Sunday.

This function is most useful for project timelines, work schedules, or any situation where weekends do not count. If you only need to exclude weekends without holidays, NETWORKDAYS is simpler than trying to calculate it manually.

Handling dates that span across years

Dates that cross from one year into another work exactly the same way as dates in the same year. If your start date is December 28, 2023, and your end date is January 5, 2024, the formula =B1-A1 will correctly return 8 days. Excel automatically accounts for the year change because it is counting the actual number of days that passed.

The only time you need to think about years is if you are using DATEDIF with the "Y" unit (complete years) or "M" unit (complete months). In those cases, Excel counts only full periods. From December 28, 2023, to January 5, 2024, is zero complete months because a full month has not passed.

Fixing common mistakes

If your formula returns a negative number, your dates are reversed. The formula subtracts the first date from the second, so if the first date is later, you get a negative result. Swap the cell references: use =A1-B1 instead of =B1-A1, or wrap your formula in the ABS function to always get a positive number: =ABS(B1-A1).

If your result shows as a date (like 1/1/1900) instead of a number, the cell is formatted as a date. Right-click, select Format Cells, choose Number, and click OK. If your result shows as a decimal like 8.5, the same fix applies — format the cell as a Number with zero decimal places.

If you see #VALUE! error, one of your cells does not contain a valid date. Check that both cells actually hold dates and not text or empty cells. Click on each cell and look at the formula bar to confirm what is stored there.

Frequently Asked Questions

Does Excel count the start date and end date or just the days between them?

straightforward subtraction counts the days between dates but not the start date itself. If you subtract January 1 from January 5, you get 4, not 5. If you need to include both the start and end dates, add 1 to your formula: =B1-A1+1. NETWORKDAYS includes both dates by default.

Can I calculate days between dates if one of them is today?

Yes. Use the TODAY function in your formula. Type =TODAY()-A1 to find how many days have passed since the date in A1, or =A1-TODAY() to find how many days until a future date. TODAY always returns the current date, so the result changes every day.

What if I want to show the result as weeks or months instead of days?

Divide your day count by the number of days in the unit you want. For weeks, use =(B1-A1)/7. For months, use =(B1-A1)/30.44 (the average days per month). For exact months or years, use DATEDIF with "M" or "Y" instead.

Why does my date formula show a number like 45000 instead of a date?

That number is Excel's internal representation of a date — the count of days since January 1, 1900. Your cell is formatted as a Number instead of a Date. Right-click the cell, select Format Cells, choose Date, pick a date format, and click OK. The cell will then display as a readable date.

Can I calculate days between dates in different time zones?

Excel's date functions do not account for time zones. If you need to include the time of day (hours and minutes), store the full date and time in your cells, and subtraction will give you a decimal result where the whole number is days and the decimal represents the time. For example, 2.5 means 2 days and 12 hours. Format as a Number to see this, or use the TEXT function to display it as hours and minutes.