Calendar in VBA

[Guest]

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,

 

[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

 

[Guest]

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,

 

[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)