Is there a DateAdd() function in Excel?

[xlsmaven]

Here are some VBA examples that I would like to mimic in Excel formulas.
'Add a month to today.
?DateAdd("m",1, date())
12/17/2009

'Subtract a month from 3/31/2009
?DateAdd("m",-1, "3/31/2009")
2/28/2009

'Add a month to 3/31/2009
?DateAdd("m",1, "3/31/2009")
4/30/2009

 

[thenderson1313]

The "Date" function would work for your purposes. It's in the format of =Date("y","m","d").

Depending on what you need it for, and how dynamic you want it to be, you can use references to another cell that contains the original date, and references to cells that contain the changes to be made. I use a version of this to find the first day of the month following 30 days from an employee's start date (including the start date, which is why I use 29 in the formula). The date is saved in column A, so after headers, the first date is in A2. My formula is =Date(Year(A2+29),Month(A2+29)+1,1). In the event that the month rolls from December to January because of the "+1" in the month section, the year is automatically rolled up to the next year. Simply format the cells for "Date", and it comes out fine.

Examples:
for start date 1/30/2017, formula returns 3/1/2017
for start date 1/31/2017, formula returns 4/1/2017
for start date 11/28/2017, formula returns 1/1/2018

At it's most dynamic, you could put your original date in A1, Years to Add/Subtract in A2, Months to Add/Subtract in A3, and Days to Add/Subtract in A4, with the following formula in A5: =Date(Year(A1)+A2,Month(A1)+A3,Day(A1)+A4). Positive numbers will add, negative numbers will subtract.

To accommodate for differing numbers of days per month, the dynamic formula would need to be: =DATE(YEAR(A1)+A2,MONTH(A1)+A3,IF(MONTH(A1)+A3=MONTH(DATE(YEAR(A1)+A2,MONTH(A1)+A3,DAY(A1)+A4)),DAY(A1),DAY(DATE(YEAR(A1)+A2,MONTH(A1)+A3+1,1)-1))+A4)

This version checks the date generated by the original code and makes sure that the month generated matches with the month that should be generated (caused by discrepancies in the Day coding).

Examples:
3/27/2017 Add 1 month = 4/27/2017
3/31/2017 Add 1 month = 4/30/2017 (instead of 5/1/2017 with old code)
3/27/2017 Sub 1 month = 2/27/2017
3/31/2017 Sub 1 month = 2/28/2017 (instead of 3/3/2017 with old code)

 

[Scott Huish]

Why wouldn't you just use EDATE? It's been standard since XL2007

=EDATE(A1,B1)

Excel 2010
	A	B	C
1	3/27/2017	1	4/27/2017
2	3/31/2017	1	4/30/2017
3	3/27/2017	-1	2/27/2017
4	3/31/2017	-1	2/28/2017
Sheet1

 

[thenderson1313]

Why wouldn't you just use EDATE? It's been standard since XL2007

=EDATE(A1,B1)

Excel 2010

	A	B	C
1	3/27/2017	1	4/27/2017
2	3/31/2017	1	4/30/2017
3	3/27/2017	-1	2/27/2017
4	3/31/2017	-1	2/28/2017

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

The OP was looking for a function similar to DATEADD from VBA, which has the advantage of being able to add or subtract years, months, or days. EDATE is only good if you are looking to adjust months. The OP appears to only need to change the date by months, so it works for them, but I wanted to provide a more diverse formula for other people searching for a similar solution, but who doesn't want it limited to just adding or subtracting months. I do admit that it makes my code more elegant though, so thanks for reminding me of how that function works (I haven't used it in forever).

Dynamic formula changed to =DATE(YEAR(A1)+A2,MONTH(A1)+A3,DAY(EDATE(A1,A3))+A4)