Add years, months, days to a date

[wal]

Excel 2003

With a worksheet function, you can add x years, y months, and z days
to a date using a formula like:

=DATE(YEAR(A1)+2, MONTH(A1)+3, DAY(A1)+4) [from Chip Pearson's site]

What is the analogous way of doing this in VBA? DATE is apparently
not a worksheet function available in VBA
(Application.WorksheetFunction). The DateAdd function in VBA lets you
add only one interval (years or months or days) at a time, and the
Help file examples don't go beyond this.

Thanks.

 

[Dave Peterson]

You could look at dateserial:

dim myDate as date

with activesheet.range("A1")
mydate = dateserial(year(.value)+2, month(.value)+3,day(.value)+4)
end with

msgbox mydate

Excel 2003

With a worksheet function, you can add x years, y months, and z days
to a date using a formula like:

=DATE(YEAR(A1)+2, MONTH(A1)+3, DAY(A1)+4) [from Chip Pearson's site]

What is the analogous way of doing this in VBA? DATE is apparently
not a worksheet function available in VBA
(Application.WorksheetFunction). The DateAdd function in VBA lets you
add only one interval (years or months or days) at a time, and the
Help file examples don't go beyond this.

Thanks.

 

[JLatham]

I like Dave Peterson's solution - it's the one I'd go with (actually pretty
much what I use these days), but I'll give you an old function I wrote long
ago that comes up with the same results and gives you some insight into what
all has to go on internally both in the worksheet function from Chip's site
and in the dateserial() function that Dave put up.

This function emulates the formula you got at cpearson.com and should give
the same results given the same inputs. I've added some explanation of the
"why" of some things, such as dealing with the added months.

Function AddToDate(startDate As Date, addYears As Integer, _
addMonths As Integer, addDays As Integer) As Date

Dim newYear As Integer
Dim tempMonth As Integer
Dim newMonth As Integer
Dim newDay As Integer

'break startDate into component parts
'adding years to add as part of 1st step
newYear = Year(startDate) + addYears
newMonth = Month(startDate)
newDay = Day(startDate)
'month is difficult, may cause a
'rollover to another year
tempMonth = newMonth + addMonths
'increment newYear by years worth of
'months added
newYear = newYear + Int(tempMonth / 12)
'use MOD math to determine what month
'the added months creates
newMonth = tempMonth Mod 12
'12 Mod 12 = 0, so if result was
'0 the month is December
If newMonth = 0 Then
newMonth = 12
End If
'put it all back together as new date
AddToDate = DateSerial(newYear, newMonth, newDay + addDays)

End Function

 

[wal]

You could look at dateserial:

dim myDate as date

with activesheet.range("A1")
   mydate = dateserial(year(.value)+2, month(.value)+3,day(.value)+4)
end with

msgbox mydate

Excel 2003

With a worksheet function, you can add x years, y months, and z days
to a date using a formula like:

=DATE(YEAR(A1)+2, MONTH(A1)+3, DAY(A1)+4) [from Chip Pearson's site]

What is the analogous way of doing this in VBA?  DATE is apparently
not a worksheet function available in VBA
(Application.WorksheetFunction).  The DateAdd function in VBA lets you
add only one interval (years or months or days) at a time, and the
Help file examples don't go beyond this.

Thanks.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks! I guess dateserial wasn't mnemonic enough for me to pick it
up when I did a Help file search.

 

[Rick Rothstein]

And just so you are aware of an additional option, if you are doing your
addition one date part at a time (in other words, just days or just months
etc.), there is a DateAdd function available where you can specify which
date part and how many of them you are adding (use a negative quantity for
subtraction). What is nice about the DateAdd function is that you are not
limited to only years, months and days... you can add quarters and weeks as
well. In addition, instead of having to rely on the TimeSerial function to
perform the same functionality with time values as DateSerial does with date
values, there are options available for using DateAdd with time values too
(although one time part at a time). Look DateAdd up in the help files for
complete information.

 

[DOrlesky]

This works perfectly.

Is there a similar way to handle negative dates, omitting weekends and
holidays?

I like Dave Peterson's solution - it's the one I'd go with (actually pretty
much what I use these days), but I'll give you an old function I wrote long
ago that comes up with the same results and gives you some insight into what
all has to go on internally both in the worksheet function from Chip's site
and in the dateserial() function that Dave put up.

This function emulates the formula you got at cpearson.com and should give
the same results given the same inputs. I've added some explanation of the
"why" of some things, such as dealing with the added months.

Function AddToDate(startDate As Date, addYears As Integer, _
addMonths As Integer, addDays As Integer) As Date

Dim newYear As Integer
Dim tempMonth As Integer
Dim newMonth As Integer
Dim newDay As Integer

'break startDate into component parts
'adding years to add as part of 1st step
newYear = Year(startDate) + addYears
newMonth = Month(startDate)
newDay = Day(startDate)
'month is difficult, may cause a
'rollover to another year
tempMonth = newMonth + addMonths
'increment newYear by years worth of
'months added
newYear = newYear + Int(tempMonth / 12)
'use MOD math to determine what month
'the added months creates
newMonth = tempMonth Mod 12
'12 Mod 12 = 0, so if result was
'0 the month is December
If newMonth = 0 Then
newMonth = 12
End If
'put it all back together as new date
AddToDate = DateSerial(newYear, newMonth, newDay + addDays)

End Function

Excel 2003

With a worksheet function, you can add x years, y months, and z days
to a date using a formula like:

=DATE(YEAR(A1)+2, MONTH(A1)+3, DAY(A1)+4) [from Chip Pearson's site]

What is the analogous way of doing this in VBA? DATE is apparently
not a worksheet function available in VBA
(Application.WorksheetFunction). The DateAdd function in VBA lets you
add only one interval (years or months or days) at a time, and the
Help file examples don't go beyond this.

Thanks.