Excel Formula for Counting Calendar Days: The Easy Way

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for counting calendar days between two dates is =B2-A2 (where B2 is the later date and A2 is the earlier date), formatted as a number. For a single date counting forward or backward, use =A2+30 to add 30 calendar days or =A2-30 to subtract them. This article explains how these work, when to use DAYS, DATEDIF, or NETWORKDAYS, and how to avoid common pitfalls like time-of-day errors and formatting mistakes.

How Excel Stores Dates (The Background)

Excel stores dates as serial numbers, where 1 equals January 1, 1900, and each whole day adds 1. For example, January 1, 2024, is serial number 45292. This means subtracting one date from another directly gives the number of whole days between them—no special function needed. The catch is that a date entered as text (like “01/01/2024”) won’t subtract correctly, and a date with a time component (like 01/01/2024 6:00 PM) includes a fraction (0.75) that can throw off your count.

The Basic Formula: Subtract Two Dates

Assume you have start dates in column A (A2:A10) and end dates in column B (B2:B10). In C2, enter:

=B2-A2

Then format C2 as a number (Home > Number Format > General). For example, if A2 is 2024-01-01 and B2 is 2024-01-10, the formula returns 9—that’s the number of full 24-hour periods between them. This counts calendar days, not business days. If you need to count both the start and end day (inclusive), add 1: =B2-A2+1. The first formula gives 9 for Jan 1 to Jan 10; the inclusive version gives 10.

Using the DAYS Function for Clarity

If you prefer a named function, DAYS does the same subtraction but with the arguments reversed. The syntax is =DAYS(end_date, start_date). With the same layout (A2 start, B2 end), enter in C2:

=DAYS(B2, A2)

This returns 9 for the example above. DAYS is available in all modern Excel versions (2013 and later). Its only advantage over plain subtraction is readability—someone reading your sheet sees “DAYS” and understands intent. It does not handle inclusive counting automatically, so add 1 if needed.

Counting Days from a Single Date (Today or Any Date)

To count calendar days from a fixed date to today, use =TODAY()-A2. For example, if A2 contains 2024-01-01 and today is 2024-03-15, this returns 74 (assuming no time component). To add a specific number of calendar days to a date, simply add the number: =A2+30 gives the date 30 days later. To find the date 45 days before, use =A2-45. This works because Excel’s serial number system treats each integer as one day. For a dynamic countdown, combine with TODAY(): =A2-TODAY() returns days remaining (negative if past).

When to Use DATEDIF or NETWORKDAYS Instead

Plain subtraction counts calendar days, but sometimes you need different units or to exclude weekends. Use DATEDIF when you need months or years, not days—its syntax is =DATEDIF(start_date, end_date, "d") for days, but it’s redundant for days alone. For business days (Monday–Friday), use =NETWORKDAYS(start_date, end_date). For example, from 2024-01-01 to 2024-01-10, NETWORKDAYS returns 8 (excluding Jan 6–7 weekend), while plain subtraction gives 9. To also exclude holidays, add a holiday range: =NETWORKDAYS(A2, B2, $F$2:$F$5) where F2:F5 contains holiday dates. Note that NETWORKDAYS counts the start day as 1, unlike subtraction.

Common Problems and How to Fix Them

  • Dates stored as text: If subtraction returns a #VALUE! error, your dates are text. Select the column, use Data > Text to Columns > Delimited > Finish, or use =DATEVALUE(A2) to convert.
  • Time components causing off-by-one: If a cell has 2024-01-01 6:00 PM, subtracting from a midnight date gives a fraction. Use =INT(B2)-INT(A2) to ignore times.
  • Negative results: If the end date is earlier than the start, you get a negative number. Use =ABS(B2-A2) for absolute days, or reorder your dates.
  • Wrong formatting: If the result shows as a date (like 01/09/1900), change the cell format to General or Number.
  • Leap years: Excel handles them automatically—Feb 29, 2024, is a valid serial number, and subtraction works correctly.

Comparing Subtraction, DAYS, and DATEDIF

Here’s a quick reference for when to use which:

  • Plain subtraction (=B2-A2): Best for simple calendar days, fastest, works in all versions.
  • =DAYS(B2,A2): Same result, clearer intent, but no extra features.
  • =DATEDIF(A2,B2,"d"): Use only if you might later change “d” to “m” (months) or “y” (years). Note DATEDIF’s “d” ignores time components but counts whole days.
  • =NETWORKDAYS(A2,B2): Use for business days, not calendar days.
  • =B2-A2+1: Use when you need inclusive counting (both endpoints).

Frequently Asked Questions

How do I count days between two dates including the start date? Add 1 to the subtraction: =B2-A2+1. For Jan 1 to Jan 10, this returns 10, counting both Jan 1 and Jan 10.

Why does my formula return a date like 01/09/1900 instead of 9? The cell is formatted as a date. Select the cell, go to Home > Number Format, and choose General or Number. The underlying value is correct; only the display is wrong.

Can I count days from today to a future date? Yes, use =A2-TODAY() where A2 is the future date. This returns a positive number of days remaining. If A2 is in the past, it returns a negative number.

Does Excel count leap days correctly? Yes, Excel’s date system includes leap years. For example, from 2024-02-28 to 2024-03-01 returns 2 days, correctly accounting for Feb 29.

What if my dates are in different columns but I need days in a third? Just reference the cells: with start in A2 and end in B2, put =B2-A2 in C2 and drag down. No special syntax needed.

Is there a formula to count days excluding weekends but including holidays? Use =NETWORKDAYS(A2, B2, holidays_range) where holidays_range is a list of dates to exclude. This counts business days, not calendar days, so it’s different from the basic formula.

Related

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.