Count From Date To Date In Excel

Counting Dates in Excel Is Simpler Than Most People Think

You have two dates and you need to know how many days, weeks, or specific weekdays sit between them. Most people reach for a calculator or try to manually count through a calendar. That's unnecessary. Excel has built-in functions that handle this instantly. The DATEDIF function is the core tool here, even though Microsoft quietly hid it. It's been in Excel since version 97 and still works in every modern version. The syntax looks like this: =DATEDIF(start_date, end_date, unit). The unit codes are "D" for days, "M" for months, and "Y" for years. You can also combine them. "MD" gives you remaining days after whole months, "YM" gives remaining days after whole years, and "YD" compares the same day-and-month across two years. A basic formula looks like this:

=DATEDIF(A1, B1, "D") Where A1 is your start date and B1 is your end date. Excel returns the number of days between them. No extra work required. Just make sure both cells are actual date values, not text that looks like dates. For months and years, you'd swap the unit code:

=DATEDIF(A1, B1, "M") for months =DATEDIF(A1, B1, "Y") for years The subtraction method is another option. If you just want total days, you can subtract one date from the other directly. Dates in Excel are stored as serial numbers, so a simple minus sign does the job.

=B1-A1 This works fine for basic counting but falls apart when you need months or years, since you can't do clean division on serial numbers without accounting for the irregular lengths of months and leap years. That's where DATEDIF earns its keep.

Counting Weekdays Only

Sometimes you don't care about total calendar days. You need business days or specific weekdays. The NETWORKDAYS function handles business days, and COUNTIFS can count individual weekdays like Mondays or Fridays between two dates. For business days: =NETWORKDAYS(start_date, end_date)

This automatically skips weekends (Saturday and Sunday). If your company uses a different weekend schedule, you can add a holiday range as a third argument to exclude those too. To count Mondays specifically between two dates, you'd use: =COUNTIFS(A:A, ">="&start_date, A:A, "<="&end_date, B:B, "=Monday")

Or more simply with a helper column that checks each date in the range against the weekday condition.

A Real Problem I Ran Into With This

A few years ago I was working on a project where someone had mixed date formats in the same column. Some cells contained real Excel dates, but others were text strings formatted to look like dates. The DATEDIF function silently produced #VALUE! errors for the text cells, and because the spreadsheet used SUM or AVERAGE around those cells, the whole result became #VALUE!. There was no warning anywhere. I spent about twenty minutes hunting through the sheet before I realized the issue wasn't with the formula itself. The workaround was straightforward: I added a check using the ISNUMBER function to confirm each cell was actually a date before running DATEDIF on it. The revised formula looked like this: =IF(ISNUMBER(A1)*ISNUMBER(B1), DATEDIF(A1, B1, "D"), 0)

That way the calculation returned zero instead of breaking the entire column when it hit a text date. If you're dealing with external data imports, always validate your date columns first. Importing from CSVs or databases often introduces text-formatted dates that look correct but behave completely differently inside formulas.

Common Pitfalls to Avoid

The most frequent mistake is entering dates as text. If a date cell has a green triangle in the corner, it's text, not a date. DATEDIF will error out. Use DATEVALUE to convert text dates into proper serial numbers before feeding them into calculations. Another issue is getting the order wrong. DATEDIF requires the start date to be earlier than the end date. If you reverse them, you get a #NUM! error. If your data might have reversed dates, wrap the formula with an IF or ABS function to handle it. Leap years sometimes cause confusion too. DATEDIF handles them correctly when using the "Y" unit because it counts anniversary dates rather than doing simple arithmetic. But the "MD" unit can give misleading results near February 29. It ignores the leap day entirely, which means the day count might be off by one year depending on which side of February 29 your dates fall on.

Why This Still Matters

People sometimes skip DATEDIF because they think Excel should have a more obvious way to do this. But the function exists, it works reliably, and it handles edge cases better than manual counting or custom formulas. The documentation is thin because Microsoft stopped updating it, not because it's broken. It has been tested across nearly thirty versions of Excel at this point. If you're building a template that other people will use, I'd recommend adding a small validation section at the top that flags any non-date values in your input range. It saves everyone from chasing down errors later. The setup takes about three minutes and prevents maybe ten minutes of troubleshooting per month for the people using your sheet. For the actual counting, the DATEDIF function with the right unit code covers 95 percent of what you'll need. The remainder is handled by NETWORKDAYS and COUNTIFS for the more specific cases. You don't need anything fancy beyond those tools for date-to-date counting in Excel.