Adding and subtracting days, weeks, months and years in Excel

Sometimes it seems that the capabilities of the program Excel are boundless, that in a lifetime it is impossible to understand all the algorithms and ways of working in it. But the main thing is to start. Today we’ll talk about dates. Microsoft Excel has many different features and methods for performing arithmetic operations. This article contains many useful formulas for quick math with various dates in Excel spreadsheets.

In other articles, we have already discussed various techniques, actions, formulas and calculations. We will now look at some formulaswho can help you with arithmetic operationswhen it comes to different dates and times. This article covers the following points:

• How to subtract one date from another.
• How to subtract or add days to a specific date.
• How to subtract or add weeks to a specific date.
• How to add months to today’s date.
• How to add years to today’s date.
• How to subtract or add combinations of years, months and days.
• How to take or add time.
• The Date and Time Formula Wizard is a quick way to work with arithmetic operations.

How to subtract one date from another 

Let’s say cells A2 and B2 contain certain date values, and your task is to set difference between them. In the circumstances, it is possible to apply various technologies, but at the same time get identical result. 

Option 1: Just subtract one date from another

Program date data is stored in the format non-repeating numbers, the countdown is from 01.01.1900/XNUMX/XNUMX. Accordingly, you work with numbers, i.e. these are normal math operations that work no problem:

= B2-A2

Option 2. DATEDIF

DATEDIF, which gives exactly the same result:

= DATEDIF (A2, B2, «d»)

In any situation, you can come to the same position in different ways. Working in Excel is also no exception. Here, too, it is possible to come to the same final result in different ways. For example, the following screenshot shows the same result, not counting line 4 – because DATEDIF displays mistake #ON ONE. So, what does this have to do with? To answer this question, let’s look at the following example.

If 06.05.2015/01.05.2015/XNUMX is subtracted from XNUMX/XNUMX/XNUMX, i.e. subtract the larger date from the smaller date, we get a negative number (-5). Function DATEDIF, therefore gives out mistake, so does not provide a negative outcome.

Option 3. Subtract another date from the present date.

Under such conditions, we can simplify our task even more. We should use the above techniques, but only by adding function TODAY () instead date 1. Here’s what it will look like:

= TODAY () — А2

or

= DATEDIF (A2, TODAY (), «d»)  

We all remember elementary school problems like: Petya had 5 apples, he gave 3 apples to Masha. How many apples does Petya have left? Obviously the result is positive. The same principle should be followed when dealing with function DATEDIF. The most important thing is to take into account larger and smaller dates, so that the result, as in an elementary task, is positiveotherwise DATEDIF will give an error:

Option 4. DATE 

Many do not like to constantly use links and paste them somewhere. The next method is just right for you. You can write data directly in the formulaby applying the function DATE .

Let’s look at an example. Next formula deducts 15.05.2015/20.05.2015/XNUMX from XNUMX/XNUMX/XNUMX and shows the difference – 5 days. Everything is simple and clear.

= DATE (2015, 5, 20) — DATE (2015, 5, 15)

Let’s sum up the intermediate result. So, in the most simple cases, you can use the first option.

In case you must find difference between specific dates not in days, but in years or months, the function DATEDIF is the only allowed option. Next, we will consider the various variants of the formulas in more detail.

Excel offers various functions, among their diversity you choose an option, guided by what you need to do, which mathematical operation you need to use (or several at once):

How to subtract or add days 

Let’s say you’re documenting a project and you have date data in a cell or column. We can always automatically add or take away a certain number of days, rather than prescribing new data manually. These techniques will save us a lot of time and effort. 

Option 1: Add days to a specific date 

Formulasuitable for this operations, as follows:

Date + number of days

There are several ways to specify a date, namely:

• Cell reference = A2 + 10
• Function DATE = DATE (2015, 5, 6) + 10
• Function TODAY (), in case of addition with the current day: = TODAY () + 10

Let’s look at Examples in the following screenshot. Let’s go back a little to the past, and for this day we will consider 05.06.2015/XNUMX/XNUMX:

Comment: After saving the formula number, press Ctrl + 1 – window opensCell format“. Then select “date” from the list “Category” and define the form of the date.

Option 2: Subtract days from a specific date

In this situation, you must again perform a simple arithmetic operation. It is no more difficult than the addition, which we have analyzed above. Thus, the only actions that will differ from the above are staging minus sign instead of the plus sign:

Date – number of days

The options are:

= A2-10

= DATE (2015, 5, 6) -10

= TODAY () – 10

How to subtract or add weeks 

Under such conditions, you must apply the same actions that are performed in days, only you need multiply number weeks by 7. Everything is extremely simple.

Addition method:

cell + number of weeks * 7

Suppose your goal – to A2 add 3 weeks. Suitable for this: = A2 + 3 * 7. 

Subtraction method:

cell – number of weeks * 7 

Let’s take an example. If the original date is present day, we prescribe = TODAY () — 2 * 7.

How to subtract / add months 

Here we also have selection. We can use function DATE or EDATE.  

Option 1: DATE 

Let’s look at an example. We have column A with a list of dates. Next, in each cell, for example, C2, indicate number months with a plus sign (when we add), with a minus sign (when we subtract).

The resulting formula is written in cell B2. Drag the corner of the cell in a downward direction. As a result, the formula will be shown in all cells:

Now let’s see how this function works. Everything is simple here. The values Features DATE:

• year in A2;
• months in A2 + number of months in C2
• day in A2.

When entering a signed number minus, the months will be take away instead of adding:

Sure sign minus it is allowed to write in the formula. This is another way for those who do not like copying links.

