Working Days (Excluding Weekends) (1 Viewer)

[mba_110]

Dear All,

Please note i am doing below exercise to calculate net working days between startdate to endDate however, i manage to come nearby but not exactly what i am looking for.

Working days should be (Sun,Mon,Tue,Wed,Thu)

Holidays (Weekends) Should be (Fri,Sat)

Whenever any date fall within this it should calculate the working days excluding (Fridays & Saturdays).

Option Compare Database
Option Explicit

Public Function Weekdays(ByRef startDate As Date, _
ByRef endDate As Date _
) As Integer
' Returns the number of weekdays in the period from startDate
' to endDate inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Friday and Saturday and
' do not total two per week in number, this function will
' require modification.
On Error GoTo Weekdays_Error

' The number of weekend days per week.
Const ncNumberOfWeekendDays As Integer = 2

' The number of days inclusive.
Dim varDays As Variant

' The number of weekend days.
Dim varWeekendDays As Variant

' Temporary storage for datetime.
Dim dtmX As Date

' If the end date is earlier, swap the dates.
If endDate < startDate Then
dtmX = startDate
startDate = endDate
endDate = dtmX
End If

' Calculate the number of days inclusive (+ 1 is to add back startDate).
varDays = DateDiff(Interval:="d", _
date1:=startDate, _
date2:=endDate) + 1

' Calculate the number of weekend days.
varWeekendDays = (DateDiff(Interval:="ww", _
date1:=startDate, _
date2:=endDate) _
* ncNumberOfWeekendDays) _
+ IIf(DatePart(Interval:="w", _
Date:=startDate) = vbSaturday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=endDate) = vbFriday, 1, 0)

' Calculate the number of weekdays.
Weekdays = (varDays - varWeekendDays)

Weekdays_Exit:
Exit Function

Weekdays_Error:
Weekdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Weekdays"
Resume Weekdays_Exit
End Function

Any help in above is highly appreciated.

 

[Minty]

What isn't working ? Are you getting an error ?

Also this formatting

varDays = DateDiff(Interval:="d", _
date1:=startDate, _
date2:=endDate) + 1

Is a little odd

VarDays = DateDiff("d",startDate,endDate)
is sufficient

Give us some examples of dates that don't work?
Do you also need to include public holidays?

 

[arnelgp]

this is much simpler:

diff = DateDiff("d", StartDate, EndDate)

For i = 0 To diff
strDayName = WeekdayName(Weekday(DateAdd("d", i, StartDate)))
If InStr("Friday/Saturday", strDayName) = 0 Then
numOfWorkingDays = numOfWorkingDays + 1
End If
End Sub

or

dim i as date

For i = StartDate To EndDate
strDayName = WeekdayName(Weekday( i))
If InStr("Friday/Saturday", strDayName) = 0 Then
numOfWorkingDays = numOfWorkingDays + 1
End If
End Sub

 

[mba_110]

Dear All,

The problem is when I use this module its not accurate in calculating working days for example start Date 20 June 2015 and end date 21 July 2015 than result should be = 23, but its showing me 20 only which is wrong and make big difference in calculation.

Secondly I need to include 23 Sep as fixed public holiday every year and if 23 Sep is weekend (Saturday or Friday) than it should take very next working day which should be 24 or 25 Sep whatever it is but next Sunday and exclude it from calculating in working days.

Thirdly I want to have space to format module where I can include manually public/festival holidays (Start Date, End date) based on each year declaration because its not fixed every year, so I have to put it manual and function should not calculate it in working days.

Since I am new to programming I don’t know much about functions, If you can help me to achieve it will be great.

Thanks & regards,

Mirza Ali

 

[Minty]

There are a number of ways of achieving this but by far the most straight forward for a beginner is to have a Calendar Table and have a field for working day - simply set your local public holidays as non-working.
You can then have what ever days you desire set as working and non working, and adapt the code already given to do the calculations for you.

 

[mba_110]

Can you please tell me the fields name that i have to create in Calendar table because its looks like i have to enter all the date manually right from the beginning because some dates in database is going back to 15 to 20 Years, what field names data types i have to select? is their any option to auto fill calendar date in to table?

