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 <t.fr...@gmail.com> 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_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

"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 <Mi...@discussions.microsoft.com> 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").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...

>> 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" <(E-Mail Removed)> wrote in message
news:d9af3fb6-d1a5-41d3-b2b9-(E-Mail Removed)...
> On 24 Okt., 11:01, Mike H <Mi...@discussions.microsoft.com> 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").Value)+1+1,0))
>
> works as a charm. I made that one in VBA - not realizing I could have
> use DateAdd