Another quirky Date question in VBA - Find Beg&End Dates of next Month

[jfarc]

I will have a Date that is read from a file in VBA. What I need to place in (2) new variables will be the following month's beginning date and ending date.

The Variables in use:
'aDate' = Known Date (this can any day within a given month)
'bDate' = Calculated 1st date of the following month.
'cDate' = Calculated Last date of the following month.

Example:

Given:
'aDate' = 09/21/11

Results:
'bDate' = 10/01/11
'cDate' = 10/31/11

What I've come up with is goofy and I know there's got to be a better way. I need the solution to be in VBA code rather than using Excel formulas. Thanks!

 

[Biz]

Try the code below
msgbox are used to show you the results and used for testing.

Sub Test()
    Dim aDate As Date
    Dim bDate As Date
    Dim cDate1 As Date
    aDate = Format(Date, "dd/mm/yyyy") 'Date function returns the current system date
    MsgBox ("Today's date := " & aDate)
    bDate = DateSerial(Year(aDate), 1 + Month(aDate), 1)
    MsgBox ("1st date of the following month := " & bDate)
    cDate1 = Application.EoMonth(bDate, 0)
    MsgBox ("Last date of the following month := " & cDate1)
End Sub

Biz

 

[jfarc]

That works great! I learned a couple new functions.

Thank you.

 

[Biz]

That works great! I learned a couple new functions.

Thank you.

Actually Thank you I learnt something new trying to solve your problem

Biz

 

[shg]

Nice job, Biz.

 

[Biz]

Nice job, Biz.

Thanks m8.

I still can't believe Date function is same as today() worksheet function.
I am enjoying learning new things.

Biz