Microsoft Excel - date two months on ward - Asked By farrukh on 11-Jun-12 02:21 PM

Hi All,

I need to pass date parameter in vba  suppose my start date is 1/1/2011 , the below passing end date works fine it get the data till 1/31/2011 means one month data while selecting 1st date of the month , i need some change in below to get end date goes to 3/31/2011 instead of 1/31/2011   means increment of 2 months more?

dEnddate = CDate(DateAdd("d", -1, DateValue(Str(Month(dStartdate) + 1) + "-1-" + Str(Year(dStartdate)))))

wally eye replied to farrukh on 11-Jun-12 07:03 PM
I would recommend you use the spelled out month, maybe break it out into sections to make it more visible:

dEndDate = DateAdd("m", 2, dStartDate)
dEndDate = DateValue(format(dEndDate,"mmm") & "-1-"&format(dEndDate,"yyyy"))
dEndDate = DateAdd("d",-1,dEndDate)

Spelling it out gets it to a more generic, non-region-specific format.

use the EOMonth function

dEndDate = worksheetfunction.EOMonth(dStartDate,2)

Which will return the end of the month, two months from now.
farrukh replied to wally eye on 15-Jun-12 03:36 AM
Hi Wally eye,

Thanks you very much it works for me.