Excel Day Count Formula: Calculate Days Between Dates

To count calendar days between two dates in Excel, subtract the earlier date from the later one: with a start date in A2 and an end date in B2, use =B2-A2. This returns elapsed days, not an inclusive count of both dates; add 1 when your rule is to count the start and end dates as days in the period.

The basic Excel day count formula

Excel stores valid dates as numbers, so subtraction works directly: the difference between the two date values is the number of days between them. Enter the start date in A2 and the end date in B2. For example, if A2 is January 6, 2025, and B2 is January 10, 2025, the formula returns 4. That is the elapsed time from the start of January 6 to the start of January 10—not a count of four named dates.

The subtraction method is usually the clearest choice for a simple date interval. It also makes the counting convention easy to see and adjust. If the dates are reversed, the result is negative, which can help reveal a data-entry error or may be useful when direction matters.

Excel also has a DAYS function. With the start date in A2 and the end date in B2, use =DAYS(B2,A2); for January 6 to January 10, 2025, it returns 4. Its argument order is end date first, start date second. Subtraction and DAYS give the same result for ordinary date values, so you do not need both in a worksheet.

Check that the cells contain real dates

A formula can only calculate a date interval reliably when Excel recognizes both inputs as dates or date-and-time values. A cell may look like a date but contain text, especially when data was copied from a website, exported from another program, or entered using a date order that does not match the computer’s regional settings. Text dates can produce errors or unexpected results.

Click a date cell and check whether Excel treats it as a number formatted as a date. Changing its display format may help identify the underlying value, but formatting text as a date does not convert the text itself. If imported dates do not calculate, convert them to actual Excel dates before relying on the day count. The appropriate conversion depends on how the source text is arranged.

To show a day-count result as a number, select the result cell and choose Home > Number Format > General or Number. If the result appears as another date, the formula may be correct while the result cell is simply formatted as a date. A date is a serial value, and applying a date display format to a difference can make the number look confusing.

Inclusive counts and date-range conventions

Subtracting dates counts the gap between them. Many everyday questions use a different convention: how many calendar dates are included from the first date through the last date? In that case, count both endpoints by adding one. With a start date in A2 and an end date in B2, use =B2-A2+1. January 1 through January 31, 2025, returns 31.

The distinction matters in leave trackers, event schedules, inventory periods, and rules that describe a date range as inclusive. For example, January 6 through January 10 contains five named dates, even though the elapsed difference between the dates is four days. Neither answer is inherently wrong; the right one depends on what the count represents.

Use the plain difference for elapsed days between two date points.
Add one when counting every calendar date from the start date through the end date.
Check the instructions for a form, policy, or report before choosing a convention.

For a range that can be entered backwards, decide what should happen before using the result. A negative answer may be appropriate, or the dates may need to be reordered. Do not automatically add one to every date difference: inclusive counting is a rule about the period, not a universal correction.

Calendar days versus working days

Calendar-day counts include weekends and holidays. If you need weekdays instead, use Excel’s NETWORKDAYS function. With a start date in A2, an end date in B2, and holiday dates in D2:D10, use =NETWORKDAYS(A2,B2,$D$2:$D$10). For Monday, January 6, through Friday, January 10, 2025, with no holidays in that interval, the result is 5. NETWORKDAYS counts qualifying workdays at both ends of the interval.

The holiday range is optional. Including it tells Excel to exclude those dates as well as the standard Saturday and Sunday weekend. Keep holidays in cells as real Excel dates, and use absolute references such as the dollar signs in the example if you plan to copy the formula down a column. Otherwise, the holiday range may shift as the formula is copied.

NETWORKDAYS answers a different question from subtracting dates. A five-day working week can contain more than five calendar days when a weekend falls inside the interval. Conversely, an interval that begins or ends on a weekend may have fewer counted workdays than its elapsed calendar-day difference suggests.

Weekends that do not fall on Saturday and Sunday

Some workplaces use a weekend pattern other than Saturday and Sunday. NETWORKDAYS.INTL is designed for those schedules. It lets you specify a weekend pattern, and it can also take a holiday range. For example, a Friday-and-Saturday weekend is different from the default Saturday-and-Sunday weekend, so using ordinary NETWORKDAYS would miscount the scheduled workdays.

Check the function’s weekend-code options against the actual schedule before using them. NETWORKDAYS.INTL can also accept a seven-character weekend pattern, with each position representing a day from Monday through Sunday. This is useful for less common schedules, but the pattern must be entered in the correct order. Keep a note in the workbook explaining the chosen weekend rule so another person can interpret the count.

