Number of working days

  • Thread starter Thread starter Paulo Ferreira
  • Start date Start date
P

Paulo Ferreira

Hello,

I don't know how to calculate the number of working days between dates.
Can someone help me?
Thanks.

Paulo Ferreira
 
Function TotalWorkDays(dtmFromDate As Date, dtmToDate As Date) As Integer
Dim dbs As Database
Dim rstHoliday As Recordset 'Record set to eliminate Holidays
Dim blnFoundWorkDay As Boolean 'Found Next Business Day
Dim lngDaySpan As Integer

lngDaySpan = DateDiff("d", dtmFromDate, dtmToDate)
Set dbs = CurrentDb()
Set rstHoliday = dbs.OpenRecordset("tblHoliday", dbOpenSnapshot,
dbReadOnly)
Do While dtmFromDate <= dtmToDate
blnFoundWorkDay = False
If Weekday(dtmFromDate, vbMonday) > 5 Then 'It is Saturday or Sunday
lngDaySpan = lngDaySpan - 1
Else
'See if the day is a holiday
rstHoliday.FindFirst ("[holiday_date] = #" & dtmFromDate & "#")
If Not rstHoliday.NoMatch Then 't is a holiday
lngDaySpan = lngDaySpan - 1
End If
End If
dtmFromDate = DateAdd("d", 1, dtmFromDate)
Loop
TotalWorkDays = lngDaySpan + 1

End Function
 
Sorry if this is a duplicate post, but I got an error trying to post. Here
is the function that will do it.

Function TotalWorkDays(dtmFromDate As Date, dtmToDate As Date) As Integer
Dim dbs As Database
Dim rstHoliday As Recordset 'Record set to eliminate Holidays
Dim blnFoundWorkDay As Boolean 'Found Next Business Day
Dim lngDaySpan As Integer

lngDaySpan = DateDiff("d", dtmFromDate, dtmToDate)
Set dbs = CurrentDb()
Set rstHoliday = dbs.OpenRecordset("tblHoliday", dbOpenSnapshot,
dbReadOnly)
Do While dtmFromDate <= dtmToDate
blnFoundWorkDay = False
If Weekday(dtmFromDate, vbMonday) > 5 Then 'It is Saturday or Sunday
lngDaySpan = lngDaySpan - 1
Else
'See if the day is a holiday
rstHoliday.FindFirst ("[holiday_date] = #" & dtmFromDate & "#")
If Not rstHoliday.NoMatch Then 't is a holiday
lngDaySpan = lngDaySpan - 1
End If
End If
dtmFromDate = DateAdd("d", 1, dtmFromDate)
Loop
TotalWorkDays = lngDaySpan + 1

End Function
 
Paulo,

Take out all the references to blnFoundWorkDay. They are left over from a
previous version and I forget to take them out.
 
Paul,

A much simpler way, by Douglas Steele MVP:

myInt = DateDiff("d", dte1, dte2) - _
DateDiff("ww", dte1, dte2, 1) * 2 - _
IIf(Weekday(dte2, 1) = 7, _
IIf(Weekday(dte1, 1) = 7, 0, 1), _
IIf(Weekday(dte1, 1) = 7, -1, 0))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
That does not account for hoiidays

Graham R Seach said:
Paul,

A much simpler way, by Douglas Steele MVP:

myInt = DateDiff("d", dte1, dte2) - _
DateDiff("ww", dte1, dte2, 1) * 2 - _
IIf(Weekday(dte2, 1) = 7, _
IIf(Weekday(dte1, 1) = 7, 0, 1), _
IIf(Weekday(dte1, 1) = 7, -1, 0))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...
 
That's true, but then Paolo didn't ask for one that did.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
You are correct; however, he did say WORKING days. Would you not assume that
holidays would be excluded from working days?
 
Klatuu said:
You are correct; however, he did say WORKING days. Would you not assume that
holidays would be excluded from working days?

:
Click to expand...
No, it depends upon what you call a holiday and a work day.
Some plants work 24x7x365. A swing shift works 7 day and off 2.
When they come back to work the swing to the 2nd shift then to the 3rd.
On their 3rd shift they work 7 day and are off 3 days, then swing back to
the 1st shift when they come back to work.

If you are off work on a holiday, it is just another day off. If you
are working
on a Holiday, you get paid double time. It depends upon the pay policy
of the
company.

Ron
 
That is even more reason to have a Holiday table. In the scenerio you are
describing, it would take quite a bit more logic like what employee, what
shift, work schedule, etc. So, nothing will work for everything, but the
routine I provided could be modified to take those circumstances into
consideration. Ok, now lets talk Vacation schedules, and Planned Plant
Shutdowns - We have 2 per year here, 4th of July week and week between
Christmas and New Years.
 
You are right Klatuu, I want real working days and your post really help me.
Thanks to all of you.

Paulo Ferreira
 
Not normally. Although many people do want to take account of holidays, many
more only want to know the number of business days, regardless of holidays.
But in case Paolo does want that, he may also like to consider the
following. It's just another way to do the same thing.

Public Function WorkingDays(dte1 As Date, dte2 As Date) As Integer
'Calculates the number of working days between two dates, taking
'account of the holidays listed in tblHolidays.HolidayDate.
Dim rs As DAO.Recordset
Dim iDays As Integer
Dim sSQL As String

iDays = DateDiff("d", dte1, dte2) - _
DateDiff("ww", dte1, dte2, 1) * 2 - _
IIf(Weekday(dte2, 1) = 7, _
IIf(Weekday(dte1, 1) = 7, 0, 1), _
IIf(Weekday(dte1, 1) = 7, -1, 0))

sSQL = "SELECT Count(*) As HolidayCount " & _
"FROM tblHolidays " & _
"WHERE HolidayDate BETWEEN #" & dte1 & "# AND #" & dte2 & "# " &
_
"AND WeekDay(HolidayDate, 2) < 6"

Set rs = DBEngine(0)(0).OpenRecordset(sSQL, dbOpenSnapshot)
WorkingDays = iDays - rs!HolidayCount

rs.Close
Set rs = Nothing
End Function

This assumes a table with the following structure:
tblHolidays
HolidayID AUTONUMBER - Primary Key
HolidayDate DATETIME
HolidayName TEXT(30)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
Please see Paulo's last post.
Your solution is good. It is probably more efficient in execution than
mine, but for someone asking for this level of help, maybe harder to
understand. It is always interesting to see all the different ways we get to
the same solution.
 
Klatuu said:
That is even more reason to have a Holiday table. In the scenerio you are
describing, it would take quite a bit more logic like what employee, what
shift, work schedule, etc. So, nothing will work for everything, but the
routine I provided could be modified to take those circumstances into
consideration. Ok, now lets talk Vacation schedules, and Planned Plant
Shutdowns - We have 2 per year here, 4th of July week and week between
Christmas and New Years.

:
Click to expand...

Klatuu,

I was trying to answer your question..."Would you not assume that
holidays would be excluded from working days?"... I misread it.

Yes, I think payroll systems need a holiday table. I have always
found it interesting the different things companies put into
their accounting/payroll policy. I once worked for a company
that acquired another company that has 13 months in their
accounting system. They said they wanted each month to be 4 weeks.
They called the 13th month the fruit month. After that, nothing
in payroll will ever surprizes me.

I live in the Northen Ohio area,
It is 1 week before May.
By Monday we are suppose to get a foot of snow.
Very little surprizes me any more.

Have a good one,

Ron
 
Back
Top