How to get Number of working days in a month Excluding Weekends

[rizunb]

Hi People
I was wondering if any one can help me in this

I need to find out the number of hours an employee can work in a month. Now for that ofcourse I first need to know that how many working days(Exclude sat's and sun's) are there in a month and then just multiply it by 7 to get the total number of working hours

All I know is the Month which can be in any format , I mean like it can be "MAY" or can be "5". thats all I know that from this i need to fiqure out how many working days are there in this month.

I need to make sure that those days does not include the saturdays and sundays.

If any one can write me some code for that, it will be gr8.
I am having a hard time getting this rite
I am sure you people have done this type of stuff
Thanks in advance


Rizzzzzzzz

 

[TomKane]

Do you have to cater for public/bank holidays as well?

What we do is we have a file with only the non working days and we use that in our calculations.

Hopr this is of some help...

 

[rizunb]

Public holidays doesnt matter. I know how to tackle them
All i need to know is how to get the Number of working days in a month considering that all we know is the Month.

I cant use a file ,I am actually making a time system for a firm. using VB-Access. I should use a built-in or create a function that gives me total number of working days if i input just the month.

 

[TomKane]

There is an excel command called workday if you include the excel object in your references you can do something like the following:

Public Function CalculateWorkDays(dtLocalStartDate As Date, dtLocalEndDate As Date) As Long
CalculateWorkDays = Excel.WorksheetFunction.WorkDay(dtLocalStartDate, dtLocalEndDate)
End Function

A bit of information from the annoying excel paper clip:
WORKDAY(start_date,days,holidays)

Start_date is a date that represents the start date.

Days is the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.

Holidays is an optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells or an array constant of the numbers that represent the dates. For more information about how Microsoft Excel works with dates, click . For more information about array constants, click .

Hopefully this is of some help

 

[CCLINT]

Try this:

Public Function CountWeekDays(ByVal dtStartDate As Date, ByVal dtEndDate As Date, _
Optional ByVal lFirstDayOfWeek As VbDayOfWeek = vbMonday) As Long
Dim lWeekDays As Long
Dim dtCurrentDate As Date
Dim iNeg As Integer

iNeg = Sgn(dtEndDate - dtStartDate)
If iNeg = 0 Then
iNeg = 1
ElseIf iNeg = -1 Then
dtCurrentDate = dtStartDate
dtStartDate = dtEndDate
dtEndDate = dtCurrentDate
End If

lWeekDays = (dtEndDate - dtStartDate) \ 7
dtCurrentDate = dtStartDate + (lWeekDays * 7)
lWeekDays = lWeekDays * 5

Do Until dtCurrentDate > dtEndDate
lWeekDays = lWeekDays + Abs(Weekday(dtCurrentDate, lFirstDayOfWeek) < 6)
dtCurrentDate = dtCurrentDate + 1
Loop
CountWeekDays = lWeekDays * iNeg
End Function

If you want to check holidays, use that list or db table and check if any of the holiday dates which fall on a weekday, also fall in with-in that date range, and subtract the count from the total workdays.

 

[rizunb]

Well Thanks to all for replying
But problem here is that you are giving me methods that takes 2 dates as input

All i have is just the MONTH
I dont have dates
Example is like &quot;may 2003&quot; or in interger &quot;05/2003&quot;
now from just this information i need to know that how many working days are there in this month

 

[Hypetia]

Here is a simple solution. You only need to pass the Month number to the WorkingDays function. Year is optional and current year is used if you don't supply.
___

Function WorkingDays(M As Integer, Optional Y As Integer) As Integer
If Y = 0 Then Y = Year(Date$)
Dim D As Integer, xDate As Date
Dim DayofWeek As String
xDate = DateSerial(Y, M, 1)
Do
DayofWeek = Format$(xDate, &quot;dddd&quot
If DayofWeek <> &quot;Saturday&quot; And DayofWeek <> &quot;Sunday&quot; Then _
WorkingDays = WorkingDays + 1
xDate = DateAdd(&quot;d&quot;, 1, xDate)
Loop While Month(xDate) = M
End Function

Private Sub Form_Load()
Dim N As Integer
For N = 1 To 12
Debug.Print N, MonthName(N), WorkingDays(N)
Next
End
End Sub

 

[CCLINT]

<sigh> That rejection did come pretty fast with-out trying to consider how to work it out! Some of these [free] ideas and code do not happen overnight...

You need to put some simple though into it also...

And, I believe you have about 5 other threads which you have started which need responses from you. Take this first into consideration before starting any more new threads please.
=====================================================

Sooooo, format the date and pass what is needed:

iMonth = Month(&quot;May/2003&quot
iYear = Year(&quot;May/2003&quot

Date1= DateSerial(iYear ,iMonth ,1)
Date2=DateSerial(iYear ,iMonth +1,0)

You SHOULD however use:
(&quot;5/2003&quot
instead of &quot;May/2003&quot;, so it is internationally compatable.

 

[Hypetia]

Although the code I posted above is okay, I suggest you to change the line:
xDate = DateAdd(&quot;d&quot;, 1, xDate)
to this:
xDate = xDate + 1
It is simpler and faster.

Also remove the variable D As Integer. I didn't use it anywhere.

 

[MichaelRed]

see faq191-261 the ~ complete soloution.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places