Excel Add Subtract Weeks to Date: How-To
To add or subtract weeks from a date in Excel, multiply the number of weeks by 7 and add or subtract that many days using a simple formula like =A1+4*7 or =A1-4*7. This works because Excel stores dates as serial numbers where each whole number equals one day, so adding 28 to a date moves it exactly 4 weeks forward.
Why This Works: Excel Dates Are Numbers
Excel treats every date as a serial number starting from January 1, 1900 (serial 1). For example, January 1, 2025 is serial 45658. Because one day equals 1 in this system, adding 7 to any date adds exactly one week. This is the foundation for all date arithmetic in Excel—you are not manipulating “dates” as text, but performing simple addition and subtraction on numbers that are formatted to look like dates.
This design means you can add or subtract any number of days directly, and weeks are just a convenient multiple of 7. The same logic applies to months and years, but those require special functions because month lengths vary. Weeks, however, are always exactly 7 days, so the arithmetic is straightforward and reliable.
The Basic Formula: Adding or Subtracting Weeks
If you have a date in cell A1 and you want to add 3 weeks, you would write:
=A1+3*7
To subtract 3 weeks:
=A1-3*7
The multiplication 3*7 converts weeks to days. You can replace 3 with any number, a cell reference (e.g., =A1+B1*7 where B1 holds the number of weeks), or even a negative number (e.g., =A1-2*7 for subtracting 2 weeks).
If you prefer, you can also use the DATE function for clarity when you need to build a date from separate year, month, and day components, but for adding weeks to an existing date, simple arithmetic is the most direct and fastest method.
Using the EDATE Function for Months (Not Weeks)
A common confusion is using EDATE for weeks. EDATE adds or subtracts months, not weeks. For example, =EDATE(A1,2) gives the date two months later. It does not work for weeks because weeks are not a calendar unit like months—they are a fixed number of days. If you need to add months, use EDATE; if you need weeks, use the multiplication method above.
Some users mistakenly try =EDATE(A1, 4) thinking it adds 4 weeks, but it actually adds 4 months. This is a frequent source of errors in spreadsheet models. Always match the function to the unit you intend to change.
Handling the Result: Formatting and Display
When you enter a formula like =A1+4*7, Excel will automatically display the result as a date if the cell is formatted as a date. However, if the result appears as a number (like 45686), the cell is formatted as a general number. To fix this, select the cell, go to the Home tab, and choose Short Date or Long Date from the Number Format drop-down. You can also press Ctrl+Shift+# to apply the default date format.
If you want to see the day of the week along with the date, use a custom format like dddd, mmmm dd, yyyy (e.g., Monday, January 06, 2025). This is helpful for project timelines and scheduling.
Common Mistakes and How to Avoid Them
- Forgetting to multiply by 7: Writing
=A1+4adds 4 days, not 4 weeks. Always multiply the week count by 7. - Using EDATE for weeks: As noted, EDATE works on months only. Use simple arithmetic for weeks.
- Text dates: If your “date” is actually text (e.g., “2025-01-01”), Excel may not recognize it as a date. Use
DATEVALUEto convert it first, or re-enter as a proper date. - Negative results: Subtracting weeks from a very early date (like 1900-01-01) can produce a negative serial number, which Excel cannot display as a date. Ensure your starting date is far enough from the minimum.
Another subtle issue: if you add weeks to a date that falls on a Sunday, the result will also be a Sunday, because you are adding exact multiples of 7 days. This is expected and often desired, but be aware if you need to adjust for business days.
Alternatives: Using WORKDAY for Business Weeks
If you need to add “weeks” but only count Monday through Friday (e.g., a project deadline that skips weekends), use the WORKDAY function. For example, to add 2 business weeks (10 business days) to a date in A1:
=WORKDAY(A1, 10)
This returns the date that is 10 working days later, excluding weekends (and optionally holidays if you add a third argument). Note that this is not the same as adding 2 calendar weeks—it gives the date after 10 business days, which could be 12 or more calendar days later.
For subtracting business weeks, use a negative number: =WORKDAY(A1, -10). This is useful for calculating start dates from deadlines.
Frequently Asked Questions
Can I add weeks to a date using the DATE function?
Yes, but it is more verbose. For example, to add 3 weeks to a date in A1, you could use =DATE(YEAR(A1), MONTH(A1), DAY(A1)+21). This works because the DAY argument can exceed the number of days in a month—Excel rolls over to the next month automatically. However, it is longer and less readable than =A1+3*7.
What if I want to add a fractional number of weeks, like 1.5 weeks?
Multiply the fraction by 7. For 1.5 weeks, use =A1+1.5*7, which equals adding 10.5 days. Excel will display the date with a time component (e.g., 12:00 PM) if the result has a fractional part. You can format the cell to show only the date if you want to ignore the time.
How do I subtract weeks and keep the result as a date?
Simply use a negative number or the minus sign. For example, =A1-2*7 subtracts 14 days. The result will be a date as long as the cell is formatted as a date. If you see a number, apply a date format as described above.
Does this work in Google Sheets too?
Yes, Google Sheets uses the same serial number system for dates, so the identical formulas work. The only difference is that Google Sheets might require you to set the cell format to Date manually, but the arithmetic is the same.
Why does my formula return a number like 45658 instead of a date?
This happens when the cell is formatted as a number. Select the cell, go to the Home tab, and choose a date format. Alternatively, you can use the TEXT function to force a date string, but it is better to keep the cell as a real date for further calculations.
Can I add weeks to a date that is in a different cell using a cell reference?
Absolutely. If your starting date is in A1 and the number of weeks is in B1, use =A1+B1*7. This makes it easy to change the number of weeks without editing the formula.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.