Summary
Excel’s ability to add and remove dates is a game-changer when it comes to managing financial data, scheduling events, and monitoring project schedules. We can assist you with this.
It may be confusing at times to work with dates in Microsoft Excel, particularly when you need to add or remove days, months, or years. We’re exploring the nuances of manipulating dates in Excel in this extensive article.
1. Use a formula to add or subtract days to the date
Use a certain arithmetic operation to add or subtract a specified number of days from a date in a cell or from all the dates in a column.
Increasing a Date in a Cell by Days
To automatically show the future date in a separate column and add many days to a date in an Excel cell, you may use a few different formulae.
The simplest formula takes the form of a cell reference plus the desired number of addition days. Suppose you would want to extend the date in the A2 cell by 10 days. The formula you’ll use is =A2+10.
The DATE(year, month, day) function is another straightforward formula that you may utilize. This is how that formula appears: =DATE(9/20/2015) + 10
The third and last option for adding days to a date in a cell is to do it as a byproduct of another operation. For instance, the TODAY() function is available to you. It is calculated as =TODAY()+10.
As you can see, the date of today does not need to be included in the box for this specific function. Excel can detect the current date, therefore an empty cell will work just fine.
Deducting Days from a Cell Date
It is akin to adding a particular number of days to a certain day when you subtract it from it. The calculations are really still the same; you only need to substitute -N days for +N days. These are a few instances:
= A2–10
=DATE(9/20/2015)-10
=TODAY() -10
2. Add or Subtract Days to Date Without a Formula
If you’re not comfortable working with formulae in Excel, you may add or subtract a certain number of days from numerous dates by utilizing Excel’s Paste Special tool. Here’s how to do it:
1. Enter the number of days you want to add or remove from the dates in a blank cell in column B. Then, duplicate it. You may either use Ctrl + C on your keyboard or right-click it and choose Copy.
2. Choose the range of dates in the cells that you wish to add or remove days from.
3. To duplicate a selection of cells, right-click on the mouse, pick Paste Special, and then select Paste Special again from the context menu’s bottom.
4. Depending on your goal, choose the Add or Subtract option when the Paste Special dialog box appears. Then, click the OK button in the lower left corner to exit the window.
5. You’ll see that the dates are now shown as five-digit integers with an additional day added to them.
6. After choosing these five-digit numbers, go to Home, the Number Format box, and the drop-down selection will show Short Date format. This will turn the digits back into dates.
3. Use Microsoft Excel to Add or Subtract Weeks to Date
The process of adding or removing weeks from the dates in the Excel columns is similar to that of adding or removing days. Actually, you’ll be using the identical calculations; only, you’ll increase the desired number of weeks by seven rather than adding days.
Let’s examine the examples’ formulae.
The formula you use to add four weeks should look like this:
= DATE(2015, 9, 20) + 4 * 7 = A2 + 4 * 7
=TODAY() + 4 * 7
Weeks are subtracted in the same way. You will utilize -N weeks instead of +N weeks.
A2-4 * 7
=DATE(9/20/2015)-4*7
=TODAY()-4*7
4. Use Microsoft Excel to Add or Subtract Months to the Date
A date in a cell may have an entire month added or subtracted using one of two operations. Either the DATE or EDATE function will be used.
Use the Excel DATE Function to Add or Subtract Months.
For the purpose of adding a complete month or months to a certain date, you will want three Excel cells. Let’s imagine that the date to which you want to add months is in cell A2. Enter the desired number of months (for example, 2) in C2. Enter the following formula in cell 2 of column B:
=DATE(MONTH(A2), DAY(A2), +$c$2, YEAR(A2))
Using this formula to subtract is simple. Simply enter the relevant portion of the formula or add -2 in the C2 cell:
or
= DATE(MONTH(A2),DAY(A2),-$c$2, YEAR(A2))
Alternatively, you may enter the desired number of months straight into the formula rather than entering it in the C2 box and referring to it.
=DATE(MONTH(A2),DAY(A2),+2, YEAR(A2))
Add or Subtract Months with the Excel EDATE Function
EDATE formula will return a date that’s specified number of months before or after the start date. This formula contains two arguments. The Start Date value (the date to which you want to add months) will be the first argument. The second argument will be the number of months you want to add or subtract.
Your formula will look like this: =EDATE(start date cell reference, months to add). You can use the same format as for the DATE function.
Adding months example: =EDATE(A2,$C$2)
5. Use Microsoft Excel to Add or Subtract Years to the Date
The DATE function in Excel may be used in the same manner as the month function to add any number of years to a given date. You will need to provide the number of years you want to add this time.
This is how your formula will appear: DATE(MONTH(date), DAY(date), YEAR(date) + N years)
Let’s look at an illustration. Let’s say you want to change the date in the cell by two years. The following should be your formula:
=DATE(MONTH(A2),DAY(A2), YEAR(A2)+2)
We can use the same technique to remove a year from a date, but you should use the negative number for years instead of the positive one.
Entering the -N number of years in the distinct cell C2 is an additional method to deduct a year. Use the =DATE(YEAR(A2)+$C$2,MONTH(A2),DAY(A2)) in such scenario.
6. In Microsoft Excel, add or subtract a combination of the years, months, weeks, and days to date.
The DATE Excel function may be used to add or remove a range of years, months, and days from your date.
This is how your formula should look to add years, months, and days:
DATE(MONTH(date) + Y months, DAY(date) + Z days, YEAR(date) + X years
To take them away:
DATE(MONTH(date) – Y months, DAY(date) – Z days, YEAR(date) – X years
To see it in the example, just add two years, three months, and ten days using the following formula:
=DATE(MONTH(A2)+3, DAY(A2)+10, YEAR(A2)+2)
=DATE(MONTH(A2)-3,DAY(A2)-10, YEAR(A2)-2
The capacity to quickly change dates is a valuable tool for managing financial calendars, project deadline calculations, or future event predictions. To become a genuine Excel master, keep trying out new functions and formulae and hone your abilities.
