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
- Excel Conditional Formatting Per Row: How To
- Excel Conditional Formatting for Duplicates: How-To
- Excel Conditional Formatting for Past Dates: How to Set It Up
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Text Right of Character: Formulas and ExamplesTo return the text to the right of a character in Excel, use TEXTAFTER in Microsoft 365 or…
- How to Filter Dates in Excel by Month, Year, or RangeTo filter dates in Excel, select a cell in your data, choose Data > Filter, open the arrow…
- Excel Day Countdown: Build a Live Days-Left FormulaWith a deadline date in B2, enter =B2-TODAY() in C2 to show how many calendar days remain. For…
- Excel Formula for Calculating PercentileThe Excel formula for calculating a percentile is =PERCENTILE.INC(array, k) for inclusive percentiles (0 to 1 inclusive) or…
- Excel Power Query vs Formulas: Which Should You Use?Use Excel formulas when you want calculations to update as you edit worksheet cells; use Power Query when…
- Project Budget Tracker Spreadsheet: Setup and FormulasA project budget tracker spreadsheet compares the approved budget with actual spending and outstanding commitments, so you can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.