1. Registered User
Join Date
Aug 2009
Posts
61

Hi There,

I also have another thread going to discuss this issue. However, I have found a better way to solve my problem of the number of workdays between two dates. I have the following code, which I am using to calculate how many days an employee has requested for vacation. I can then see how many are remaining, etc.

My problem is that if the date is on the same day because the employee only wants half a day, the end result is zero days taken, but it should be .5. The best way around this, in my opinion, is to do the calculations using hours instead of days.

Can you somebody help me to modify the code to pull hours instead of days. At the moment I have the dates and time separated into two fields to make it easy for the employee.

Public Function Workdays(ByRef startDate As Date, _
ByRef endDate As Date, _
Optional ByRef strHolidays As String = "Holidays" _
) As Integer
' Returns the number of workdays between startDate
' and endDate inclusive. Workdays excludes weekends and
' holidays. Optionally, pass this function the name of a table
' or query as the third argument. If you don't the default
' is "Holidays".
On Error GoTo Workdays_Error
Dim nWeekdays As Integer
Dim nHolidays As Integer
Dim strWhere As String

' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)

nWeekdays = Weekdays(startDate, endDate)
If nWeekdays = -1 Then
Workdays = -1
GoTo Workdays_Exit
End If

strWhere = "[Holiday] >= #" & startDate _
& "# AND [Holiday] <= #" & endDate & "#"

' Count the number of holidays.
nHolidays = DCount(Expr:="[Holiday]", _
Domain:=strHolidays, _
Criteria:=strWhere)

Workdays = nWeekdays - nHolidays

Workdays_Exit:
Exit Function

Workdays_Error:
Workdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Workdays"
Resume Workdays_Exit

End Function

2. Registered User
Join Date
Oct 2003
Location
Posts
574
I would suggest a simple change to your If statement:

If nWeekdays = -1 Then
Workdays = -1
elseif nweekdays = 0 then
workdays = 0.5
End If

3. Registered User
Join Date
Aug 2009
Posts
61
Originally Posted by jmrSudbury
I would suggest a simple change to your If statement:

If nWeekdays = -1 Then
Workdays = -1
elseif nweekdays = 0 then
workdays = 0.5
End If
This is still returning 1 for the day, even though they have booked only .5, or 4 hours. The calculation is not looking at any hours, only days. I have two fields, [VacRequestSrtTime] for the start time and [VacRequestFinTime] for the end time. How can I integrate these in so that it counts the hours as well instead of just the days?

4. Registered User
Join Date
Jun 2011
Location
Posts
297
How does the user input a half day?

i.e. How does your function accept (13/08/2011, 13/08/2011) and know if the user is taking a full day or a half day?

5. Registered User
Join Date
Aug 2009
Posts
61
Originally Posted by kez1304
How does the user input a half day?

i.e. How does your function accept (13/08/2011, 13/08/2011) and know if the user is taking a full day or a half day?
I also have fields for start time and end time. They request that they want to leave at 11:30 am, which means that they require a half day vacation.

i.e. 13/08/2011 7:30:00 until 13/8/2011 11:30:00 am

6. Registered User
Join Date
Jun 2011
Location
Posts
297
You'd need to check those fields to see if they're <= 4 hours apart, if so, and the startDate and the endDate are equal, .5 should be returned.

If they're > 4 hours apart, then a full day will be required.

Personally, I'd incorporate that into the same function, but it's up to you really.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•