Skip to main content

How to add or subtract days, months, and years to date in Excel?

Author: Xiaoyang Last Modified: 2024-08-28

This article provides 7 solutions to add or subtract days, weeks, months, years, or a combination of years/months/days to a date in Excel.

  1. Add or subtract days to date with formula
  2. Add or subtract days to multiple dates without formula
  3. Add or subtract weeks to date in Excel
  4. Add or subtract months to date in Excel
  5. Add or subtract years to date in Excel
  6. Add or subtract a combination of years, month, and days to date in Excel
  7. Add or subtract days, weeks, months, or years to date with Kutools for Excel

Add or subtract days to date with formula

You may have noticed that dates are 5-digit numbers in Excel. Therefore, you can add or subtract days as easy as adding or minus the number of days in Excel.

= date + number of days

1. Select a blank cell you will place the calculating result, type the formula =A2+10, and press the Enter key.
Note: For subtracting 10 days from the date, please use this formula =A2–10.
a screenshot of using formula to add days to a date

2. If you need to apply this formula to other cells, please drag the AutoFill handle of the formula cell to these cells as you need.

Now you have added or subtracted the same number of days from these dates in bulk. See screenshot:
a screenshot of final dates after adding 10 days

Easily add/subtract days, weeks, Months, or Years to date in Excel

Difficult and tedious to remember long complicated formulas in Excel? Kutools for Excel’s Formula Helper lists most common-used formulas to help you quickly calculate and solve problems in Excel, says Add years / months / weeks / days to date, Add hours / minutes / seconds to date, Sum absolute values, Find most common value, etc.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

Add or subtract days to multiple dates without formula

Comparing to formulas, some Excel users prefer to add or subtract days directly without formulas. Here I will guide you to add or subtract the same number of days from multiple dates with Paste Special feature in Excel.

1. Type the number of days you will add or subtract to dates in a blank cell, says 10, and then copy it. See screenshot:
a screenshot of typing the number of days you will add or subtract to dates in a cell then copying

2. Select the dates you will add or subtract days, right click, and select Paste Special > Paste Special in the context menu. See screenshot:
a screenshot of opening the Paste Special dialog box

3. In the Paste Special dialog, please check Add or Subtract options as you need, and click the OK button. See screenshot:
a screenshot of selecting Add or Subtract in the Paste Special dialog box

4. Now the number is added or subtracted to the dates, and the dates display as 5-digit numbers. Keep these 5-digit numbers selected, and click Home > Number Format box > Short Date to convert them to dates again.
a screenshot of changing the cell format to Short Date

Now you will see the specified number of days is added or subtracted from the specified range of dates in bulk without formula. See screenshot:
a screenshot of the original dates and the dates after adding a number of days

Add or subtract weeks to date in Excel

You can also apply formula to add or subtract the specified number of weeks from a date in Excel. Please do as follows:

= date + 7 * number of weeks

1. Select the blank cell you will place the calculating result, type the formula =A2+4*7, and press the Enter key.
Note: For subtracting 4 weeks from the date, please use this formula =A2-4*7.
a screenshot of using formula to add weeks to a date

Kutools for Excel

Stand out from the Crowd

300+ Handy Tools
Solve 80% of Problems in Excel
Free Trial Now

Say goodbye to tiring VBA and formulas!

2. If you need to add or subtract weeks from other dates, please drag the AutoFill handle of the formula cell as you need.

Now you have added or subtracted the same number of weeks from multiple dates in bulk. See screenshot:
a screenshot showing the original dates and dates after adding weeks

Add or subtract months to date in Excel

For adding or subtracting months to dates, it’s not fit to add or subtract 30 from the dates because months contain different number of days in a year, some contain 30 days, some contain 31 days, while some contain 28 or 29 days. Therefore, we need to apply the EDATE function to deal with this situation.

= EDATE(date, number of months)

1. Select the blank cell you will place the calculating result, type the formula =EDATE(A2,3), and drag this formula cell’s AutoFill handle to apply this formula to other cells as you need.
Note: To subtracting 3 months from the date, please use this formula =EDATE(A2,-3).
a screenshot of using formula to add months to a date

2. As you see, the EDATE function will return 5-digit numbers. Please keep these 5-digit numbers selected, and click Home > Number Format box > Short Date to convert them to dates back. See screenshot:
a screenshot of changing the results to Short Date format

Now you will see it has added or subtracted the same number of months to the dates in bulk. See screenshot:
a screenshot showing the final results

Add or subtract years to date in Excel

For example, you will add 6 years to a batch of dates in Excel, you can do as follows:

= DATE(YEAR(date) + number of years, MONTH(date),DAY(date))

1. Select the blank cell you will place the calculating result, type the formula =DATE(YEAR(A2)+6,MONTH(A2),DAY(A2)), and press the Enter key.
Note: To subtract 6 years from the date, please apply this formula =DATE(YEAR(A2)-6,MONTH(A2),DAY(A2)).
a screenshot of using formula to add certain years to a date

2. If needed, drag the formula cell’s AutoFill handle to apply this formula to other cells as you need.
a screenshot shows all dates after adding years

Add or subtract a combination of years, month, and days to date in Excel

In some cases, you may need to add or subtract years, months, and days at the same time, says add 3 years 5 months and 15 days. In this case, you can apply the DATE function to solve the problem.

= DATE(YEAR(date) + number of years, MONTH(date) + number of months, DAY(date) + number of days)

1. Select the blank cell you will place the calculating result, type the formula =DATE(YEAR(A2)+3,MONTH(A2)+5,DAY(A2)+15), and press the Enter key. See screenshot:
Note: To subtract the combination of years, months, and days together, please use this formula =DATE(YEAR(A2)-3,MONTH(A2)-5,DAY(A2)-15).
a screenshot of using formula to add a combination of years, month, and days to date

2. If needed, please drag this formula cell’s AutoFill handle to apply this formula to other cells.
a screenshot showing the results after adding a combination of years, month, and days

Add or subtract days, weeks, months, or years to date with Kutools for Excel

If you have Kutools for Excel installed, you will find its Formula Helper lists most-commonly-used formulas to help you easily calculate in Excel, including adding days, weeks, months, or years to date. Please do as follows:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

1. Select the cell you will put calculating result in, and click Kutools > Formula Helper > Date & Time > Add years to date.
Tip: Please select the suitable formula from the Formula Helper drop down list based on your needs. For example, select Add weeks to date for adding/subtracting specified weeks to a date.

2. In the opening Formula Helper dialog box, specify the date cell address in the Date Time box, and type the number of years you will add in the Number box.
Tip: In our case, we will add 5 year from the date, therefore we type 5 into the Number box. If you need to subtract some years, for example 3 years, please type -3 into the Number box.
a screenshot of configuring the formula arguments

3. Click the Ok button to apply the formula of Add years to date. Please drag the Fill Handle to apply this formula to other range if necessary.
a screenshot of the final dates after adding 5 years

Add/subtract days to date:
a screenshot of easily adding days to a list of given dates with Kutools for Excel

Add/subtract weeks to date:
a screenshot of easily adding monthes to a list of given dates with Kutools for Excel

Add/subtract months to date:
a screenshot of easily adding days to a list of given dates with Kutools for Excel

Kutools for Excel’s Formula Helper will save Excel users from memorizing complicate formulas and lists most common-used formulas to help you quickly calculate and solve problems in Excel, says Add years / months / weeks / days to date, Add hours / minutes / seconds to date, etc. Have a Free Trial!


Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!