Business Days (Negative or Positive)

[cinosinu]

Hi, I found this code online and it works well for calculating how many business days are between two dates. It also excludes Holidays in a table. My kudos to the author, Arvin Meyer.

What I am working on can have an End date that is before the start date, and I do want negative calculations. Right now this function returns a zero for anything that would be negative.

Can anyone help me modify this great function to calculate for positive or negative time spans?



...

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

 

[scalextric59]

I would use the Datediff function. However, if you have the case of an End date being before the Start date, then reverse the order (to get a positive number), count, and then multiply by -1

 

[HiTechCoach]

See if this will work for you:

fNetWorkdays and fAddWorkDays

Another advantage to this function over others is the ability to handle a Start date that is greater than an End date.

 

[cinosinu]

Thanks guys

 

[MarkK]

Alternatively you can wrap the existing function in the following logic...

[FONT="Verdana"][SIZE="1"]function wd3(date1 as date, date2 as date) as integer
  if date1 < date2 then
[COLOR="Green"]    'execute the existing function as is[/COLOR]
    wd3 = workingdays2(date1, date2)
  else
[COLOR="Green"]    'reverse dates and negate result[/COLOR]
    wd3 = -workingdays2(date2, date1)
  end if
end function[/SIZE][/FONT]

 

[dark11984]

I am a bit confused... I have copied the workdays2 code into a module and changed to suit my date fields but how do i use that module on my unbound text box?

 

[HiTechCoach]

I am a bit confused... I have copied the workdays2 code into a module and changed to suit my date fields but how do i use that module on my unbound text box?

I have posted a working example here:

Omitting Public Holidays from CalcWorkDays

 

[dark11984]

Thanks HiTech Coach!

 

[dark11984]

I got it to work but now i'm trying to do something else with it on another form but not having much luck.

I have an unbound text box on an unbound form where I want to display number of business days from start of financial year to today. I tried =fNetWorkdays(1/7/2008,[today]) but I get an error.

 

[raskew]

Hi -

Give this a shot:

Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
'************************************************
'Purpose:   Compute number of workdays (Mon - Fri)
'           between pStart and pEnd
're:        http://www.access-programmers.co.uk/forums/showthread.php?t=164254&highlight=simplified
'Coded by:  raskew
'Input:    ? fGetWorkdays2(date(), #4/1/09#)
'Output:    51
'************************************************

   fGetWorkdays2 = 7 - WeekDay(pstart) + 5 * (DateDiff("ww", pstart, pend) - 1) + WeekDay(pend) - 1

End Function

HTH - Bob

 

[tucker61]

Hi -

Give this a shot:

Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
'************************************************
'Purpose:   Compute number of workdays (Mon - Fri)
'           between pStart and pEnd
're:        http://www.access-programmers.co.uk/forums/showthread.php?t=164254&highlight=simplified
'Coded by:  raskew
'Input:    ? fGetWorkdays2(date(), #4/1/09#)
'Output:    51
'************************************************
 
   fGetWorkdays2 = 7 - WeekDay(pstart) + 5 * (DateDiff("ww", pstart, pend) - 1) + WeekDay(pend) - 1
 
End Function

HTH - Bob

I have just started using the code above, but if one of my date fields is blank it gives a error. what is the best way to handle this error.

My date fields are datestart, for when a job starts, and datecomp. some instancies the job is not complete, hence no date, and the error.

Please help

Regards
T61

 

[vbaInet]

Check for when the control's is null? The function is IsNull()