How to add and subtract dates, days, weeks, months and years in Excel

In this lesson, you will learn various useful formulas for adding and subtracting dates in Excel. For example, you will learn how to subtract another date from one date, how to add several days, months or years to a date, etc.

If you have already taken lessons on working with dates in Excel (ours or any other lessons), then you should know the formulas for calculating units of time, such as days, weeks, months, years.

When analyzing dates in any data, you often need to perform arithmetic operations on these dates. This article will explain some formulas for adding and subtracting dates that you may find useful.

• Subtract two dates in Excel
• How to add (subtract) days to a date in Excel
• How to add (subtract) several weeks to a date
• Add months to dates in Excel
• Adding Years to a Date in Excel
• Adding (subtracting) days, months and years to a date
• How to Add and Subtract Time in Excel

How to subtract dates in Excel

Let’s assume that in your cells A2 и B2 contains dates, and you need to subtract one date from another to find out how many days there are between them. As is often the case in Excel, this result can be obtained in several ways.

Example 1: Directly subtracting one date from another

I think you know that Excel stores dates as integers starting at 1, which is January 1, 1900. So you can simply subtract one number from another arithmetically:

=B2-A2

Example 2. Subtracting dates using the DATEDAT function

If the previous formula seems too simple for you, the same result can be obtained in a more sophisticated way using the function RAZNDAT (DATEDIF).

=РАЗНДАТ(A2;B2;"d")

=DATEDIF(A2,B2,"d")

The following figure shows that both formulas return the same result, except for row 4, where the function RAZNDAT (DATEDIF) returns an error #NUMBER! (#NUM!). Let’s see why this happens.

When you subtract a later date (May 6, 2015) from an earlier date (May 1, 2015), the subtraction operation returns a negative number. However, the function syntax RAZNDAT (DATEDIF) does not allow start date was bigger end date and, of course, returns an error.

Example 3: Subtract a date from the current date

To subtract a specific date from the current date, you can use any of the previously described formulas. Just use the function instead of today’s date TODAY (TODAY):

=СЕГОДНЯ()-A2

=TODAY()-A2

or

=РАЗНДАТ(A2;СЕГОДНЯ();"d")

=DATEDIF(A2,TODAY(),"d")

As in the previous example, formulas work fine when the current date is greater than the date being subtracted. Otherwise, the function RAZNDAT (DATEDIF) returns an error.

Example 4: Subtracting dates using the DATE function

If you prefer to enter dates directly into the formula, use the function DATE (DATE) and then subtract one date from another.

Function DATE has the following syntax: DATE (year; month; day).

For example, the following formula subtracts May 15, 2015 from May 20, 2015 and returns the difference – 5 days.

=ДАТА(2015;5;20)-ДАТА(2015;5;15)

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

Let’s summarize. If you need to subtract dates in Excel and you want to determine the number of days between two dates, it makes sense to do this in the simplest and most obvious way – to directly subtract one date from another.

If needed count the number of months or years between two dates, then the function RAZNDAT (DATEDIF) is the only possible solution. In the continuation of the article, you will find several examples of formulas that reveal this function in detail.

Now that you know how to subtract one date from another, let’s see how you can add or subtract a certain number of days, months, or years from a date. There are several Excel functions for this. Which one to choose depends on which units of time you want to add or subtract.

How to add (subtract) days to a date in Excel

If you have a date in a cell or a list of dates in a column, you can add (or subtract) a certain number of days to them using the appropriate arithmetic operation.

Example 1: Add days to a date in Excel

The general formula for adding a certain number of days to a date looks like this:

= Дата + N дней

The date can be set in several ways:

• Cell reference:

  =A2+10

• Calling a function DATE (DATE):

  =ДАТА(2015;5;6)+10

  =DATE(2015,5,6)+10

• Calling another function. For example, to add several days to the current date, use the function TODAY (TODAY):

  =СЕГОДНЯ()+10

  =TODAY()+10

