To calculate elapsed days between two dates in Excel, enter the start date in A2 and the end date in B2, then use =B2-A2. If A2 is January 1, 2025 and B2 is January 10, 2025, the result is 9 days; use =B2-A2+1 instead when you want to count both the start and end dates.
The Quick Formula for Elapsed Days
Excel stores dates as serial numbers, so subtracting one date from another gives the number of days between them. With the start date in A2 and the end date in B2, enter =B2-A2 in another cell. For January 1, 2025 in A2 and January 10, 2025 in B2, the result is 9: nine 24-hour calendar-day intervals separate those dates.
The order matters. Subtract the earlier date from the later date to get a positive result. If the end date is in A2 and the start date is in B2, reverse the references to match the actual cell layout. Excel does not need a special date-difference function for ordinary calendar-day calculations.
After entering the formula, if the result looks like a date rather than a number, select the result cell and choose Home > Number Format, then select General or Number. Excel may have inherited a date format from a nearby cell; the calculation can be right even when the display format is misleading.
What “Days Between” Means
There are two common ways to interpret a date range. The subtraction formula counts elapsed days: January 1 to January 10 is 9 days, because the start date is not counted as a complete day that has passed. This is usually what people mean by duration, age differences, days until a deadline, or the gap between two events.
An inclusive count treats both dates as days in the range. For example, a trip that starts January 1 and ends January 10 touches ten calendar dates, even though only nine days elapse between the two dates. With the start date in A2 and end date in B2, use =B2-A2+1 to count both endpoints. That returns 10 for the example dates.
Choose the interpretation that fits the task rather than assuming one is universally correct. A contract period, attendance record, reservation, or date range shown on a calendar may need inclusive counting. A person asking how many days have passed since an event usually wants elapsed days. State the convention when sharing a result so another person understands whether the endpoints are included.
Enter and Check the Dates
For a dependable calculation, first make sure Excel recognizes both entries as dates rather than text. Type dates in a familiar format, such as 1/1/2025 and 1/10/2025, according to your regional settings. You can also create dates with Excel’s DATE function, which avoids ambiguity when assembling a date from a year, month, and day. With the year in C2, month in D2, and day in E2, enter =DATE(C2,D2,E2) to create the date value in another cell.
Use these steps for the basic calculation:
- Enter the earlier date in A2 and the later date in B2.
- Select C2 and type
=B2-A2, then press Enter. - If needed, change C2 to General or Number so the result displays as a count of days.
- Check whether the task needs elapsed days or an inclusive count before using the result.
For a table, put the same formula in the first result row and fill it down. Excel will adjust the relative references for each row. If a column contains a header, keep the formula beneath it and verify a few rows by subtracting their dates manually.
Using DATEDIF Instead of Subtraction
DATEDIF is another option when the calculation should explicitly return a difference in days. With the start date in A2 and the end date in B2, use =DATEDIF(A2,B2,"d"). For January 1 to January 10, 2025, it also returns 9. The unit argument "d" requests the total number of days between the dates.
For a simple day count, subtraction is usually clearer and easier to adapt. DATEDIF can be useful in a formula that also calculates months or years, but it is a legacy compatibility function and does not appear in Excel’s function autocomplete in the same way as many standard functions. The start date must be less than or equal to the end date; otherwise DATEDIF can return a #NUM! error. Direct subtraction instead returns a negative number when the dates are reversed, which can be useful for spotting the order.
Count Working Days, Not Calendar Days
If weekends should not count, use NETWORKDAYS. With a start date in A2 and end date in B2, enter =NETWORKDAYS(A2,B2). This counts weekdays from Monday through Friday and includes the start and end dates when they fall on weekdays. For a Friday-to-Monday range, it returns 2 because Friday and Monday are working days; ordinary subtraction returns 3 elapsed days.
To exclude holidays too, put the holiday dates in E2:E10. With the start and end dates in A2 and B2 and holidays in E2:E10, use =NETWORKDAYS(A2,B2,$E$2:$E$10). The holiday cells must contain actual Excel dates. The dollar signs keep the holiday range fixed when the formula is filled down. Holidays that fall on a weekend do not reduce the weekday count a second time.
For a schedule with a different weekend pattern, NETWORKDAYS.INTL lets you specify the weekend. With dates in A2 and B2, and holidays in E2:E10, use =NETWORKDAYS.INTL(A2,B2,1,$E$2:$E$10). The weekend argument 1 means Saturday and Sunday, the standard weekend; other supported codes and a seven-character weekend pattern can represent alternate schedules. Check the function’s weekend convention against the actual work calendar, especially for rotating shifts.
Dates That Include Times
Excel dates can include a time fraction as well as a calendar date. For example, two entries on the same date at 9:00 a.m. and 5:00 p.m. differ by one-third of a day. Subtracting them returns a fraction, though a cell formatted as General may display a decimal such as 0.3333. To show elapsed hours with the start date and time in A2 and the end date and time in B2, use =(B2-A2)*24. For those example times, the result is 8 hours.
If the goal is to count calendar-date boundaries and ignore times, remove the time portions before subtracting. With date-time values in A2 and B2, use =INT(B2)-INT(A2). INT drops the fractional time component from each serial value. This is different from measuring 24-hour periods: crossing midnight by a few minutes counts as one calendar date change but is not a full elapsed day.
Common Errors and How to Fix Them
- The answer displays as a date: Change the result cell’s number format to General or Number. The formula may be correct even if the display is not a day count.
- The formula returns #VALUE!: One or both entries may be text that only looks like a date. Re-enter them as dates using the workbook’s regional date format, or convert the source data to real date values.
- The answer is negative: The date in the first reference is later than the date in the second. Check the column order and reverse the subtraction if necessary. Avoid using ABS automatically, since it hides which date came first.
- The result is one day different from expectations: Confirm whether the request means elapsed days or an inclusive count. Add 1 only when both endpoints are meant to count.
- The result shows hashes: A narrow column can display hash marks instead of a value. Widen the column. A negative result displayed using a date format can also be problematic; use General or Number to inspect the signed difference.
Regional settings can affect how Excel interprets typed dates. In one locale, 3/4/2025 may mean March 4; in another, it may mean 3 April. When data comes from different systems, use unambiguous date values or construct dates with DATE rather than assuming every imported string has the same interpretation. Sorting the source dates from oldest to newest can also help reveal text entries that do not sort with genuine dates.
Choosing the Right Method
Use ordinary subtraction for the simplest and most transparent answer. It works for elapsed calendar days, supports negative differences when dates are reversed, and can be extended to include or exclude time. Use DATEDIF when its explicit date-difference style fits a larger formula, while remembering that the start date must not be later than the end date. Use NETWORKDAYS or NETWORKDAYS.INTL when the question is about working days rather than all calendar days.
Before choosing, identify three details: whether endpoints count, whether weekends count, and whether holidays or times matter. For a single deadline countdown, subtraction is generally enough. For staffing, service-level reporting, or a business calendar, a workday function and a maintained holiday list are more appropriate. Excel formulas calculate only from the dates and rules supplied; they do not know an organization’s local holidays or special closures.
Frequently Asked Questions
How do I calculate days between two dates in Excel, including the start date?
With the start date in A2 and end date in B2, use =B2-A2+1. This counts both endpoints; for January 1 through January 10, 2025, the result is 10.
Can Excel calculate days between dates in different years?
Yes. With the earlier date in A2 and later date in B2, =B2-A2 works across months, years, and leap days because Excel stores dates as serial values. No special year adjustment is needed.
How can I calculate days from a date until today?
With the earlier date in A2, use =TODAY()-A2. TODAY returns the current date and updates when the workbook recalculates. A future date in A2 produces a negative result; reverse the subtraction if you want a positive countdown instead.
How do I count weekdays between two dates?
With the start date in A2 and end date in B2, use =NETWORKDAYS(A2,B2). It counts Monday through Friday and includes qualifying endpoints. Add a holiday range as the third argument when those dates should be excluded.
Why does my date-difference formula show a decimal?
The cells may contain times as well as dates. Subtraction preserves fractions of a day, so a 12-hour difference is 0.5. Format the result as needed, multiply by 24 for hours, or use INT on both date-time values when you only want the difference between calendar dates.
Related
- Excel Named Range Greyed Out? Causes and Fixes to Try
- Excel Named Range Lookup: Find and Use Named Ranges
- Budget vs Actual Excel Template: Build and Use One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Not Responding? Causes and FixesIf Excel says Not Responding, give it a short time to finish before closing it; the message can…
- CSV vs ODS in Excel: Which Should You Use?In Excel, choose CSV when you need a simple, widely compatible file of flat data; choose ODS when…
- How to Freeze Two Columns in Excel While ScrollingTo keep columns A and B visible while you scroll horizontally in Excel for Microsoft 365 on Windows,…
- Google Translate for Google Sheets: Add-on or Formula?You usually do not need a Google Translate add-on for Google Sheets: the built-in GOOGLETRANSLATE function can translate…
- Schedule an Email Reminder in Outlook: Step-by-StepTo get a reminder to follow up on an Outlook email, flag the message and set a reminder…
- Google Sheets FILTER Formula: How Do You Use It?The Google Sheets FILTER formula returns only the rows or columns that meet conditions you specify. For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.