Excel Add Subtract Years Months Days Weeks to Date

This page explains how to add or subtract years, months, days, and weeks to a date in Microsoft Excel, covering the EDATE function, the DATE function, and simple arithmetic with days. You enter a start date and the number of years, months, days, or weeks to adjust, and you get the resulting date. The core methods work in Excel 2010 through Microsoft 365, with the same formulas applying to Google Sheets.

Formula

The general formula for adding or subtracting a combination of years, months, days, and weeks to a date is:

=DATE(YEAR(start_date) + years, MONTH(start_date) + months, DAY(start_date) + days + weeks*7)

Where:

• start_date – the original date (as a cell reference or a date entered with DATE(2024,1,15)).
• years – number of years to add (use a negative number to subtract).
• months – number of months to add (use negative to subtract).
• days – number of days to add (use negative to subtract).
• weeks – number of weeks to add; multiplied by 7 to convert to days.

Excel stores dates as serial numbers (1 = January 1, 1900), so adding days is simple arithmetic. For years and months, the DATE function handles the calendar logic, including month lengths and leap years.

Worked Example

Using the default values from the calculator: start date 2024-01-15, add 1 year, 2 months, 5 days, and 1 week.

1. Start with the year: 2024 + 1 = 2025.
2. Add months: January (1) + 2 = March (3). So we are at March 15, 2025.
3. Add days: 15 + 5 = 20. Now March 20, 2025.
4. Add weeks: 1 week = 7 days, so 20 + 7 = 27. Final date: March 27, 2025.

The DATE function gives the same result: =DATE(2025, 3, 27) returns March 27, 2025. The calculator above shows that result.

When to Use Each Method

Excel offers several ways to adjust dates; the best choice depends on what you are adding:

• Days or weeks: Use simple addition. If A1 contains a date, =A1+7 adds one week. This works because Excel treats each day as one serial number.
• Months: Use the EDATE function for adding a whole number of months. =EDATE(A1, 3) adds 3 months. EDATE handles month-end correctly (e.g., January 31 plus 1 month returns February 28 or 29).
• Years: You can use EDATE with months multiplied by 12, e.g., =EDATE(A1, 12*2) for 2 years. Or use DATE as shown above.
• Mixed units (years + months + days): The DATE function is the most flexible because it accepts all components in one formula.

Common Mistakes and How to Avoid Them

One frequent error is using the EDATE function for days or weeks – EDATE only works with months, not days. Another is forgetting that weeks must be multiplied by 7; adding 1 directly adds only one day, not a week.

When subtracting, you can use negative numbers: =DATE(YEAR(A1)-1, MONTH(A1)-3, DAY(A1)-10) subtracts 1 year, 3 months, and 10 days. However, be careful with the DAY argument: if you subtract more days than the current day number, Excel will roll back into the previous month. For example, March 5 minus 10 days gives February 23 (in a non-leap year). This is usually what you want, but if you need a different behavior (like staying at the end of the month), you might need a more complex formula.

Another pitfall is entering dates as text. If a cell contains “2024-01-15” as text, Excel cannot perform date arithmetic. Always use actual date values (cells formatted as Date) or the DATE function.

Handling Month-End Dates

Adding months to a date at the end of the month can produce unexpected results. For example, January 31 plus 1 month using the DATE function gives March 2 or March 3 (because February has 28 or 29 days, and the overflow carries into March). If you want the last day of the target month instead, use =EOMONTH(A1, 1) to get the end of the next month, or combine it with DAY to get the min of the original day and the last day.

For instance, to add one month but keep the end-of-month behavior: =MIN(DATE(YEAR(A1), MONTH(A1)+1, DAY(A1)), EOMONTH(A1,1)). This ensures January 31 becomes February 28 (or 29), not March 2.

Using the Calculator

The calculator at the top of this page lets you enter a start date and the number of years, months, days, and weeks to add (or subtract, by entering negative numbers). It uses the DATE function method described above, which is the most common and straightforward approach for mixed adjustments. The result is shown as a date in YYYY-MM-DD format.

To subtract, simply enter a negative number in any field. For example, to go back 2 weeks, enter -2 in the weeks field. The calculator will update automatically.

Frequently Asked Questions

How do I add 1 year to a date in Excel?

Use =EDATE(A1, 12) to add exactly 12 months, which is one year. Or use =DATE(YEAR(A1)+1, MONTH(A1), DAY(A1)). Both work, but EDATE is simpler for whole years.

Why does adding months sometimes give a different day?

Because months have different lengths. Adding one month to January 31 using the DATE function gives March 2 (or 3) because February has only 28 or 29 days, so the extra days roll over. If you want the last day of the target month, use EOMONTH.

Can I add weeks directly without converting to days?

No, Excel does not have a dedicated “weeks” unit. You must multiply the number of weeks by 7 and add that many days. For example, =A1+2*7 adds 2 weeks.

How do I subtract days from a date?

Simply use a negative number: =A1-30 subtracts 30 days. This works because Excel dates are serial numbers.

What if my date is stored as text?

Convert it to a real date using DATEVALUE, e.g., =DATEVALUE("2024-01-15"), or use the Text to Columns feature. Then you can perform arithmetic.

Does this work in Google Sheets?

Yes, the same formulas (DATE, EDATE, EOMONTH) work in Google Sheets with identical syntax.

Related

• Wordle Hard Mode: Rules, Tips & How to Turn It On
• Green Screen Backgrounds: How They Work & How to Use Them
• Where to Watch Combat Robotics Competitions

in start | Start date (YYYY-MM-DD) | - | 2024-01-15 | 0
in years | Years to add | - | 1 | 0
in months | Months to add | - | 2 | 0
in days | Days to add | - | 5 | 0
in weeks | Weeks to add | - | 1 | 0
out result | Result date | - | DATE(YEAR(start)+years, MONTH(start)+months, DAY(start)+days+weeks*7) | 0