The following figure shows how these formulas work. At the time of writing, the current date was May 6, 2015.

Note: The result of these formulas is an integer representing the date. To show it as a date, you must select the cell (or cells) and press Ctrl + 1… A dialog box will open Cell format (Format Cells). On the tab Number (Number) in the list of number formats, select date (Date) and then specify the format you need. A more detailed description can be found in the article. How to change the date format in Excel.

Example 2: Subtract days from a date in Excel

To subtract a certain number of days from a date, you again need to use the normal arithmetic operation. The only difference from the previous example is a minus instead of a plus 🙂

= Дата - N дней

Here are some examples of formulas:

=A2-10

=ДАТА(2015;5;6)-10

=СЕГОДНЯ()-10

How to add (subtract) several weeks to a date

When you need to add (subtract) several weeks to a certain date, you can use the same formulas as before. You just need to multiply the number of weeks by 7:

• Add N weeks to date in excel:

  = A2 + N недель * 7

  For example, to add 3 weeks to a date in a cell A2, use the following formula:

  =A2+3*7

• Subtract N weeks from date in excel:

  = А2 - N недель * 7

  To subtract 2 weeks from today’s date, use this formula:

  =СЕГОДНЯ()-2*7

  =TODAY()-2*7

How to add (subtract) several months to a date in Excel

To add (or subtract) a certain number of months to a date, you need to use the function DATE (DATE) or DATEMES (EDATE) as shown below.

Example 1: Adding several months to a date using the DATE function

If the list of dates is, for example, in a column A, indicate the number of months you want to add (positive number) or subtract (negative number) in some cell, say, in C2.

Type in cell B2 the formula below, click on the highlighted corner of the cell and drag it down the column with the mouse B to the last filled cell in the column A. Formula from cell B2 will be copied to all cells in the column B.

=ДАТА(ГОД(A2);МЕСЯЦ(A2)+$C$2;ДЕНЬ(A2))

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

Let’s see what this formula does. The logic of the formula is clear and obvious. Function DATE (year; month; day) receives the following arguments:

• Year from the date in the cell A2;
• Month from the date in the cell A2 + the number of months specified in the cell C2;
• Day from the date in the cell A2;

Everything is simple! If you enter into C2 a negative number, the formula will subtract months, not add.

Naturally, nothing prevents you from entering a minus directly in the formula to subtract months:

=ДАТА(ГОД(A2);МЕСЯЦ(A2)-$C$2;ДЕНЬ(A2))

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

And, of course, you can specify the number of months to add or subtract directly in the formula without a cell reference. The finished formulas will look something like this:

• add months to date:

  =ДАТА(ГОД(A2);МЕСЯЦ(A2)+2;ДЕНЬ(A2))

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

• subtract months from date:

  =ДАТА(ГОД(A2);МЕСЯЦ(A2)-2;ДЕНЬ(A2))

  =DATE(YEAR(A2),MONTH(A2)-2,DAY(A2))

Example 2: Add or subtract months from a date using the DATES function

Excel provides a special function that returns a date that is a certain number of months ago or ahead of a given date – this is a function DATEMES (EDATE). It is available in the latest versions of Excel 2007, 2010, 2013 and the new Excel 2016.

If you are using DATEMES (EDATE) You provide the following two arguments:

• Start date – the date from which the number of months is counted.
• Months is the number of months to add (positive number) or subtract (negative number).

These formulas will give the same result as the formulas with the function DATE (DATE) in the previous example:

When using the function DATEMES (EDATE) start date and number of months can be specified directly in the formula. Dates can be set using the function DATE (DATE) or as a result of other formulas. For example:

• This formula adds 10 months to May 7, 2015

  =ДАТАМЕС(ДАТА(2015;5;7);10)

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

• This formula subtracts 10 months from today’s date

  =ДАТАМЕС(СЕГОДНЯ();-10)

  =EDATE(TODAY(),-10)

