Skip to main content

How to increment dates by months, years, or days in Excel?

Author: Sun Last Modified: 2024-11-26

Incrementing dates in Excel is a common task, but the default Autofill handle only increases dates by one day. If you need to increment dates by a specific interval - such as one month, two years, or seven days - this tutorial provides practical solutions.
A screenshot showing incremented dates by months, years, or days in Excel

Increment date by months/years/days with Fill Series utility

Add months/years/days to date with formula

Add months/years/days to date with Kutools for Excelgood idea3


Increment date by months/years/days with Fill Series utility

With the Fill Series utility, you can increment date by 1 month, 1 year or a week.

1. Select a blank cell and type the starting date.
A screenshot of a starting date entered in an Excel cell for incrementing

2. Select a range including starting date, and click Home > Fill > Series. See screenshot:

A screenshot showing Excel's Home tab A screenshot showing the Fill Series option in Excel's Home tab

3. In the Series dialog, do the following options.

A screenshot of the Series dialog box with options to increment dates by months or years

1) Specify the filling range by rows or columns.

2) Check Date in Type section.

3) Choose the filling unit.

4) Specify the increment value.

4. Click OK. The selection will now be filled with dates incremented by months, years, or days.
A screenshot showing incremented dates by months, years, or days in Excel


Add months/years/days to date with formula

If you want to add months, years or days to a date or dates, you can apply one of below formulas as you need.

Add years to date, for instance, add 3 years, please use formula:

=DATE(YEAR(A2)+3,MONTH(A2),DAY(A2))
A screenshot showing the formula for adding years to a date in Excel

To add months to a date, for instance, to add 2 months, use the following formula:

=EDATE(A2,2)
A screenshot showing the EDATE formula for adding months to a date in Excel

=A2+60
A screenshot showing a formula for adding days to a date in Excel

Tip:

When you use the EDATE function to add months, the result will appear in general format as a serial number. You need to format the result as a date.


Add months/years/days to date with Kutools for Excel

With Kutools for Excel's Date & Time Helper, you can quickly add months, years, weeks, or days to a date.
An animated demonstration of the Date & Time Helper in Kutools for Excel

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select a blank cell which will place the result, click Kutools > Formula Helper > Date & Time helper, then select one utility as you need from the list. See screenshot:
A screenshot showing the Date & Time Helper option in Kutools tab in Excel

2. Then in the Date & Time Helper dialog, check Add option, and select the date you want to add years/months/days into the textbox of Enter a date or select a date formatting cell section, then type the number of years, months, days, even weeks into the Enter numbers or select cells which contain values you want to add section. You can preview the formula and result in Result section. See screenshot:
A screenshot showing the Date & Time Helper dialog box with example input

3. Click OK. And drag the fill handle over the cells you want to use this formula. See screenshot:
A screenshot showing the results after using the Date & Time Helper A screenshot of a completed date increment using the Kutools utility

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

Related Articles:

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!