To subtract calendar days from a date in Excel, subtract a number from the date cell: if the date is in A2, enter =A2-7 to return the date seven days earlier. For working days rather than calendar days, use WORKDAY; the right choice depends on whether weekends and holidays should count.
Subtract calendar days with a simple formula
Excel treats a valid date as a number, so ordinary subtraction is usually all you need. With the date in A2, enter =A2-7 in another cell to move back seven calendar days. The day you subtract is a count of days, not a number of weeks or business days. To move forward, use addition instead.
For example, if A2 contains April 18, 2026, then =A2-10 returns April 8, 2026. The result is a date value that Excel can use in other calculations, not merely text that looks like a date. Change the number to subtract a different interval: 1 means one day, 30 means 30 days, and 365 means 365 days.
With a date in A2 and the number of days to subtract in B2, enter =A2-B2. This is useful when each row has a different offset. For instance, a date of April 18, 2026, in A2 and 10 in B2 produce April 8, 2026. Copy the formula down to calculate results for other rows; Excel adjusts the row references as it fills the formula.
- Subtract a fixed number of calendar days:
=A2-7 - Subtract a per-row amount stored in another cell:
=A2-B2 - Add days instead of subtracting: change the minus sign to a plus sign.
How Excel date arithmetic works
In Excel, dates are stored as serial numbers: each whole-number step represents one day. A date cell may display as April 18, 2026, but its underlying value is a number. Subtracting 7 reduces that number by seven, which moves the displayed date back seven days. This is why a basic subtraction formula works across month ends and year ends without special cases.
The date display is controlled separately from the underlying value. If a formula result appears as a number such as 46130 rather than a familiar date, the calculation may still be correct; the result cell may simply be formatted as General or Number. Select the result cells and choose a date format from Home > Number Format. You can also press Ctrl+1 on Windows to open Format Cells; on Mac, use Command+1.
Excel workbooks can use the 1900 or 1904 date system. Most current Windows workbooks use the 1900 system, while the 1904 system is found in some older workbooks. The same date can have serial values that differ by 1,462 between the systems. Avoid changing a workbook’s date system casually: it can shift existing dates. The difference does not change the basic subtraction rule, but it matters when copying date values between workbooks that use different systems.
Subtract a different number of days in each row
When a schedule contains an original date and a separate offset, use a cell reference for the offset instead of editing the formula for every row. For example, put start dates in A2:A20 and the number of days to move back in B2:B20. In C2, enter =A2-B2, then fill the formula down column C. Each row uses its own date and offset.
For a fixed offset that should remain the same throughout a table, you can store the number in one cell and lock that reference before copying. If the fixed number is in B1 and each row’s date is in column A, enter =A2-$B$1 in C2. The dollar signs keep the reference to B1 fixed as the formula is copied down. If B1 contains 7 and A2 is April 18, 2026, the result is April 11, 2026.
Use a numeric value for the offset. A value that looks like a number but is stored as text may cause an error or unexpected behavior, depending on the data. If the dates come from an imported file, confirm they are real Excel dates rather than text strings before filling formulas down a large list.
Use TODAY for a date relative to today
For a rolling calculation based on the current date, use the TODAY function. With no other data required, enter =TODAY()-30 to calculate the date 30 calendar days before today. The result updates when Excel recalculates the workbook, so it is useful for a rolling cutoff, such as identifying records older than a month.
If you want to compare a stored date with that cutoff, keep the dates as date values and use a separate comparison or filter. Remember that TODAY is a volatile function: Excel can recalculate it when the workbook recalculates. A report that must preserve the date used when it was prepared may need a fixed date entered as a value instead of a moving TODAY-based result.
When a calculation should use the current date and time, Excel also has NOW, but subtracting a whole number from NOW retains the time component. For a date-only calculation, TODAY is generally clearer. If the source value includes a time, see the time section below before deciding whether to keep or remove it.
Subtract working days and account for holidays
Calendar subtraction counts every day, including Saturdays, Sundays, and holidays. For a deadline or processing schedule that counts weekdays, use WORKDAY. With a start date in A2 and holiday dates in F2:F10, enter =WORKDAY(A2,-5,$F$2:$F$10) to move back five working days, excluding Saturday, Sunday, and the listed holidays.
For a concrete example, if A2 is Friday, July 17, 2026, and the holiday list contains no dates in the interval, five working days earlier is Friday, July 10, 2026. The count goes back through Thursday, July 16; Wednesday, July 15; Tuesday, July 14; Monday, July 13; and Friday, July 10. Saturday and Sunday are skipped. Add actual holiday dates to F2:F10 if they should also be skipped; blank cells in the referenced range are ignored.
By default, WORKDAY assumes a Saturday-Sunday weekend. For a different recurring weekend pattern, use WORKDAY.INTL. With a start date in A2, five working days to subtract, and holidays in F2:F10, =WORKDAY.INTL(A2,-5,1,$F$2:$F$10) specifies the standard Saturday-Sunday weekend using weekend code 1. Other weekend codes or a seven-character weekend pattern can represent different days off. Check the function’s weekend argument carefully, especially for a work schedule with a nonstandard weekend.
WORKDAY returns a date serial, so format its result as a date if Excel displays a number. These functions are intended for working-day schedules; they do not know your organization’s leave rules, shift calendar, or regional public holidays unless you provide the relevant dates and weekend pattern.
Calendar days are not the same as months
Subtracting 30 days is not the same as moving back one calendar month. Months have different lengths, so subtracting 30 can land on a different day than a month-based calculation. To move back one month, use EDATE instead. With a date in A2, enter =EDATE(A2,-1). For example, one month before March 31, 2026, is February 28, 2026, because February has no 31st day.
Choose the operation that matches the wording of the task. Use direct subtraction for a specified number of elapsed days, such as 14 days before a delivery date. Use EDATE for monthly intervals, such as one month before a renewal date. For years, consider whether the rule means a fixed number of days or the same calendar date in an earlier year; leap years can make those outcomes differ.
For a date built from separate year, month, and day values, use the DATE function rather than typing a locale-sensitive text string into a formula. With the year in A2, month in B2, and day in C2, enter =DATE(A2,B2,C2)-7 to create that date and subtract seven days. DATE also normalizes overflowing month or day values, so verify that the source components are sensible if they come from imported data.
Dates that contain times
Excel date-time values have a whole-number part for the date and a fractional part for the time. Subtracting 7 from a cell that contains both a date and time moves the value back seven days while preserving the time of day. For example, if A2 is April 18, 2026, at 3:00 p.m., then with that date-time value in A2, =A2-7 returns April 11, 2026, at 3:00 p.m.
If you need the result at midnight, use INT to remove the fractional time portion before subtracting. With a date-time value in A2, enter =INT(A2)-7. Format the result as a date so the time is not displayed. This distinction matters for comparisons: a value at 3:00 p.m. is later than midnight on the same displayed date, even if the cell is formatted to show only the date.
Common errors and how to fix them
- The result is a serial number. The calculation may be fine. Apply a date format using Home > Number Format.
- The result shows ######. The column may be too narrow, or the result may be a negative date that the workbook’s date system cannot display as a normal date. Widen the column first; then check whether the calculation went before Excel’s supported date range.
- The formula returns #VALUE!. Check whether the source date or offset is stored as text, contains stray spaces, or is not a valid date or number. Imported date text can also be interpreted differently under different regional settings.
- The displayed date looks unchanged. Confirm that the formula cell is not displaying a stale value and that calculation is enabled. Also check whether the offset cell contains zero or text rather than the intended number.
- A deadline lands on a weekend. Direct subtraction counts calendar days by design. Use WORKDAY or WORKDAY.INTL if the goal is a working-day deadline.
Dates before January 1, 1900, are not handled as ordinary positive dates in Excel’s default 1900 date system, and negative date results can display as hashes. If a calculation crosses that boundary, do not assume changing the number format will make it a supported date. For ordinary modern scheduling, the more common cause of hashes is simply a narrow column.
Which method should you choose?
Use simple subtraction when the instruction specifies a number of calendar days. It is direct, easy to audit, and works naturally across months and years. Use WORKDAY when weekends should not count, and add a holiday range when those dates must be excluded. Use WORKDAY.INTL when the weekend does not follow Saturday and Sunday. Use EDATE when the interval is in whole months rather than a fixed number of days.
Before sharing a result, check the input type, the intended counting rule, and the displayed format. A good check is to test a boundary case, such as a date near the end of a month or a deadline immediately before a weekend. That catches many misunderstandings that a formula can’t resolve on its own: Excel follows the calculation you specify, not the informal meaning someone may intend by “a week earlier” or “one month before.”
Frequently Asked Questions
How do I subtract 30 days from a date in Excel?
With the date in A2, enter =A2-30. Format the result cell as a date if it appears as a number.
How do I subtract days from today in Excel?
Enter =TODAY()-7 to return the date seven calendar days before today. The answer updates when Excel recalculates.
How do I subtract weekdays instead of calendar days?
With a start date in A2, use =WORKDAY(A2,-5) to move back five weekdays, excluding Saturday and Sunday. Add a holiday range as the third argument if needed.
Why does subtracting days show a number?
Excel stores dates as numbers. Select the result cell and choose a date format from Home > Number Format to display it as a calendar date.
Does subtracting seven days keep the time?
Yes. If the source contains a time, direct subtraction preserves it. To remove the time portion first, use INT on the source date-time value before subtracting.
Related
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
- Data Validation Studio: Excel Rules, Setup, and Limits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Remove Spaces in Excel: The Shortcut and Right MethodTo remove every ordinary space from selected cells in Excel for Microsoft 365 on Windows, select the cells,…
- INDIRECT Excel Function: Examples, Uses, and LimitsIn Excel, INDIRECT converts text into a live cell or range reference; if A2 contains B5, =INDIRECT(A2) returns…
- Excel Percentage Difference Formula: How to CalculateFor the percentage difference between two nonnegative values in Excel, use =ABS(B2-A2)/AVERAGE(A2,B2), then format the result as a…
- Excel QR Code Formula: Create QR Codes from Cell DataExcel has no built-in QR-code function, but in Microsoft 365 for Windows you can display a QR code…
- Excel Attendance List: Setup, Formulas, and Practical TipsAn Excel attendance list is a worksheet that records who attended each class, meeting, or event and makes…
- Excel Time Plus Minutes: Formulas and FixesTo add minutes to an Excel time, add the minutes divided by 1,440: with a time in A2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.