If you are counting shifts rather than whole workdays, a workday count may still be too broad. A date-based function counts qualifying dates; it does not calculate the number of scheduled hours or handle varying shift lengths by itself.

When dates include times

Excel dates can include a time of day as a fractional part of the stored number. Consequently, subtracting a start timestamp from an end timestamp returns elapsed days and may include a decimal. For instance, the difference between 9 p.m. on January 1 and 8 a.m. on January 3 is 1 day and 11 hours, or about 1.46 days. It is not two full elapsed days.

If the question is instead how many calendar-date boundaries separate the two timestamps, discard their time portions before subtracting. With a start date and time in A2 and an end date and time in B2, use =INT(B2)-INT(A2). For the January 1, 9 p.m. to January 3, 8 a.m. example, this returns 2 because the calendar dates are two apart. That is a different measure from elapsed time, so choose based on the report’s definition.

For whole elapsed days, a decimal result can also be intentional. For example, it may be important to distinguish a 24-hour interval from a shorter period. Avoid rounding just to make the result look tidy unless the counting policy calls for it.

Day counts for interest and year fractions

In some financial calculations, the question is not simply how many calendar days separate two dates. A day-count convention defines how a date interval is converted into a fraction of a year. Common conventions include Actual/365, Actual/360, Actual/Actual, and 30/360. They can produce different year fractions for the same dates, so ordinary date subtraction alone does not establish which convention a contract or report requires.

Excel’s YEARFRAC function calculates a year fraction using a selected basis. With the start date in A2 and the end date in B2, use =YEARFRAC(A2,B2,1) for basis 1, Actual/Actual. If A2 is January 1, 2024, and B2 is January 1, 2025, the result is 1 year. The basis argument matters: basis 0 uses the US 30/360 method, basis 2 uses Actual/360, basis 3 uses Actual/365, and basis 4 uses European 30/360.

These options are not interchangeable. Leap years and the specific convention can affect the result, and some financial agreements define details beyond a simple spreadsheet selection. Treat examples as calculations, not advice: check the relevant terms or ask a qualified professional when the day count affects an actual financial obligation.

Common errors and how to fix them

A date appears as a large number. Excel stores dates numerically. Apply a date display format to the input cell if you want to read it as a date.
The result looks like a date. Set the result cell to General or Number using Home > Number Format.
The formula returns an error or an implausible number. Check that both inputs are valid Excel dates, not text, and that the intended start and end cells have not been reversed.
The count is off by one. Decide whether the task asks for elapsed days or an inclusive count of dates. The difference between those conventions is often exactly one.
A weekday count is wrong. Confirm whether the interval includes its endpoints, whether holidays are listed, and whether the weekend matches the schedule.
The answer has a decimal. One or both inputs may include times. Decide whether you want elapsed time or a count based on calendar dates.

When a formula is copied to other rows, verify that the start and end references move as intended. A holiday list usually needs fixed references so each row uses the same dates. Also check the workbook’s date inputs after importing or pasting data; a formula cannot repair inconsistent source values on its own.

Choosing the right method

Start by naming what the result should mean. For elapsed calendar days, subtract the dates. For a count that includes both boundary dates, add one. For weekdays, use NETWORKDAYS or NETWORKDAYS.INTL and account for holidays and the correct weekend. For a year fraction used in a particular convention, use YEARFRAC only after confirming the appropriate basis.

Keep the rule visible in the workbook. A nearby label such as Elapsed calendar days, Inclusive date count, or Working days excluding listed holidays helps prevent a correct formula from being mistaken for a different measure. This is especially useful when several columns show counts for the same dates but apply different conventions.

Frequently Asked Questions

Does Excel count the start date in a date difference?

No. Subtracting the start date from the end date returns the elapsed difference and does not count both endpoints as separate dates. Add one only when you need an inclusive count from the first date through the last.

What is the simplest formula to count days between dates?

Subtract the earlier date from the later date. If the start date is in A2 and the end date is in B2, =B2-A2 returns the calendar-day difference, provided both cells contain valid Excel dates.

How do I count weekdays between two dates in Excel?

Use NETWORKDAYS for a Saturday-and-Sunday weekend, optionally supplying a range of holiday dates. Use NETWORKDAYS.INTL when your weekend pattern differs from the standard one.

Why does my day count show a decimal?

The input dates may include times, which Excel stores as fractions of a day. The decimal represents part of a 24-hour day. If you want the difference between calendar dates regardless of time, remove the time portions before subtracting.

Is DAYS better than subtracting dates?

Not generally. DAYS explicitly names the operation and takes the end date before the start date, while subtraction is shorter and familiar. Both return the same elapsed-day difference for valid dates; use whichever makes your worksheet easier to understand.

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.