Calendar in VBA

J

Jeff

Hello,

Is it possible to design a VBA macro that would return the number of days in
a specific month?
Ex. I'd input a month(December) and the VBA would return (31).
Thanks,
 
J

Jason Morin

The number of days in a month is affected by the year
(leap year), so with a formula use:

=EOMONTH((A1&"-"&C1)*1,0)-(A1&"-"&C1)*1+1

where A1 holds the month and C1 holds the year (like
2004).

If the formula returns the #NAME? error, install and load
the Analysis ToolPak add-in under Tools > Add-Ins.

HTH
Jason
Atlanta, GA
 
J

Jeff

Thank you for help.
Is there a way to get the number of Saturdays and Sundays in specific month ?
Ex. I'd input November and Excel would return 8.
Regards,
 
D

Dave Peterson

Until Jason comes back...

One way with the real date in A1:

=DAY(EOMONTH(A1,0))-NETWORKDAYS(EOMONTH(A1,-1),EOMONTH(A1,0))


=DAY(EOMONTH(A1,0))
is another way to get the number of days in a month.

=EOMONTH(A1,-1)
is the date of the last day of the previous month.

=EOMONTH(A1,0)
is the date of the last date of the month in A1

=networkdays()
is the number of working days (yech!) between two dates. It ignores Saturday
and Sunday.

If I had "December" in a cell, I'd either type it as a date (12/01/2004,
mm/dd/yyyy) and format it to only show the month.

Or I'd use a formula like Jason's in a helper cell and use that as the reference
in my formula.

=(A1&"-"&C1)*1
(format as date and you'll see it)
 
Top