"working days" function (part days)

G

Guest

I am using this function to count the number working days (excluding
weekends and public holidays).

The "startdate" and "enddate" are formatted as dd/mm/yyyy hh:mm:ss

How can I change the function to calculate parts of days as well as whole
days. I appreciate the answer might be obvious (even to me) but I would like
to check.


_________________________

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer

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] = #" & Format(StartDate, "mm-dd-yyyy") & "#"
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
 
J

James A. Fortune

scubadiver said:
I am using this function to count the number working days (excluding
weekends and public holidays).

The "startdate" and "enddate" are formatted as dd/mm/yyyy hh:mm:ss

How can I change the function to calculate parts of days as well as whole
days. I appreciate the answer might be obvious (even to me) but I would like
to check.


_________________________

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer

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] = #" & Format(StartDate, "mm-dd-yyyy") & "#"
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

Maybe (aircode):

Public Function WorkingDays2(ByVal Startdate As Date, _
ByVal Enddate As Date) As Integer
Dim iDate As Date

On Error GoTo Err_WorkingDays2

Dim dblCount As Double
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", _
dbOpenSnapshot)
dblCount = CDbl(0)
iDate = Startdate
Do While DateDiff("d", iDate, Enddate) >= 0
rst.FindFirst "[HolidayDate] = #" & Format(Startdate, "mm-dd-yyyy") _
& "#"
If rst.NoMatch Then
If WeekDay(iDate) <> vbSunday And WeekDay(iDate) <> vbSaturday Then
'last iteration
dblCount = dblCount + DateDiff("s", DateValue(Enddate), _
Enddate) / 3600#
Else
'not last iteration
If DateDiff("d", Startdate, iDate) = 0 Then
'first iteration
dblCount = dblCount + DateDiff("s", Startdate, _
DateValue(DateAdd("d", 1, Startdate))) / 3600#
Else
'not first iteration and not last iteration
dblCount = dblCount + 1
End If
End If
End If
iDate = DateAdd("d", 1, iDate)
Loop
WorkingDays2 = dblCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

James A. Fortune
(e-mail address removed)
 
J

James A. Fortune

scubadiver said:
I am using this function to count the number working days (excluding
weekends and public holidays).

The "startdate" and "enddate" are formatted as dd/mm/yyyy hh:mm:ss

How can I change the function to calculate parts of days as well as whole
days. I appreciate the answer might be obvious (even to me) but I would like
to check.


_________________________

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer

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] = #" & Format(StartDate, "mm-dd-yyyy") & "#"
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

I left out a piece.

Public Function WorkingDays2(ByVal Startdate As Date, _
ByVal Enddate As Date) As Integer
Dim iDate As Date

On Error GoTo Err_WorkingDays2

Dim dblCount As Double
Dim rst As DAO.Recordset
Dim db As DAO.Database

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

dblCount = CDbl(0)
iDate = Startdate
Do While DateDiff("d", iDate, Enddate) >= 0
rst.FindFirst "[HolidayDate] = #" & Format(Startdate, "mm-dd-yyyy") _
& "#"
If rst.NoMatch Then
If WeekDay(iDate) <> vbSunday And WeekDay(iDate) <> vbSaturday Then
If DateDiff("d", iDate, Enddate) = 0 Then
'last iteration
dblCount = dblCount + DateDiff("s", DateValue(Enddate), _
Enddate) / 3600#
Else
'not last iteration
If DateDiff("d", Startdate, iDate) = 0 Then
'first iteration
dblCount = dblCount + DateDiff("s", Startdate, _
DateValue(DateAdd("d", 1, Startdate))) / 3600#
Else
'not first iteration and not last iteration
dblCount = dblCount + 1
End If
End If
End If
End If
iDate = DateAdd("d", 1, iDate)
Loop
WorkingDays2 = dblCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

James A. Fortune
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top