How to Add / Subtract Days, Months and Years to Date

This post will guide you how to add or subtract a given number of days to a date in excel. How to add or subtract weeks to a date using excel formula. How to add or subtract a certain number of months to a date using excel formula. How to add or subtracts a given number of years to a date using an excel formula.  And how to add or subtract a combination of years, months and days to a date.

Assuming that you have a list of date in your worksheets, and you want to perform some arithmetic operations (such as, addition or subtraction) to calculate different time units, such as, you want to add or subtract a number of days to or from a date by using a formula to adjust the date according to your needs.

Add / subtract days to date

If you have a list of date in a range or worksheet, and you want to add or subtract a certain number of days to the date in the range, you can refer to the following general formula:

=Date + days

Or

=Date - days

For example, to add 100 days to a date in Cell B1, then you can write the following formula in Cell C1:

=B1 +100

To subtract 100 days from a date in Cell B1, then you can write the following formula in Cell D1

=B1-100

If you want to add or subtract 100 days to a date in Cell b1, then you can use the TODAY function to build the following formula:

=Today() +100

Or

=Today() – 100

Let’s see the below screenshot to see the returned result by the above formulas:

add days to date1

Add / subtract weeks to date

Assuming that you want to add or subtract a given number of weeks to a certain date in A2, then you can refer to the following generic formula:

=Date +N Weeks * 7  

Or

=Date – N weeks*7

You need to multiply the number of weeks by 7 to get the total number of days, then you can perform arithmetic operations in the same time unit (days).

Let’s see the below examples:

Supposing that you want to add or subtract 5 weeks to a date in A2, you can refer to the following excel formulas:

=A2 + 5*7

=A2 – 5*7

=TODAY() + 5*7

=TODAY() – 5*7

Let’s see the returned results by the above formula from the below screenshot:

add or subtract weeks to date1

Add / subtract months to date

If you want to add or subtract a given number of whole months to a date, you can use the EDATE function to create a generic formula as follows:

=EDATE(start_date, months)

Start_date – the start date

Months – the number of months that you want to add or subtract

If you want to subtract months, you just need to enter a negative number as the months argument in the EDATE function.

Assuming that you want to add or subtract 3 months to the date in A2, you can use the following formula:

=EDATE(A2,3)

=EDATE(A2,-3)

=EDATE(TODAY(),3)

=EDATE(TODAY(),-3)

Let’s see the last result for above formulas:

add or subtract months to date1

Add / subtract years to date

If you want to add or subtract a given number of years to a date, you can create an excel formula based on the DATE function, the YEAR function, the MONTH function and the DAY function.

The generic formula is as follows:

To add years to a date in excel:

=DATE(YEAR(date)+ years, MONTH(date),DAY(date))

To subtract years to a date in excel:

=DATE(YEAR(date)+ years, MONTH(date),DAY(date))

Assuming that you want to add or subtract 2 years to a date in A2, you can use the following formulas:

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

or

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

Let’s see how this formula works:

=YEAR(A2)  

Result: 2018

The YEAR function returns the year component of a date in A2.

=MONTH(A2)

Result: 2

The MONTH function returns the month portion of a date in A2.

=DAY(A2)

Result: 19

The DAY function returns the day of component of a date in A2.

Let’s see the following screenshot to show the result for above DATE formulas: add or subtract years to date1

Add / subtract a combination of days, months and years

If you want to add or subtract a combination of days, months and years to a date in a single formula, you can also use a combination of the DATE function, the YEAR function, the MONTH function and the DAY function.

To add days, months and years in excel:

=DATE(YEAR(start_date) + years, MONTH(start_date) + months, DAY(start_date) + days)

To subtract days, months and years

=DATE(YEAR(start_date) - years, MONTH(start_date) - months, DAY(start_date) - days)

Assuming that you want to add or subtract 2 years, 3 months and 100 days to or from a date in cell A2, you can use the following formulas:

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

Or

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

Then enter the above formula into Cell C2 or C3, then let’s see the returned results:

add or subtract combination day month year1


Related Formulas

  • Convert date to month and year only in excel
    If you want to convert the date to month and year only, you can use “yyyymm” format code within the TEXT function in excel, so you can write down the below TEXT formula:=TEXT(date,”yyyymm”)
  • Convert date to month and day only in excel
    If you want to convert the date (mm/dd/yyyy) to month and day only, you can use “mm dd” format code within the TEXT function in excel, so you can write down the below TEXT formula: =TEXT(date,”mm dd”)
  • Convet Text Date dd/mm/yy to mm/dd/yyyy as Date Format
    If you want to convert dates in the text format to the date format as mm/dd/yyyy, you can use a combination of the DATE function, the VALUE function, the RIGHT function, the MID function, and the LEFT function to create a new excel formula…

Related Functions

  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…
  • Excel EDATE function
    TThe Excel EDATE function returns the serial number that represents the date that is a specified number of months before or after a specified date.The syntax of the EDATE function is as below:=EDATE (start_date, months)…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • Excel TODAY function
    The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The syntax of the TODAY function is as below:=TODAY()…
  • Excel MONTH Function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…

 

Related Posts
How to Count Dates of Given Year in Excel
count dates of given year7

This post will guide you how to count Dates of a certain year in the range of dates using a formula in Excel 2013/2016 or Excel office 365. How do I count dates by a given year in Excel. And ...

How to Sum Values Based on Month and Year in Excel
Sum Values Based on Month 6

We often do some summary or statistic at the end of one month or one year. In these summary tables, there are at least two columns, one column records the date, and the other column records the sales or product ...

How to Convert Date & Time Format to Date in Excel
Convert Date & Time Format to Date 7

Sometimes we want to convert date and time format to date only format in excel for example convert 01/29/2019 06:51:03 to 01/29/2019, we can convert format by Formula or Format Settings. The two ways are easy to learn, so you ...

How to Extract Year from Date & Time Format in Excel
Extract Year from Date & Time Format 6

Sometimes we want to get Year information from date and time format to show the Year only in excel. For example convert 01/29/2019 06:51:03 to 2019, we can get Year information by Formula or Format Settings. The two ways are ...

How to Calculate Remaining Days in a Month or Year in Excel
calculate remaining days5

This post will guide you how to calculate remaining days in a given month or year in Excel. How do I calculate the number of days left in a month or year using a formula in Excel 2013/2016. Calculate Remaining ...

How to Split Date into Day, Month and Year in Excel
split date into day month year10

This post will guide you how to split date into separate day, month and year in excel. How do I quickly split date as Day, Month and Year using Formulas or Text to Columns feature in Excel. Split Date into ...

How to sort Dates by Month and Day Only in Excel
sort dates by month7

This post will guide you how to sort dates or birthdays by month or day only in Excel. How do I sort dates by Year or Month or Day only with a formula in Excel 2013/2016. How to use the ...

How to Add the Current Month or Year in a Cell in Excel
add current date in cell6

This post will guide you how to add the current month or year into a cell or header or footer in your worksheet. How do I insert the current date or time in a cell with a formula in Excel. ...

Converting Dates to Fiscal Quarters and Years
convert dates to fiscal year quarters2

This post will guide you how to convert dates to fiscal quarters or years in Excel. How do I get fiscal quarter from a given date in Excel. How to calculate the fiscal year from a date in Excel. How ...

Count Dates in Given Year/Month/Day in Excel
count dates in given year6

This post will guide you how to count dates in a give year or month or day with a formula in Excel. How do I count cells based on year, month or day in Excel. How to countif by a ...

Sidebar