=DATE(YEAR(A2), MONTH(A2) – $C$2, DAY(A2))

Moreover, instead of specifying links, number months can be written in the formula itself:  

= DATE (YEAR (date), MONTH (date) + number of months, DAY (date))

The options are: 

• • Adding months: =DATE(YEAR(A2), MONTH(A2) + 2, DAY(A2))

• Subtraction months: =DATE(YEAR(A2), MONTH(A2) – 2, DAY(A2))

Option 2. EDATE.

EDATE found only in Excel 2007, as well as later versions.

Two component are registered in formulas EDATE (start_date, month):

Start_Date: The starting date from which to count.

Month: The number of months to be added (plus value) or subtracted (minus value).

Either way, the outcome will be the same:

Again way for those who like to prescribe all the data in the formula. Let’s analyze options:

Add up:

=EDATE(DATE(2015,5,7), 10)

10 months add to 07.05.2015.

Subtract:

= EDATE (TODAY (), -10)

10 months take away from the current date.

Comment. In order for the received data to be written in date format, cells with EDATE must be formatted.

How to subtract / add years 

In this case, the situation is similar to working with months. Let’s use the DATE function again, only now you need to set the number of years needed:

Date) + number of years, MONTH (date), DAY (date))

The options are:

Add up:

=DATE(YEAR(A2)+5, MONTH(A2), DAY(A2))

To data A2 is added 5 years.

We take away:

=DATE(YEAR(A2) – 5, MONTH(A2), DAY(A2))

From data A2 will be taken away 5 years.

If we want our formula was universal, we must specify the number of years with a plus sign to add or the number of years with a minus sign to subtract in a cell, then this cell specify on DATE:

How to subtract or add combinations of years, months and days 

And we give again preference our beloved Features DATE 🙂 

Addition method:

DATE (YEAR (date) + X years, MONTH (date) + Y months, DAY (date) + Z days)

Subtraction method:

DATE (YEAR (date) – X years, MONTH (date) – Y months, DAY (date) – Z days)

Let’s take a concrete example. FROM

Next the formula add 2 years, 3 months, and also subtract 15 days from the specific date specified in cell A2: 

= DATE (YEAR (A2) + 2, MONTH (A2) + 3, DAY (A2) – 15)

As for our the column dates, formula as follows:

=DATE(YEAR(A2) + $C$2, MONTH(A2) + $D$2, DAY(A2) + $E$2)

How to take or add time 

In this program you can add or subtract time periods using the function TEAM. The principle is the same as when processing dates. 

Add: 

Cells + TIME (hours, minutes, seconds)

Subtract:

Cell – TIME (hours, minutes, seconds)

Inside A2 is indicated timeWhich will change. 

And now let’s figure it out example with time. Our task is to perform the following mathematical operations: by the time inside A2 add 2 hours, 30 min. and 15 sec. Here’s what we do:

= А2 + TIME (2, 30, 15)

Let’s suppose we have to carry out one action at once two operations. In fact, it is not as difficult as it seems at first glance. Just put up a sign minus to the corresponding values:

= А2 — TIME (2, 30, -15)

Thus, with the help of simple actions, we managed by the time inside A2 add on 2 hours and 30 minutes and subtract 15 seconds 

Moreover, you can specify timewhich you want change in specific cells and enter those cells in your formula:

= TIME А2 ($ C $ 2, $ D $ 2, $ E $ 2)

Such a formula can also be used if the source cells contain both dateAnd time:

Date and Time Formula Wizard – a very fast way to work with arithmetic operations  

After reading all the sections, you have learned many different formulas that can be used in a particular situation. But is there a faster way? Is it possible to combine all these formulas into one simple action? The answer is yes, you can. There is a great add-on to the Microsoft Excel program called Ultimate Suite. This application has a tool “Date and Time Master”, which makes working with dates even easier. It works like this:

First you must choose cellwhere you want to place the formula.

Then you must go to the tab Ablebit Tools and click “Date and Time Master“:

After opening the windowDate and Time Master» go to the appropriate tab, enter the necessary parameters in the formula and click «Paste Formula».

Let’s look at an example of how this tool works. Suppose we have to add several months to the date in cell A2. To do this, go to the tab “Add“, enter A2 in the section”Enter date” (or select a cell directly in the Excel spreadsheet) and enter the desired number of months.

This tool will create the formula and preview in cell. Also displayed calculated in accordance with the formula date:

When you are completely satisfied with the data received, click the “Paste Formula“. Once the formula has been added, it can be copy to all required cells:

Agree that it was very easy. Let’s give our magic tool some more difficult work. For example, subtract immediately all parameters. To do this, we go to the tab “Subtract” and enter the data in the required sections. We need to enter units of measurement in individual cells, then save references to these cells, as shown in the screenshot:

When you press the “Paste Formula” inside A2 the following appears:

= DATE (YEAR (A2) – D2, MONTH (A2) – E2, DAY (A2) – G2 – F2 * 7)

If you fill in this formula in other cells, you must change all references, except for A2, to absolute linksso that no errors occur and formulas are copied correctly (by default, this tool always uses relative links). To change the link, simply enter a $ sign in front of the row and column coordinates:

=DATE(YEAR(A2)-$D$2, MONTH(A2)-$E$2, DAY(A2)-$G$2-$F$2*7)

Result:

You can also click on the link Show time fields and add or subtract date and time units using only one formulas.

So, here you have mastered a few more knowledge and skills. We wish you success in applying them to practice. See you in the next article, where we will analyze a lot of other interesting things related to this useful program. Microsoft Excel.