An alternative for the edate function...
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. |
An alternative for the edate function...
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 |
An alternative for the edate function...
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. |
An alternative for the edate function...
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 :) |
An alternative for the edate function...
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 :) |
All times are GMT +1. The time now is 06:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com