WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to add years to a date in excel 2016 (3 Easy Methods)

July 31, 2023
25.2K Views

Working with date-type data is a prerequisite in WPS spreadsheet. It entails duties such as adding and subtracting days, months, and years from dates. I've shown you three different ways to add or subtract years from an existing date in WPS spreadsheet in this article.

Using TEXT Function to Add Days to a Date in Excel

Many ask, "how do I add 7 days to a date?” The answer is simple. Excel has a built-in TEXT function that can convert a value to text in a specific format. It’s a simple function that can add days to a date in Excel. Many people find it challenging to use the TEXT function. Basically, the TEXT function is divided into two parts. The first part is the value to be converted. This value can be any number, date, or text string. The second part is the format that needs to be used to convert the value into text. A format code updates the format to use.

Here’s how to use the TEXT function.

Step 1: The first thing you need to do is enter a cell's function. Additionally, you can use the function in a formula too. Suppose you want to add days to 7 dates (a week) in cell A1; then, here’s how to create a date formula.

=TEXT(A1,"dd/mm/yyyy")+7

Step 2:You can enter this formula from the cell your dates start. If you add this formula to Cell 2, the result will be the days after the date in Cell A1.

Adding days to date

Step 3: Furthermore, you can use the macro option of the TEXT function to add days to the dates in Cell A1. For example, if you want to add 7 days to date in Cell A1, you can use the below-mentioned macro.

Applying the formula

Step 4:To do this, you need to use this macro:

Sub AddDays() Dim d As Date d = Range("A1").Value d = d + 7 Range("A2").Value = Format(d,"dd/mm/yyyy") End Sub.

Step 5: Boom! It’s done. The macro formula will add 7 days to the date in Cell A1 in the dd/mm/yyyy format.

Using the SUM Function to Add Weeks to a Date in Excel

Do you need to add weeks to a date in Excel but can’t figure out how? We’ve got your back. This formula for weekly dates in Excel will enable you to add weeks to a date in Excel. For this, we’ll focus on the SUM function. The SUM function is extremely simple and easy to use to add weeks to a date in Excel. You just need to follow the steps carefully as they are.

Step 1: The first thing we need to do is to convert the weeks into days. We’ll multiply weeks by 7 to get the total number of days.

Adding weeks to date

Step 2: Now, we’ll use the SUM function to sum the existing date to the calculated days.

Step 3: To begin the magic of the SUM function, simply click on the E5 Cell, as seen in the screenshot, and write the formula mentioned below:

=SUM(C5,7*D5)

Adding the formula

Step 4: Hit the enter button. This will result in the E5 Cell showing the new date after adding the number of weeks.

Step 5: You must use the auto-fill or fill function to add this formula to the rest of the cells automatically.

Results of the formula

Step 6: It's done! You will see new dates after adding a number of weeks to the previous dates.

Using EDATE Function to Add Months to a Date in Excel

Adding months to a date in Excel would never have been easy if there was no EDATE function. Thanks to the EDATE function’s easy usability, users can easily add months to a date in Excel. Many Excel users get confused and ask: how do I add 30 days to a date in Excel? Here’s how it’s done. Simply read the following steps and follow them carefully to add 30 days to a date in Excel.

Step 1: To create EDATE formulas in Excel, you must first add a valid start date. A valid start date can be “1/30/2025” or “30-jan-2025”. The date formula will look like DATE(2025, 1, 30).

Using EDATE formula

Step 2: Provide the data for months. This specifies how many months to be added or substrates from the start date. You can use a positive number to shift the date forward and a negative number to shift the date backward.

Step 3: Apply the date formula to the relevant cell.

Here’s an example:

If you want to go 5 months forward from the start date, then you can use these:

=EDATE("1/30/2025", 5)

=EDATE("30-Jan-2025", 5)

=EDATE(DATE(2025, 1, 30), 5)

(Note that you can use any date)

To get 5 months backward from the start date, you can use these formulas:

=EDATE("1/30/2025", -5)

=EDATE("30-Jan-2025", -5)

=EDATE(DATE(2025, 1, 30), -5)

If your dates are specified in a cell (says it Cell C4), then you can use formulas like these:

=EDATE(C4, 5)

=EDATE(C4, -5)

Step 4: The result will be shown as in dates and months as in months before or after the start date.

Results of EDATE

Using Simple Arithmetic Operations to Add Years to a Date in Excel online, 2016 and 2019

Simple Arithmetic Operations enable people to add years to a date in Excel online in the 2016 and 2019 versions. It’s a simple process with clear and straightforward steps.  Here’s how you can add years to a date in Excel online.

Step 1: Select the cell where your results need to appear. (In our case, it’s Cell  E6)

Adding years to dates

Step 2: The next thing you need to do is to type an equal symbol (=). You’ll have to type the formula afterward.

Step 3: Choose the cell with the date (in our case, Cell D6). You can manually input the date.

Step 4: After this, you need to add a plus or subtraction sign. Plus ( + ) sign refers to addition and subtraction ( - ) sign for subtraction of years.

Step 5: Now, multiply the number of years you want to add or remove by 365. Press the enter button. (=D6+(2*365)).

Step 6: The results will be seen on your selected cell. You can see that it’ll add your added years (2 years in our case) to the selected date. You may use this formula for other cells.

Getting results
Note: This technique will add or remove the year indicated; however, it may display some differences in the days/months because leap years have 366 days, and this method operates on days. Consider looking into the alternative options in such a case.

Frequently Asked Questions (FAQs)

Can Excel handle different date formats?

Yes, Excel can handle various date formats. You can customize the date format by selecting the cells containing dates, right-clicking, choosing "Format Cells," and selecting the desired date format from the options.

How can I calculate future or past dates in Excel?

You can calculate future or past dates in Excel using the DATE() function combined with other functions. For example, to calculate a future date, use "=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()))" to get the date one month ahead of the current date.

Can Excel perform calculations based on dates?

Yes, Excel has built-in functions for performing calculations based on dates. You can use functions like DATE(), DAY(), MONTH(), YEAR(), EOMONTH(), and others to perform calculations such as finding the day of the week, determining the last day of a month, adding or subtracting days, and more.

Summary

Adding days to dates in Excel might be complex for many people; hence this article has discussed four possible ways to add days, weeks, months, and years to your dates with straightforward steps. Follow the steps carefully, and you can make your work easier. Excel is a widely used software; however, it’s not free. People need to buy Excel. Hence, it’s best to use the best available alternative. One best alternative to Excel is WPS Office.

WPS Office is free software that allows users to use PDF, Excel, Word, and PPT under one roof. Yes, you can use these four applications in a single application, WPS Office. It’s a worldwide used software that boosts productivity and creativity. Moreover, it offers free WPS Templates in the template library on its website. WPS offers various functions, like Excel, enabling users to work smoothly and efficiently. What are you waiting for? Download yours now!


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.