Note: Function DATEMES (EDATE) returns just an integer. To represent it as a date, you must apply a date format to the cell. How to do this is indicated in the article. How to change the date format in Excel.

How to add (subtract) years to a date in Excel

Adding years to dates in Excel is the same as adding months. You need to use the function again DATE (DATE), but this time you need to specify the number of years you want to add:

= ДАТА(ГОД(дата) + N лет; МЕСЯЦ(дата); ДЕНЬ(дата))

= DATE(YEAR(дата) + N лет, MONTH(дата), DAY(дата))

On an Excel sheet, formulas might look like this:

• Add 5 years to the date specified in the cell A2:

  =ДАТА(ГОД(A2)+5;МЕСЯЦ(A2);ДЕНЬ(A2))

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

• Subtract 5 years from the date specified in the cell A2:

  =ДАТА(ГОД(A2)-5;МЕСЯЦ(A2);ДЕНЬ(A2))

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

To get a generic formula, you can enter the number of years in a cell and then refer to that cell in the formula. A positive number will add years to the date, while a negative number will subtract.

Adding (subtracting) days, months and years to a date

If you carefully read the two previous examples, then I think you guessed how to add (or subtract) years, months and days to a date at once in one formula. Yes, with the help of the good old function DATE (DATA)!

• For additions X years, Y months and Z days:

  = ДАТА(ГОД(дата) + X лет; МЕСЯЦ(дата) + Y месяцев; ДЕНЬ(дата) + Z дней)

  = DATE(YEAR(дата) + X лет, MONTH(дата) + Y месяцев, DAY(дата) + Z дней)

• For subtraction X years, Y months and Z days:

  = ДАТА(ГОД(дата) - X лет; МЕСЯЦ(дата) - Y месяцев; ДЕНЬ(дата) - Z дней)

  = DATE(YEAR(дата) - X лет, MONTH(дата) - Y месяцев, DAY(дата) - Z дней)

For example, the following formula adds 2 years and 3 months, and subtracts 15 days from the date in the cell A2:

=ДАТА(ГОД(A2)+2;МЕСЯЦ(A2)+3;ДЕНЬ(A2)-15)

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

For our date column, the formula looks like this:

=ДАТА(ГОД(A2)+$C$2;МЕСЯЦ(A2)+$D$2;ДЕНЬ(A2)+$E$2)

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

How to Add and Subtract Time in Excel

In Microsoft Excel, you can add and subtract time using the function TIME (TIME). It allows you to treat units of time (hours, minutes, and seconds) in the same way as years, months, and days in the function DATE (DATE).

• add time in Excel:

  = A2 + ВРЕМЯ(часы; минуты; секунды)

  = A2 + TIME(часы, минуты, секунды)

• subtract time in Excel:

  = A2 - ВРЕМЯ(часы; минуты; секунды)

  = A2 - TIME(часы, минуты, секунды)

  where A2 is the cell with the time to be changed.

For example, to add 2 hours 30 minutes and 15 seconds to the time in a cell A2 you have to use the following formula:

=A2+ВРЕМЯ(2;30;15)

=A2+TIME(2,30,15)

If you want to add and subtract units of time in one formula, just add a minus sign “-” to the desired values. The following formula adds to the time in the cell A2 2 hours 30 minutes and subtract 15 seconds:

=A2+ВРЕМЯ(2;30;-15)

=A2+TIME(2,30,-15)

You can also enter the desired values ​​in the cells of the sheet and refer to them in the formula:

=A2+ВРЕМЯ($C$2;$D$2;$E$2)

=A2+TIME($C$2,$D$2,$E$2)

If the original cell contains both date and time, the previous formula also works fine:

This is how you can add and subtract dates in Excel. I hope that today you have learned a couple of useful features. In any case, thank you for your attention and hope to see you again!