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

 

[p45cal]

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY()))

This could get untidy so how about your own function?:
Called thus:
=mydateadd("m",1,TODAY())
=mydateadd("m",-1,"31/3/2009")
=mydateadd("m",1,"31/3/2009")
or with a cell ref:
=mydateadd("m",1,D23)

supported of course by the udf in code:

Function myDateAdd(interval, howmany, adate)
myDateAdd = DateAdd(interval, howmany, adate)
End Function

works here.
You may have to format the result as a date.

 

[Jonmo1]

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

The DATEDIF function should do what you want...
IT's an undocumented function for some reason..but very handy.

Search Google, you should find some information on it.
I think CPearson's website has it covered..
http://www.cpearson.com/Excel/datedif.aspx

 

[Scott Huish]

DATEDIF doesn't return a Date.

 

[Jonmo1]

DATEDIF doesn't return a Date.

Argh!!!
I should have known that...

 

[xlsmaven]

Thanks for the reply. I found EDate("3/31/2009", +/- # of months works)
ie. +1 = 4/30/2009
-13 = 2/29/2008
-25 = 2/28/2007

 

[p45cal]

Thanks for the reply. I found EDate("3/31/2009", +/- # of months works)
ie. +1 = 4/30/2009
-13 = 2/29/2008
-25 = 2/28/2007

Which doesn't quite do what you asked viz. "Is there a DateAdd() function in Excel?" as you're confining yourself to months differences with Edate.

 

[xlsmaven]

I previously wrote a similar MyDateAdd function. I was surprised that there is not an equivilent function natively in Excel for this. In this case EDate will work since it is only needing month to date for this month vs. month to date for the prior month.

 

[thenderson1313]

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

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.

 

[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.

I just noticed that it wouldn't work with the "-1 month" scenario because of differing days in the months. Doing -1 month for 3/31/2017 yields 3/3/2017 (28 days in February + the 3 day difference).