"Calculate the days" sounds like one question, but a spreadsheet usually means one of several different things by it: how many days sit between two dates, how many days are left until a deadline, how many days are already gone since a start date, or simply how many days a given month has. Excel has a clean formula for each of these, but reaching for the wrong one — or the right one with a formatting slip — is how a countdown cell ends up showing a date like 1/15/1900 instead of a number, or a "days left" column silently stops updating.
This guide walks through the core day-counting formulas in Excel — DAYS, TODAY-based countdowns, and EOMONTH — what each one is actually for, and the mistakes that quietly break them.
To calculate the number of days between two dates in Excel, use
=DAYS(end_date,start_date), which returns a plain day count without the
formatting quirks of direct subtraction. To count down to a future date, use
=B2-TODAY(), and to find how many days are in a given month, use
=DAY(EOMONTH(A2,0)).
What is a "days" formula, and what are the options?
"Calculate days" in Excel usually maps to one of three distinct questions, each with its own dedicated tool.
- DAYS function (
=DAYS(B2,A2)) returns the total number of calendar days between a start and end date, as a clean number, no reformatting required. - TODAY()-based countdowns (
=B2-TODAY()) measure days remaining until, or days elapsed since, a fixed date, and recalculate automatically every time the sheet opens. - EOMONTH (
=DAY(EOMONTH(A2,0))) answers a different question entirely — not the gap between two dates, but how many days a specific calendar month actually contains.
If the goal is business days only, rather than every calendar day, NETWORKDAYS handles that separately by excluding weekends and optional holidays from the count.
Why the right days formula matters
Each of these formulas answers a slightly different question, and mixing them up produces a number that looks plausible but answers the wrong thing.
- Countdown trackers. A "days until renewal" column built with a fixed subtraction instead of
TODAY()will freeze at whatever value it had on the day it was written. - Billing cycles. Prorating a subscription or rent charge depends on knowing the exact number of days in that specific month, which varies between 28 and 31.
- Aging reports. Days-elapsed-since calculations that use the wrong reference date will misclassify how overdue an invoice or task actually is.
- Interest and accrual calculations. Financial models that assume a 30-day month (DAYS360) will diverge from ones counting actual calendar days, so the two shouldn't be mixed in the same sheet.
Step-by-step: calculating days in Excel
Method 1: Days between two dates (DAYS function)
- Enter both dates in separate cells. Make sure both are stored as real dates, not text — for example, A2 as the start date and B2 as the end date.
-
Type the DAYS formula, end date first.
=DAYS(B2,A2)
- Press Enter. The result is a plain number of calendar days, without the date-formatting issue that direct subtraction sometimes carries over.
Method 2: Days remaining or days elapsed (TODAY())
-
For days remaining until a future date:
=B2-TODAY()
-
For days elapsed since a past date:
=TODAY()-A2
- Format the result cell as Number. Since TODAY() is a date value, the subtraction result can inherit Date formatting, so switch the cell to Number or General.
Method 3: Number of days in a specific month (EOMONTH)
-
Use EOMONTH to find the last day of the month.
=EOMONTH(A2,0)
-
Wrap it in DAY to get just the day count.
=DAY(EOMONTH(A2,0))
- Press Enter. The result is the total number of days in the month that contains the date in A2, whether that's 28, 29, 30, or 31.
Common mistakes with days formulas
1. Reversing the arguments in DAYS
DAYS expects the end date first and the start date second, like =DAYS(B2,A2);
swapping the order returns a negative number instead of the expected count.
2. Hardcoding TODAY() instead of using the function
Typing today's literal date into a cell freezes the countdown at that value, while
=TODAY() recalculates automatically every time the workbook opens, which is
what a "days remaining" column actually needs.
3. Forgetting the second argument in EOMONTH
EOMONTH(A2,0) returns the last day of the current month, but changing that
second argument shifts the target month — for instance, 1 moves one month
forward and -1 moves one month back, so leaving it out or guessing at it
points the formula at the wrong month entirely.
4. Mixing DAYS360 into a calendar-accurate report
DAYS360 assumes every month has 30 days, which is useful for certain accounting standards but will disagree with DAYS or plain subtraction on any month that isn't exactly 30 days long, so the two shouldn't be combined in the same total.
Real-world formula examples
A few common scenarios and the formula each one needs.
DAYS vs TODAY() vs EOMONTH
A side-by-side look at how the three approaches compare in Excel.
| Factor | DAYS function | TODAY()-based | EOMONTH + DAY |
|---|---|---|---|
| Answers | Gap between two fixed dates | Gap to/from the current date | Length of a given month |
| Updates automatically | No | Yes | No |
| Needs cell reformatting | Rarely | Sometimes | Rarely |
| Best for | Contract length, tenure spans | Countdowns, aging reports | Proration, billing cycles |
Skip the spreadsheet: free days calculator
If the only thing needed is a day count, the Rebrixe Days Calculator works entirely in your browser: enter a start and end date, get the exact number of days instantly. No formulas, no account, no data sent anywhere.