Please is it not possible that you can amend the my above code to meet my requirement it will be in one function and can avoid repetition work or at least wrong calculation of working days.

Many thnkx for your idea.

 

[Minty]

If you need to go back the easiest way would be to create the data in Excel using autofill, Then import into access. There are also funtions for Weekday etc in Excel. The only thing you would need to do is find a list of your local holidays to set the Working Day field.

If you are going to make a Calendar table I would suggest fields as being useful in calculations (Some of these are obviously available as functions in Access but I've found it handy to be able to have them in a table)

CalendarDate (The actual date - don't use Date as a field name it's a reserved word)
IsWeekend
YearNo
QuarterNo
MonthNo
DayOfYearNo
DayNo
WeekNo
WeekDayNo
WorkingDay

All of these will be useful at some point if you work with dates, and can save some effort. The purists will probably bemoan the use of this as 95% can be achieved using VBA functions - But I find this approach much easier.

Once you have the table you can then start to build various functions to determine working days forwards or backwards.

 

[namliam]

Thirdly I want to have space to format module where I can include manually public/festival holidays (Start Date, End date) based on each year declaration because its not fixed every year, so I have to put it manual and function should not calculate it in working days.

You would be surpriced how many holidays can be calculated once you think about it.

Even things like easter can be done, most other holidays go back to first tuesday in july or something of somesort. Very rare to have truly random holidays.

 

[mba_110]

Dear All,

I have made the table with necessary fields to calculate working days.

Table data is open to change necessary holidays and can be change work and weekend days or holidays anytime.

I need code that should depend on Type and Nwd (net Work Days).

for Example on Access form if i put start date and end date it should be verified from My calendar_table (Can be use query for more perfection) and count Nwd from start date to end date regardless to weekdays because i already set this in table itself as NWD, by this way i can easily change anything in Calendar schedule that can count exact numbers of working days in NWD field for every particular date.

If any date/period is falling as weekend or holiday than NWD will not count as 1 it will be zero 0, so that total working days can be perfect depending on NWD of each period that selected on form.

Calendar_table that i have made is contain the period from 1991 to 2040 and i will put the holidays one by one because can't go back to 20 Years in one time.

Is anybody can help me with this?

MBA

 

[arnelgp]

You only need to put Holidays on your table, regular days including thursdays and fridays can be computed on a fly.

 

[namliam]

Well this is a dimentional table Arnel, yes some things can be calculated but dimensional tables are made so that non-def-non-tech savy people can work with it...

You should be able to "Cross join" your Original table with this one.

Using something like

Select OriginalTable.startdate, OriginalTable.enddate, sum(nwd)
from OriginalTable, DimTable
where DimTable.Calanderdate between OriginalTable.Startdate and OriginalTable.Enddate
group by OriginalTable.startdate, OriginalTable.enddate

 

[mba_110]

Dear Namliam,

I am sorry to ask again where do i have to use above code mentioned by you and for ease of reference i feel to mention again the below details.

Please note the following.

I have form called frmApplication and table called calendar_table having all data and schedule of leave as mentioned above and i already updated the holidays as well, so now i have to take startdate and endDate of form fields whatever is selected on frmApplication to match and verify NWD (net working days) from Calendar_table's CalendarDate field.

So, in future if i need to change or add anymore holidays it will be handy with NWD and doesn't required new coding.

Holidays & Weekend segment will be added later on form itself.

Yes its something like "non-def-non-tech savy people can work with it..."

I am also doing a helping hand for someone without mentioning non-def-non-tech savy...

Many thnx for your help.

MBA

 

[namliam]

If you want to check this just against 2 dates on your form, simply make a new query based on the Calander_table *** and use the expresion builder to get the controls from the form...\

Use google to find out how to use the builder if you dont already know.

*** more widely assumed naming convention is to use tbl_Calander but atleast you have a naming convention which is 2 up from a lot of people.

 

[namliam]

Yes its something like "non-def-non-tech savy people can work with it..."

This was not aimed at you, more of a general statement....

These people, our customers, wouldnt know a table, query or function if it hit them in the head. :banghead: