If the end date is in B2 and the start date is in A2, enter =DAYS(B2,A2); the end date must come first. If the result is an error or looks wrong, check that both cells contain real Excel dates and that the result cell is formatted as a number.
What the DAYS formula calculates
DAYS returns the number of days between two dates. Its argument order is easy to reverse: the first argument is the later, or end, date, and the second is the earlier, or start, date. The function calculates end date minus start date; it does not automatically count both endpoints as whole days.
For example, with May 1, 2026, in A2 and May 10, 2026, in B2, the result is 9. That is the elapsed number of days from the start of May 1 to the start of May 10. If your question is “How many calendar dates are included from May 1 through May 10?”, the answer is 10, because both the first and last dates count. That is a different counting convention, not an error in DAYS.
Excel stores ordinary dates as serial numbers, with time, if present, represented as a fraction of a day. A date that looks correct on screen may instead be text, and a cell that contains a valid date may display an unexpected result because of its number format. Those are the main reasons a DAYS formula appears not to work.
Check the argument order and the expected count
With the start date in A2 and the end date in B2, use =DAYS(B2,A2). The result is positive when B2 is later than A2, zero when the dates are the same, and negative when B2 is earlier. A negative result can be correct if the dates are intentionally in reverse chronological order; otherwise, check which cell holds each date.
If the task is to count both the start and end dates, use a one-day adjustment. With the same layout—start date in A2 and end date in B2—the inclusive count is =DAYS(B2,A2)+1. For May 1 through May 10, 2026, this returns 10. Confirm that this is the convention you need: elapsed time, date ranges on forms, and attendance counts do not always use the same rule.
A simple subtraction can produce the same elapsed-day result. With the start date in A2 and the end date in B2, enter =B2-A2. This can be useful as a quick diagnostic. If both this calculation and DAYS return an unexpected result, focus on the cell values, the date order, or the result format rather than on the function name.
Look for text dates instead of real dates
Excel can display text that resembles a date without treating it as a date value. This often happens after copying data from a website, a CSV file, or another system. Left alignment can be a clue because text is often left-aligned by default, but alignment alone is not proof: formatting and workbook settings can change how cells look.
To check A2, with the suspected date in A2, enter =ISNUMBER(A2) in an empty cell. A real Excel date normally returns TRUE because it is stored as a number; a text date normally returns FALSE. Repeat the check for the other date cell. This test checks the underlying value, not whether the entry looks like a date.
For one recognizable text date, a helper formula can attempt conversion. With the text date in A2, enter =DATEVALUE(A2) in another cell, then format the result as a date. DATEVALUE interprets text according to Excel’s date and regional settings, so an ambiguous entry such as 04/05/2026 may mean April 5 or May 4 depending on the settings. Check the intended date before using converted values in calculations.
For a column of imported dates, you can also use Data > Text to Columns on Excel for Microsoft 365 for Windows. Select the date column, choose the delimited option, continue through the wizard, and choose the correct date order in the date-format step before finishing. Do not choose a month-day-year order by guesswork if the source uses day-month-year. Keep a copy of the original data until you have checked the converted dates.
Fix errors and results that look like dates
If DAYS returns #VALUE!, at least one argument may be text Excel cannot interpret as a date, or may contain another unsuitable value. Check both referenced cells individually, remove stray spaces or punctuation from imported entries, and verify that the date is valid. A blank cell can also lead to a result that is technically calculated but not meaningful, so decide how your sheet should handle missing dates.
If the formula appears in the cell instead of a result, first check for a leading apostrophe or an accidental space before the equals sign. Also check whether Show Formulas is enabled: press Ctrl+` on Windows to toggle it. On a Mac, the equivalent shortcut is Control+`; keyboard layouts may require a different key combination to type the grave accent. If formulas still are not recalculating, check Formulas > Calculation Options > Automatic on Windows. A cell formatted as Text can also cause a newly entered formula to display literally; change it to General, then re-enter the formula.
If the result is a date such as January 9, 1900, rather than the number 9, the calculation may be right but the result cell is using a date format. Select the result cell and choose Home > Number Format > General or Number. Conversely, if a date input is displayed as a large number, the value may still be valid: change that input cell’s number format to a date format to make it readable.
For a deliberate error message when either input is missing or invalid, add error handling. With the start date in A2 and the end date in B2, enter =IFERROR(DAYS(B2,A2),"Check dates"). This displays “Check dates” when DAYS produces an error, but it does not repair the inputs or catch every logically wrong date order. A formula can hide a symptom, so validate the source values as well.
Account for times and working days
A cell formatted to show only a date can still contain a time. For example, one value might represent midnight and another might represent noon. That difference can affect elapsed-time calculations even though the displayed dates appear identical. If you need whole calendar days and want to discard any time portions, use INT to remove the fractional parts before subtracting.
With the start date and time in A2 and the end date and time in B2, enter =INT(B2)-INT(A2) to count the change in calendar dates. This does not measure completed 24-hour periods. For example, from 11 p.m. on one day to 1 a.m. the next day, the calendar-date difference is 1 even though only two hours have elapsed. Choose the calculation that matches the question being answered.
DAYS counts all calendar days, including weekends and holidays. If the task is to count Monday-through-Friday workdays, use NETWORKDAYS instead. With the start date in A2 and the end date in B2, enter =NETWORKDAYS(A2,B2). For June 1 through June 5, 2026, this returns 5 because those dates run from Monday to Friday.
If holidays are listed in D2:D10, use that range as the optional holiday argument. With the start date in A2, the end date in B2, and holiday dates in D2:D10, enter =NETWORKDAYS(A2,B2,$D$2:$D$10). Excel excludes listed holidays that fall on weekdays, as well as weekend days. Make sure the holiday entries are real dates, and note that this function uses Saturday and Sunday as the weekend by default; a different work schedule calls for NETWORKDAYS.INTL.
Choose the right date function
DAYS is a clear choice when you want the difference between two dates in days and prefer to state the end date first. Subtracting dates is a concise alternative when the cells already contain valid date values. These approaches do not count both endpoints unless you add one, and neither automatically removes weekends or holidays.
DATEDIF is useful when you need a difference in a specified unit, such as complete years, months, or days. With the earlier date in A2 and the later date in B2, enter =DATEDIF(A2,B2,"d") to return the day difference. DATEDIF uses the start date first, unlike DAYS. For a straightforward day count, DAYS or subtraction is usually easier to read; do not swap argument orders just because you have changed functions.
For work schedules, NETWORKDAYS is usually more suitable than either DAYS or DATEDIF because it excludes weekends and can exclude a supplied holiday list. Think about what “days” means in your task before changing functions: elapsed calendar days, inclusive dates, full 24-hour periods, and working days are four distinct measures.
A practical troubleshooting sequence
When a DAYS formula does not return the expected answer, work through these checks in order. It is usually faster to identify the specific cause than to replace the formula repeatedly.
- Confirm the intended start and end dates, then check that the end-date cell is the first DAYS argument.
- Use ISNUMBER on both inputs to find dates stored as text.
- Check for impossible dates, blanks, extra characters, and ambiguous date formats in imported data.
- Decide whether the answer should include both endpoints or exclude weekends and holidays.
- Set the result cell to General or Number if it displays as a date.
- Check that the formula is not being shown as text and that calculation mode is Automatic.
- Compare the result with a small, known date range to verify the intended counting rule.
For example, May 1 to May 10 should produce 9 elapsed days, or 10 inclusive calendar dates. If Excel produces 9 but a report expects 10, the formula is working; the report is using an inclusive count. If it produces an error, investigate the inputs. If it produces 9 but displays as a date, adjust the result format. Separating these possibilities keeps a formatting issue from being mistaken for a calculation problem.
Frequently Asked Questions
Why does DAYS return a negative number?
If the start date is later than the end date, the result is negative. Check the dates and argument order; if the order is intentional, a negative result correctly indicates that the dates run backward.
Does DAYS count the start and end dates?
No. It returns the difference between the dates, so May 1 to May 10 is 9 elapsed days. Add 1 when the task explicitly requires counting both endpoint dates.
Why does Excel show a date instead of a number?
The result cell is likely formatted as a date. Change its number format to General or Number; this changes the display, not the underlying calculation.
Can DAYS calculate between dates stored as text?
Only if Excel can recognize and interpret the text. Imported or ambiguous text may cause an error or an unintended date, so check the values and convert them carefully before relying on the result.
Should I use DAYS or NETWORKDAYS?
Use DAYS for calendar-day differences. Use NETWORKDAYS when you need weekdays only and, optionally, want to exclude holidays listed in the workbook.
Related
- Count Numbers vs Count in Excel PivotTables
- Copy Paste in Excel: Keep Column Widths When Pasting
- Copy an Entire Excel Spreadsheet to a New File
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Dropdown Lists: Create, Edit, and Fix ThemTo make a dropdown list in Excel for Microsoft 365 on Windows, select the cells, choose Data >…
- Excel Not Working Right? Fix Common ProblemsIf Excel is not working right, first identify whether the trouble affects one workbook or the whole app,…
- Excel Text Going Out of Cell? Fix Overflow and WrappingIn Excel, text usually spills into the cell to its right when that cell is empty; if the…
- Remove Spill Error in Excel: Causes and FixesTo remove a #SPILL! error in Excel, select the error cell, inspect the outlined spill range, and clear…
- LEFT Function Not Working in Excel? Fix ItIf LEFT is not returning the characters you expect, check the formula, the character count, and whether the…
- COUNTIFS Not Working With Dates? Fix the CriteriaYes—COUNTIFS often misses dates because its criteria are written as text, the cells contain text rather than real…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.