How to Use the DATE Function to Add Months to a Date in Excel

The DATE function in Excel is a useful tool for creating and manipulating dates. It allows you to specify a year, month, and day, and returns a valid date value. You can use the DATE function to add or subtract months from a given date, by changing the month argument. In this article, we will explain how the DATE function works, how to use it to add months to a date, and some alternative approaches.

Basic Theory

The syntax of the DATE function is:

=DATE(year, month, day)

The year argument can be a positive or negative integer, representing the year of the date. If the year argument is between 0 and 1899, Excel adds 1900 to it. For example, DATE(21, 1, 1) returns January 1, 1921, and DATE(-100, 1, 1) returns January 1, 1900.
The month argument can be a positive or negative integer, representing the month of the date. If the month argument is greater than 12, Excel adds that number of months to the first month in the year specified. For example, DATE(2021, 13, 1) returns January 1, 2022. If the month argument is less than 1, Excel subtracts that number of months from the first month in the year specified, and subtracts one year. For example, DATE(2021, -1, 1) returns November 1, 2020.
The day argument can be a positive or negative integer, representing the day of the date. If the day argument is greater than the number of days in the month specified, Excel adds that number of days to the first day in the month specified. For example, DATE(2021, 1, 32) returns February 1, 2021. If the day argument is less than 1, Excel subtracts that number of days from the first day in the month specified, and subtracts one month. For example, DATE(2021, 1, -1) returns December 30, 2020.

Procedures

To use the DATE function to add months to a date, follow these steps:

Enter the date value that you want to add months to in a cell, or reference a cell that contains the date value. For example, if you want to add months to January 1, 2021, you can enter 1/1/2021 in cell A1, or reference another cell that contains this value, such as =B1.
In another cell, enter the number of months that you want to add to the date value, or reference a cell that contains the number of months. For example, if you want to add 3 months to the date value, you can enter 3 in cell B2, or reference another cell that contains this value, such as =C2.
In a third cell, enter the DATE function, using the cell references from the previous steps as the arguments. For example, if you entered the date value in cell A1, and the number of months in cell B2, you can enter =DATE(YEAR(A1), MONTH(A1) + B2, DAY(A1)) in cell C3. This will return the date value after adding the number of months specified. In this example, the result is April 1, 2021.
Format the result cell as a date, using the format that you prefer. For example, you can format the result cell as mm/dd/yyyy to display the date as 04/01/2021.

Explanation

The DATE function can handle different scenarios when adding months to a date, such as crossing over a year boundary, or dealing with leap years. Here are some examples of how the DATE function works in different cases:

If you add 12 months to a date, the result will be the same date in the next year. For example, =DATE(2021, 1, 1) + 12 returns January 1, 2022.
If you add a negative number of months to a date, the result will be the same date in the previous year. For example, =DATE(2021, 1, 1) - 12 returns January 1, 2020.
If you add a fractional number of months to a date, the result will be rounded down to the nearest integer. For example, =DATE(2021, 1, 1) + 3.5 returns April 1, 2021, the same as =DATE(2021, 1, 1) + 3.
If you add a number of months that results in a month that does not exist, such as 13 or 0, the result will be adjusted to the next or previous valid month. For example, =DATE(2021, 12, 1) + 1 returns January 1, 2022, and =DATE(2021, 1, 1) - 1 returns December 1, 2020.
If you add a number of months that results in a day that does not exist in the resulting month, such as February 30 or April 31, the result will be adjusted to the last valid day in the resulting month. For example, =DATE(2021, 1, 31) + 1 returns February 28, 2021, and =DATE(2020, 1, 31) + 1 returns February 29, 2020 (a leap year).

Example

Suppose you want to calculate the due date of a loan that has a term of 36 months, starting from January 15, 2021. You can use the DATE function to add 36 months to the start date, and get the due date. Here is how you can do it:

Enter the start date of the loan in cell A1, such as 1/15/2021.
Enter the term of the loan in cell B1, such as 36.
In cell C1, enter the DATE function, using the cell references from the previous steps as the arguments, such as =DATE(YEAR(A1), MONTH(A1) + B1, DAY(A1)). This will return the due date of the loan, which is January 15, 2024.
Format the result cell as a date, using the format that you prefer, such as mm/dd/yyyy.

Here is a table that shows the input and output values:

Start Date	Term (months)	Due Date
1/15/2021	36	1/15/2024

Alternative Approaches

There are other ways to add months to a date in Excel, besides using the DATE function. Here are some alternative approaches:

You can use the EDATE function, which returns the date that is the specified number of months before or after a given date. The syntax of the EDATE function is:

=EDATE(start_date, months)

The start_date argument is the date that you want to add or subtract months from. It can be a date value, a cell reference, or a formula that returns a date value.
The months argument is the number of months that you want to add or subtract from the start_date. It can be a positive or negative integer, a cell reference, or a formula that returns an integer.

For example, if you want to add 3 months to January 1, 2021, you can use the EDATE function as follows:

=EDATE(1/1/2021, 3)

This will return April 1, 2021.

You can use the DATEVALUE function, which converts a text string that represents a date into a date value. The syntax of the DATEVALUE function is:

=DATEVALUE(date_text)

The date_text argument is the text string that represents a date. It can be a text value, a cell reference, or a formula that returns a text value. The date_text argument must be in a format that Excel recognizes as a valid date, such as mm/dd/yyyy or dd-mmm-yyyy.

For example, if you want to add 3 months to January 1, 2021, you can use the DATEVALUE function as follows:

=DATEVALUE("1/1/2021") + 3 * 30

This will return March 31, 2021. Note that this approach assumes that each month has 30 days, which may not be accurate for some months.