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

2 Ways to Subtract Or Add Months, Years And Days To Date In Excel

July 31, 2023
13.3K Views

Imagine you want to add five months to a project's schedule date to see what the final closing date would be or you want to figure out how long a certain activity in a list of project activities will take to complete so you need to add months to date in excel. Excel worksheet functions that are made expressly to operate with dates can be used to subtract or add months to date in excel. Also you can add years and days to a date as well.

Here are the tutorials for how you can subtract or add months, years and days to date.

The function, called EDATE function is used to subtract or add months to date in excel. EDATE function is particularly helpful in financial modelling and altering timelines.

The EDATE function need two inputs, the start date and the number of months you want to subtract or add to the date.

Here is the stepwise tutorial on how you can use the EDATE function to subtract or add months to date in excel.

Add Months to Date in Excel

  1. Enter the dates you want to alter in a column. Also, make sure that dates you are entering are formatted properly.

Enter Dates

2. Enter the number of months you have to add or subtract in adjacent column.

3.To add months to date in excel use a positive number and in order to subtract months use negative number.

Enter number of month

  1. Format the output cell as date. Select the cell where you want to display your new date.

  2. Use the formula: =EDATE(cell having start date, cell having number of months).

Enter formula

  1. Press the return key. This should add months to date in excel successfully.

correctly add months

  1. Follow the same procedure with other dates or use auto fill option.

Utilize the auto fill option

Now you can subtract or add months to date in excel.

Add or subtract years from date

Here is the stepwise tutorial on how you can add or subtract years from date.

  1. Enter the dates you want to alter in a column.

Fill the dates

  1. Enter the number of years you have to add or subtract in adjacent column.

  2. To add years to date in excel use a positive number and in order to subtract years use negative number.

Enter the numbers

  1. Select the cell where you want to display your new date.

  2. Use the formula: =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2). Where A2 is the cell having original date and B2 is the cell having number of years to add or subtract.

Use formula

  1. Press the return key.This should display the new date.

the updated date

  1. Follow the same procedure with other dates or use auto fill option.

Result

Now you can subtract or add years from date n excel.

Subtract Or Add Days From Date

Here is the stepwise tutorial on how you can add or subtract days from date.

  1. Enter the dates you want to alter in a column.

Add day

  1. To add days to date in excel use a positive number and in order to subtract days use negative number.

  2. Select the cell where you want to display your new date.

  3. Use the formula: =cell having original date+days to add or =cell having originaldate-days to subtract

enter formula

  1. Press the return key. This should display the new date.

new date

Now you can subtract or add days from date in excel.

Add Months, Years And Days To Date in Best Alternative- WPS Office

WPS Office is a feature-rich office suite that provides users with the ability to add or remove employment dates in various applications such as WPS Writer, WPS Spreadsheets, and WPS Presentation. These applications include WPS Word, WPS Excel, and WPS PowerPoint, respectively. With these tools, users can easily manipulate dates to suit their needs while maintaining the original length of the document or spreadsheet.

Tips and Tricks for Efficient Date Manipulation

Use Named Ranges:

  • Give meaningful names to your cells or ranges.

  • Improves formula readability and maintainability.

Utilize Relative References:

  • Create dynamic formulae that change dependent on the position of the cell.

  • Copy and paste formula into various cells with ease.

By following these tips, you can simplify your formulas, improve readability, and make your spreadsheet calculations more efficient.

FAQs

Can I add a specific number of weekdays to a date?

Yes, you can add a specific number of weekdays (business days) to a date in Excel or WPS Spreadsheets. Here's how you can do it using the WORKDAY function:

To add 10 working days to a date in Cell B2, for example, the formula would be =WORKDAY(A2,10).

Add working day

How do I subtract months from a date in Excel?

To rapidly add or remove months from a date, use the EDATE function.

The EDATE function takes two parameters: the start date and the number of months to add or remove. Enter a negative value as the second input to deduct months. =EDATE("6/07/23",-5) returns 1/07/23, for example.

What is the difference between EDATE and EOMONTH functions?

  • EDATE: Returns the date that is the number of months before or after the start date specified.

  • EOMONTH: Returns the last day of the month in datetime format before or after a given number of months.

Is it possible to add months with decimal values in Excel?

Yes, it is possible to add months with decimal values in Excel. There are different ways to do this, but one common method is to use the EDATE function. The EDATE function takes a start date and a number of months as arguments and returns a new date after adding or subtracting the specified number of months.

To add decimal months, you can use a fraction as the second argument. For example, =EDATE ("1/1/2023",0.5) returns 1/16/2023, which is half a month after the start date.

Conclusion

The above tutorials are applicable on all versions of WPS including 2016/2019 versions. All the versions are available on multiple platforms such as window and mac. Also, in order to use excel you don’t need to be online as it can be used without internet as well.

Did you learn about how to alter years and subtract or add months to date in excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience. 


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