By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,939 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,939 IT Pros & Developers. It's quick & easy.

Having problem with DateAdd when calculating next month end date.

100+
P: 167
I am having problems getting the end date to calculate correctly.

I start with Quarter '03/02', (YY/QTR), for this it means it is for the 2nd qtr of 2003. My goal is to get the begin & end dates for each 'month' in the quarter, hence, I want to calculate the begin & end of the month dates for April, May & June 2003 :
so my starting point is:
firstMonthBegin = vcBegMM & "/" & vcBegDD & "/" & vcBegYY
firstMonthEnd = vcEndMM & "/" & vcEndDD & "/" & vcEndYY
which translates to:
firstMonthBegin = '04/01/2003'
firstMonthEnd = '04/30/2003'
*these are the begin & end dates for the first month in the period

Now, I use DateAdd to get the begin & end dates for the next month (May)
SecondMonthBegin = DateAdd("M", 1, CDate(FirstMonthBegin))
SecondMonthEnd = DateAdd("M", 1, CDate(FirstMonthEnd))

****Here is my problem:
* This dateAdd function calculates the begin date (SecondMonthBegin) correctly as '05/01/07' BUT
* it is calculating the end date (SecondMonthEnd) with a value of '05/30/07' and it should be '05/31/07' which is the last day of the month.
Can you tell me what I am doing wrong????
Jan 11 '08 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,357
To get the end date of a month, add one month to the begin date and then subtract 1.
Jan 11 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
Or use DateSerial. The 0 (zero) day of any month is the last day of the previous month!

[font=Shruti]DateSerial(Year(YourDate),Month([/font][font=Shruti]YourDate[/font][font=Shruti])+1,0)

Linq ;0)>
[/font]
Jan 12 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.