View Full Version : find number of work days in month

06-12-2012, 10:37 AM
So I am trying to find the number of work days in a month given that the month is a user inputted integer.

so this is what assigns the month

month = InputBox("Enter the month as an number (EX. If you want to enter for January then enter 1).", "Info", "99")

then i want to find the nubmer of work days from month/1/current year to the end of the month.

so i have

startdate = month / 1 / 12

enddate = WorksheetFunction.EoMonth(startdate, 0)
workdaysinmonth = Application.WorksheetFunction.NetworkDays(startdate, enddate)

but it thinks i am trying to divide the month by one and by 12 and never gives me a date.

its like i need something like

startdate = &month& #1/12#

but that is not recogized by vba.

please help

06-12-2012, 11:07 AM
You should probably change the variable name as Month is a reserve word.

startdate = MyMonth & "/1/12"

06-12-2012, 11:40 AM
If you were to use Option Explicit at the start of your code, it would pick up errors such as typos.undeclared variables and use of reserved words.

06-12-2012, 02:30 PM
I think you want to use DateSerial(..), as well as Option Explicit as well as explicitly Dim-ing your variables

Option Explicit
Sub demo()
Dim StartDate As Date, EndDate As Date
Dim Mon As Long
Mon = InputBox("Enter the month as an number (EX. If you want to enter for January then enter 1).", "Info", "99")

StartDate = DateSerial(2012, Mon, 1)
EndDate = DateSerial(Year(StartDate), Month(StartDate) + 1, 0)
End Sub


Bob Phillips
06-12-2012, 03:34 PM
You could also use

EndDate = StartDate + 31 - Day(StartDate + 31)

06-13-2012, 07:50 AM
The last one from you "xld" seems quit simple and nice. Well. these was new thing that I get to learn here. These formula should mostly be used by people going to office. About me I used to calculate the working day at the start of every month. So it great to be the part of these thread and discussion.