Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Filter Data in Excel
  • How to count table rows in Excel
  • Randomize/ Shuffle List in Excel
  • How to create running total in an Excel Table
  • How to add Trendline to a chart in Excel

References

  • Extract data with helper column in Excel
  • Vlookup Examples in Excel
  • Perform case-sensitive Lookup in Excel
  • How to get last column number in range in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation must not contain
  • Excel Data validation exists in list
  • Data validation must not exist in list
  • Excel Data validation number multiple 100

Add years to date in Excel

by

If you need to add years to date in Excel then this tutorials is for you. See the example below .

To add a given number of years to a date, you can use a formula based on the DATE function, with help from the YEAR, MONTH, and DAY functions.

Worked Example:   Sum by month ignore year in Excel

Formula

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

Note: if you need to add an “even” multiple of 12 months to a date (i.e. 12, 24, 36, 48, etc) you can use a much simpler formula based on the EDATE function.

Explanation

In the example shown, the formula in D5 is:

=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))

How this formula works

Working from the inside out, the YEAR, MONTH, and DAY functions extract those respective date components:

=YEAR(B5) // 1960
=MONTH(B5) // 3
=DAY(B5) // 8

At the outer level, the DATE function simply reassembles the component values back into a valid Excel date. To add years to the date, we just need to add the value in C5 to the year component prior to reassembly:

=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))

The formula is then solved like this:

=DATE(1960+10,3,8)
=DATE(1970,3,8)
=8-Mar-1970

Post navigation

Previous Post:

Assign points based on late time in Excel

Next Post:

Add workdays to date custom weekends in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel FALSE Function
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • How to return blank in place of #DIV/0! error in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • Nested IF function example in Excel

Date Time

  • Count holidays between two dates in Excel
  • Convert decimal seconds to Excel time
  • How to calculate nth day of year in Excel
  • Convert time to time zone in Excel
  • Calculate series of dates by workdays in Excel

Grouping

  • Categorize text with keywords in Excel
  • Group numbers with VLOOKUP in Excel
  • Group numbers at uneven intervals in Excel
  • Group times into unequal buckets in Excel
  • If cell contains one of many things in Excel

General

  • Basic text sort formula in Excel
  • How to generate random times at specific intervals in Excel
  • AutoFit Column Width, AutoFit Row Height in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • How to get amount with percentage in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning