Is there a good alternative for the edate function?

Don't want to use it in vba - due to the tool analysis toolpack. Not
everyone has it installed.

On 24 Okt., 10:41, TFriis wrote:
Is there a good alternative for the edate function?

Don't want to use it in vba - due to the tool analysis toolpack. Not
everyone has it installed.

Never mind - found a solution :]

Sub AlternativeEdateFunction()

Dim start_date As Date
Dim No_Months As Integer

start_date = VBA.Date
No_Months = 3

Range("A1") = VBA.DateSerial(VBA.Year(start_date),
VBA.Month(start_date) + No_Months,
Application.WorksheetFunction.Min(VBA.Day(start_da te),
VBA.Day(VBA.DateSerial(VBA.Year(start_date), VBA.Month(start_date) +
No_Months + 1, 0))))

End Sub

Hi,

It's not clear from your post what you want.
Edate formula
=EDATE(A1,1)

VB equivalent
mydate = DateAdd("m", 1, Range("a1").Value)

On the worksheet without ATP
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

Mike

"TFriis" wrote:

Is there a good alternative for the edate function?

Don't want to use it in vba - due to the tool analysis toolpack. Not
everyone has it installed.

On 24 Okt., 11:01, Mike H wrote:
Hi,

It's not clear from your post what you want.
Edate formula
=EDATE(A1,1)

VB equivalent
mydate = DateAdd("m", 1, Range("a1").Value)

On the worksheet without ATP
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

Mike

"TFriis" wrote:
Is there a good alternative for the edate function?

Don't want to use it in vba - due to the tool analysis toolpack. Not
everyone has it installed.


Your worksheet formula doesn't work; try the date 31/01/2008 ("dd/mm/
yyyy") (It returns 02/03/2008 ("dd/mm/yyyy") - where 29/02/2008 ("dd/
mm/yyyy") is the correct date.

I do believe

=MIN(DATE(YEAR(Range("a1").Value),MONTH(Range("a1" ).Value)
+1,DAYRange("a1").Value)),
DATE(YEAR(Range("a1").Value),MONTH(Range("a1").Val ue)+1+1,0))

works as a charm. I made that one in VBA - not realizing I could have
use DateAdd :)

How about these instead...

It's not clear from your post what you want.
Edate formula
=EDATE(A1,1)

VB equivalent

D = DateSerial(Year(Range("A1").Value), Month(Range("A1").Value) + 2, 0)

On the worksheet without ATP

=DATE(YEAR(A1),MONTH(A1)+2,0)

--
Rick (MVP - Excel)


"TFriis" wrote in message
...
On 24 Okt., 11:01, Mike H wrote:
Hi,

It's not clear from your post what you want.
Edate formula
=EDATE(A1,1)

VB equivalent
mydate = DateAdd("m", 1, Range("a1").Value)

On the worksheet without ATP
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

Mike

"TFriis" wrote:
Is there a good alternative for the edate function?

Don't want to use it in vba - due to the tool analysis toolpack. Not
everyone has it installed.


Your worksheet formula doesn't work; try the date 31/01/2008 ("dd/mm/
yyyy") (It returns 02/03/2008 ("dd/mm/yyyy") - where 29/02/2008 ("dd/
mm/yyyy") is the correct date.

I do believe

=MIN(DATE(YEAR(Range("a1").Value),MONTH(Range("a1" ).Value)
+1,DAYRange("a1").Value)),
DATE(YEAR(Range("a1").Value),MONTH(Range("a1").Val ue)+1+1,0))

works as a charm. I made that one in VBA - not realizing I could have
use DateAdd :)