Excel Formula for Calculating Days Between Dates

To calculate the number of elapsed days between two dates in Excel, subtract the earlier date from the later one. If the start date is in A2 and the end date is in B2, enter =B2-A2; for example, January 1 to January 10 returns 9 days. Use a different method when you need to count both dates, weekdays only, or days from a date to today.

Basic formula: subtract one date from another

Excel stores dates as serial numbers, so ordinary subtraction gives the number of days between them. In a worksheet with the start date in A2 and the end date in B2, enter =B2-A2 in C2. If A2 contains January 1, 2026, and B2 contains January 10, 2026, the result is 9. Fill the formula down to calculate the interval for other rows.

The order matters: the end date goes first. Reversing the references produces a negative result when the end date is later than the start date. If you want the distance between two dates regardless of which comes first, and the dates are in A2 and B2, use =ABS(B2-A2). The January 1 and January 10 example returns 9 whichever date is in which cell. ABS removes the negative sign; it does not tell you which date came first.

After entering a formula, check how Excel formats its result. A day count should usually appear as a number. If the result displays as a date, select the result cell and choose Home > Number format drop-down > General, or choose Number. You can also open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, then select General or Number.

Understand what the day count means

A simple subtraction returns the elapsed number of 24-hour date steps between the two dates. It does not count both the start date and end date as full calendar days. For instance, January 1 to January 10 is nine elapsed days, even though the date labels from January 1 through January 10 include ten calendar dates.

This difference matters for booking durations, project schedules, age calculations, and attendance records. Decide whether your task asks for elapsed days or for the number of dates included in a range before choosing a formula. A hotel stay from check-in to check-out is commonly expressed as elapsed nights, while a list of calendar dates may need an inclusive count.

Excel’s calculation also depends on the values being real dates, not merely text that looks like dates. A date entered into a cell and recognized by Excel can be subtracted. Text copied from a website or imported from another system may not be recognized, particularly if its order is ambiguous, such as 03/04/2026. Confirm the intended month and day order before relying on the result.

Count both the start date and end date

For an inclusive count, add one to the ordinary difference. With the start date in A2 and the end date in B2, use =B2-A2+1. January 1 through January 10, 2026 returns 10, because the calculation includes both January 1 and January 10. This is often useful for counting calendar dates covered by a range.

Adding one is appropriate only when both endpoints should count. If a process begins on one date and finishes on another, and you want the number of complete days between those dates, the basic subtraction is usually the intended result. Be consistent when comparing intervals: mixing inclusive and elapsed-day counts can create off-by-one errors in reports.

If the dates might be entered in reverse order but you still want an inclusive count, the cells contain the two endpoint dates in either order, and both endpoints should count, use =ABS(B2-A2)+1. For January 1 and January 10, it returns 10. This still assumes the cells contain valid dates and does not account for weekends or holidays.

DATEDIF and other ways to calculate calendar days

Excel also supports DATEDIF for returning the difference in a selected unit. With the earlier date in A2 and the later date in B2, enter =DATEDIF(A2,B2,"d") to return the number of days. For January 1 to January 10, 2026, the result is 9. The "d" unit means days; DATEDIF can also calculate completed months or years, which is a different task.

For a plain day difference, subtraction is usually easier to read and maintain. DATEDIF is useful when a formula already uses its unit options, but it requires the start date to be no later than the end date. If the dates are reversed, it can return an error rather than a negative count. The function is supported in Excel for compatibility, although it may not appear in Excel’s formula autocomplete or function help in the same way as many newer functions.

Choose the calculation that matches the question rather than selecting a formula by appearance:

Use subtraction for elapsed calendar days.
Add one when both calendar endpoints count.
Use DATEDIF with the day unit when you specifically want that function’s unit-based syntax.
Use a workday function when weekends or holidays should be excluded.

Calculate weekdays and exclude holidays

For a count of Monday-to-Friday workdays, use NETWORKDAYS. It counts the start and end dates when they are weekdays, and it excludes Saturday and Sunday by default. With the start date in A2 and end date in B2, enter =NETWORKDAYS(A2,B2). January 5 through January 9, 2026, is Monday through Friday, so the result is 5.

To exclude holidays as well, list the holiday dates in D2:D3 and use =NETWORKDAYS(A2,B2,$D$2:$D$3), with the same start and end date cells. If D2 contains January 7, 2026, and D3 contains another holiday, January 5 through January 9 returns 4 because January 7 is removed from the five weekdays. The dollar signs keep the holiday range fixed if you copy the formula down. Leave unused holiday cells blank rather than putting labels in the date range.

