vba code to determine the full end of month date given the month and year

Thrawn3000
Thrawn3000 used Ask the Experts™
on

Hi Experts
I need to use vba code to determine the end of month date given the month and year
example:  09/10  is 30/09/2010   or 10/10 is 31/10/10.


Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
Given a month and a year, there is a little trick you can use with DateSerial:
Dim TheYear As Long, TheMonth As Long

TheYear = 2010
TheMonth = 9

Debug.Print DateSerial(TheYear, TheMonth + 1, 0)

Open in new window

Author

Commented:
HI matthewspatrick
Thank you does excatly what I need it to do

cheers
Top Expert 2016

Commented:

assuming your month/year variable is xVar


LastDay=dateserial(Int("20" & split(xVar,"/")(1),Int(split(xVar,"/")(0) + 1,0)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial