To subtract days from a date in Excel, use a minus sign: if A2 contains a date and B2 contains the number of days to subtract, enter =A2-B2 in another cell. For a fixed interval of seven days, use =A2-7. Excel returns the earlier date, provided the starting value is a real date and the result cell is formatted to display it as one.
The basic date-minus-days formula
Excel treats dates as sequential numbers, so subtracting a number from a date moves backward by that many calendar days. A date in A2 minus 1 is the previous day; minus 7 is one week earlier. The formula does not need a special date-subtraction function: the ordinary minus operator does the job.
With the starting date in A2 and the number of days in B2, enter =A2-B2 in C2. If A2 is June 15, 2026, and B2 is 10, the result is June 5, 2026. This version is useful when the subtraction amount may change, because you can edit B2 without rewriting the formula. If you always want the same interval, a fixed number is simpler.
For example, when A2 contains the date to adjust and the goal is always to go back 30 days, enter =A2-30. The number can be positive, zero, or a decimal, although whole numbers are the usual choice for day-based work. Subtracting zero leaves the date unchanged.
How Excel stores dates
Excel stores a recognized date as a serial value: whole-number steps represent days, and the displayed date is a format applied to that value. This is why date arithmetic works. If June 15 is represented internally by a number, subtracting 10 reduces that serial value by 10, which corresponds to June 5.
This also explains a common surprise: a correct formula may appear to return a number such as 46200 instead of a readable date. Often, the calculation is fine and only the result cell’s number format needs changing. Conversely, a cell can look like a date while containing text; text may not behave as expected in date arithmetic.
When a date is entered directly into a formula, the DATE function avoids ambiguity in how a typed date is interpreted. With no input cells required and the intended starting date of June 15, 2026, enter =DATE(2026,6,15)-10. The result is June 5, 2026. DATE takes year, month, and day as separate numeric arguments, so it is especially clear in a reusable formula.
Choosing between a fixed number and a cell reference
Use a literal number such as 7 when the interval is a permanent part of the calculation. Use a cell reference such as B2 when people may need to change the number, or when each row has a different interval. A reference makes a worksheet easier to maintain: one column can hold dates, another can hold offsets, and a formula in the results column can be filled down.
With starting dates in A2:A100 and the number of days to subtract for each row in B2:B100, enter =A2-B2 in C2, then copy or fill the formula down the column. In row 3, Excel adjusts the references to A3 and B3 automatically. If you want every row to subtract the same value stored in B1, lock that reference before filling down: with dates in A2:A100 and a shared offset in B1, use =A2-$B$1 in C2. The dollar signs keep the reference to B1 fixed as the formula is copied.
Be deliberate about the cell type for the offset. If B2 contains the text seven rather than the number 7, the formula can produce a value error. Enter a numeric value, and consider adding a clear column heading such as Days to subtract so someone reading the sheet knows what the number means.
Worked examples and date boundaries
Suppose a shipping worksheet lists an estimated arrival date in A2 and you want to find a date five days earlier for a preparation deadline. With the arrival date in A2 and the five-day lead time in B2, enter =A2-B2 in C2. If the arrival is July 20, 2026, and B2 is 5, the calculated deadline is July 15, 2026. The calculation crosses month and year boundaries automatically; you do not have to account manually for months with different lengths.
For a calculation that crosses into the previous year, if A2 contains January 3, 2027 and B2 contains 5, the same formula =A2-B2 returns December 29, 2026. In a leap year, subtracting across February also follows the calendar correctly. If A2 is March 1, 2024 and B2 is 1, the result is February 29, 2024.
Subtraction can also create a date before the dates Excel can display in its standard date system. A negative serial result may show as a row of number signs or as an unexpected value, depending on the workbook and format. In that situation, check whether the requested earlier date is valid for the workbook’s date range rather than assuming the minus sign is wrong.
Calendar days versus working days
The basic formula subtracts calendar days, not business days. If a Friday is reduced by 1, the result is Thursday; subtracting 3 gives Tuesday. A weekend or holiday does not cause Excel to skip ahead or back. This is the right behavior for questions such as what date was 14 days before an event, or for an interval defined as consecutive calendar days.
If you need to count backward by working days and skip Saturday and Sunday, use WORKDAY instead. With a starting date in A2 and the positive number of workdays to go back in B2, enter =WORKDAY(A2,-B2). For example, if A2 is Monday, June 15, 2026 and B2 is 3, the result is Wednesday, June 10, 2026: Friday, Thursday, and Wednesday are the three preceding workdays.
WORKDAY can also exclude a supplied list of holidays. With the start date in A2, workday count in B2, and holiday dates in D2:D10, enter =WORKDAY(A2,-B2,D2:D10). The holiday cells must contain genuine Excel dates. WORKDAY uses Saturday and Sunday as the weekend by default; for a different weekend pattern, Excel provides WORKDAY.INTL, which lets you specify weekend days. Use these functions only when the task really means working days. They do not replace the simpler subtraction formula for calendar-day intervals.
Formatting and filling the formula down
If the output appears as a serial number, select the result cell or range and choose Home > Number Format dropdown > Short Date. You can choose Long Date or another suitable date format from the same dropdown. Formatting changes how a value is displayed; it does not change the underlying calculation. If a date is displayed with a time as well, use a date-only format to hide the time portion.
To apply the same calculation to multiple rows, enter the formula in the first result row and fill it down using the fill handle at the lower-right corner of the selected cell. Double-clicking the fill handle often copies the formula down alongside a neighboring column of data. Check the last few rows afterward, especially if the neighboring data has gaps, because Excel uses adjacent entries to decide how far to fill.
In Excel for Microsoft 365 on Windows, you can also select the formula cell and the target range below it, then use Home > Fill > Down. On Excel for Mac, the same basic formula and date formats are available, though the ribbon layout and some keyboard shortcuts differ slightly. In Excel for the web, date subtraction works the same way; the ribbon may present formatting choices differently from the desktop app.
Common problems and practical checks
- The result is a number: Apply a date number format. A serial number usually means Excel calculated a date but is displaying its stored value.
- The result is an error: Check whether the starting date or day count is stored as text. Re-enter the date in a recognized format or convert the source data to actual date values, and make sure the offset is numeric.
- The answer is one day off: Confirm whether the starting date should count as day zero or day one. A date minus 7 means seven days earlier, not including the starting date as one of the seven days.
- The formula changes unexpectedly when copied: Relative references adjust by row. Use an absolute reference such as $B$1 for a shared offset that must remain fixed.
- The result displays hashes: Widen the column first; if that does not help, check whether the result is outside Excel’s supported date range or the cell uses an unsuitable format.
Also check the order of the references. =A2-B2 means date minus day count; =B2-A2 reverses the operation and is generally not a meaningful date calculation when B2 is an ordinary small number. If a formula returns a date far from what you expect, inspect the actual values in the referenced cells rather than relying only on their appearance. Imported data can include leading spaces, text dates, or hidden time values.
Frequently Asked Questions
How do I subtract 30 days from today in Excel?
With no input cells needed, enter =TODAY()-30. TODAY returns the current date, and the result is the date 30 calendar days earlier. Because TODAY updates when the workbook recalculates, the answer changes over time. Use a fixed date in a cell instead if you need a result that will not move.
How do I subtract days from a date in another cell?
If A2 contains the date and B2 contains the number of days, enter =A2-B2 in the result cell. Format the result as a date if Excel displays a serial number. This method works for positive whole-number day counts and naturally handles month, year, and leap-year transitions.
Can Excel subtract weekends or holidays automatically?
The minus formula counts calendar days and does not skip any dates. To count backward by Monday-to-Friday workdays, use WORKDAY with a negative day count. Add a holiday-date range as its optional third argument when those dates should be excluded too.
Why does subtracting days show a number instead of a date?
Excel may be showing the underlying serial value with a General or Number format. Select the result and apply a date format through Home > Number Format dropdown. If the displayed value is still wrong after formatting, verify that the starting value is a real date and the offset is a number.
Does Excel count the starting date as one of the days?
No. In =A2-7, Excel moves seven calendar-day steps backward from the date in A2. The starting date is the reference point, not day one of an inclusive count. If a policy or schedule uses inclusive counting, clarify that rule and adjust the interval accordingly.
Related
- Daily Cash Flow Report Template in Excel: Setup and Use
- Customizable Invoice Template in Excel: How to Make One
- Daily Attendance Register Template Excel: Setup and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel: Test If a Cell Contains Specific TextIn Excel for Microsoft 365, test whether a cell contains specific text with =ISNUMBER(SEARCH("apple",A2)). It returns TRUE when…
- Excel: Round a Percentage to a Whole Number in CellsIf A2 contains a percentage such as 12.5%, use =ROUND(A2*100,0)/100 to round it to 13% as a numeric…
- CLEAN TRIM Formula in Excel: Remove Extra SpacesIn Excel for Microsoft 365, use =TRIM(CLEAN(A2)) to remove many nonprinting control characters from a cell and trim…
- Basic Invoice Template Excel: Setup and FormulasA basic invoice template in Excel can be made with invoice and customer details, a line-item table, and…
- Excel Working Hours Formula: Calculate Time WorkedIf A2 contains a shift start time and B2 its end time on the same day, enter =(B2-A2)*24…
- Excel Formula Bar Not Showing Text? Fixes That WorkIf Excel’s formula bar is missing, turn it back on with View > Show > Formula Bar in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.