An alternative for the edate function...

  • Thread starter Thread starter TFriis
  • Start date Start date
T

TFriis

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.
 
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.
Click to expand...

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_date),
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
 
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
Click to expand...


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").Value)+1+1,0))

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

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

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