When the weekend is not Saturday and Sunday, NETWORKDAYS.INTL lets you specify a weekend pattern. With A2 as the start date and B2 as the end date, enter =NETWORKDAYS.INTL(A2,B2,1) to specify the standard Saturday-Sunday weekend explicitly. Its weekend argument can also represent other weekend patterns; consult Excel’s function help for the numeric code or text pattern that matches your schedule. You can include a holiday range as an additional argument when needed.

These functions count working dates, not the number of paid hours or shifts. A part-time schedule, rotating roster, or business-specific closure calendar may need a more tailored calculation. Check that the dates and holiday list cover the same reporting period, and verify a short sample manually before using the count in a larger report.

Calculate days from a date to today

TODAY returns the current date and updates when Excel recalculates the workbook. With a starting date in A2, use =TODAY()-A2 to calculate elapsed days from that date to today. The result changes as days pass. If A2 is a future date, the result is negative, which can be useful for a countdown-style calculation but may not be what a past-due report needs.

For the number of days remaining until a future date in B2, use =B2-TODAY(). A positive result means the date is ahead; zero means it is today; and a negative result means it has passed. These formulas update based on Excel’s recalculation, so they are appropriate for a live status sheet. If you need to preserve the count as it stood on a particular day, record a fixed date rather than relying on TODAY.

To count weekdays from today to a deadline, with the deadline in B2, enter =NETWORKDAYS(TODAY(),B2). This includes today if today is a weekday and falls within the range. If your business rule should start counting tomorrow, clarify that rule and adjust the starting date accordingly; a weekday total can differ by one depending on whether the current date is included.

Dates that include times

Excel can store a date and time together. The whole-number portion represents the date, while the fractional portion represents time within the day. Subtracting two date-time values therefore returns a number of days that may include a decimal. For example, a result of 1.5 represents one and a half days, or 36 hours.

With a start date-time in A2 and an end date-time in B2, enter =B2-A2 to get the exact elapsed interval in days. If the cells contain January 1 at 12:00 PM and January 3 at 12:00 AM, the result is 1.5 days. Format the result as General or Number to see the decimal; a date format can make the result look confusing. Multiply the day value by 24 if you need elapsed hours, or by 1440 for elapsed minutes.

Do not remove the decimal automatically if hours matter. A formula that returns only whole days answers a different question from one that measures elapsed time. Also consider whether the timestamps use the same time zone and whether daylight-saving changes affect the underlying data before interpreting an interval as exact clock hours.

Common errors and how to fix them

If subtraction returns an error or an unexpected result, check the cells before changing the formula. Common causes include text masquerading as a date, an end date earlier than the start date, or a result cell formatted as a date. A date that looks correct on screen is not proof that Excel recognizes it as a date value.

Result appears as a date: change the result cell to General or Number using the Home tab’s number-format controls.
Result is negative: confirm the start and end references are in the intended order. Use an absolute difference only if direction does not matter.
Formula gives an error: check for text dates, blank cells, or invalid date entries. Re-enter the dates in a format Excel recognizes for your regional settings.
Count differs by one: determine whether you need elapsed days or an inclusive count of both endpoint dates.
Workday count seems wrong: check the weekend pattern and ensure holiday cells contain valid dates, not text.

For unambiguous dates, use the Date format appropriate to your region or build a date with Excel’s DATE function. With year 2026, month 1, and day 1 as the intended values, enter =DATE(2026,1,1) in a cell to create January 1, 2026. This can be safer than typing a slash-separated date whose month/day order may be interpreted differently on another computer.

Frequently Asked Questions

What is the simplest Excel formula to find days between two dates?
Put the earlier date in A2 and the later date in B2, then subtract the earlier date from the later date with =B2-A2. It returns elapsed days, so January 1 to January 10 is 9, not 10.

How do I include both dates in the count?
With the start date in A2 and end date in B2, use =B2-A2+1. That counts both endpoints, making January 1 through January 10 equal 10 calendar dates.

How can I count only working days in Excel?
Use NETWORKDAYS with the start date and end date as its first two arguments. It excludes Saturday and Sunday by default; supply a holiday-date range as the optional third argument to exclude holidays too.

Why does Excel show a date instead of a number?
The formula result cell may have a date number format. Select the cell and change its format to General or Number. The calculation can be correct even when the display format makes the result look wrong.

How do I calculate days from a date to today?
For a date in A2, subtract it from TODAY with =TODAY()-A2. The answer updates as Excel recalculates, and it will be negative if the date in A2 is in the future.

Can Excel calculate days when cells contain times too?
Yes. Subtract the start date-time from the end date-time. The result may include a decimal fraction of a day; for example, 1.5 means 36 elapsed hours. Use a number format that displays decimals if you need to see the partial day.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.