Converting Work Days to Hours

[Yam84]

Hello,

I am trying to calculate leave taken. I track leave by having the
users input the days they are taking off. there is a reasonDtFrom and
reasonDtTo for dates. I use the dateadd function to determine how
many days that are being taken off, however I need it in hours. I
realize I could change my statement to:

HoursOut: (DateDiff("h",[ReasonDetails]![reasonDtFrom],[ReasonDetails]!
[reasonDtTo]))

However that gives me more hours than in the 8 hour work day. For
instance, one of my users is taking 7 days off. it should say 48 (8
hours per day, 6 days) hours have been used, it says 144 (24 hours per
day, 6 days). Is there anyway that I could make this calculate work
days?

 

[Ken Sheridan]

It sounds like you work a 6 day week as 7 days off = 48 hours ( 6 * 8), so
I'll assume Sunday is the non-working day. There are a number of ways you
could do this. One would be to create a 'calendar' table of all working days
and return the Count of rows from this table where the date is between the
start and end dates. If the working day is fixed at 8 hours for all employees
then:

HoursOutCount("*", "WorkDaysCalendar", "WorkDate Between #" &
Format([reasonDtFrom],"yyyy-mm-dd") & "# And #" &
Format([reasonDtTo],"yyyy-mm-dd") & "#") * 8

The other way would be to write a VBA function which steps through the days
in the range and increments the return value by 8 for each working day:

Public Function LeaveHours(dtmFrom As Date, _
dtmTo As Date, _
sngDailyHours As Single) As Single

Dim dtmDate As Date
Dim sngHours As Single

For dtmDate = dtmFrom To dtmTo
If Weekday(dtmDate, vbSunday) > 1 Then
sngHours = sngHours + sngDailyHours
End If
Next dtmDate

LeaveHours = sngHours

End Function

and call it like so:

HoursOut:LeaveHours([reasonDtFrom],[reasonDtTo],8)

If the working varies for each employee the you'd need to store each
employee's working day hours in an Employees table, include this table in the
query, and use the value of the EmployeeWorkingDay field (or whatever you
call it) rather than the constant 8, either in the DCount expression if you
create a WorkDays Calendar table, or as the LeaveHours function's third
argument.

Note that if the working day is not an integral number of hours then you
need to specify it as a decimal value, so 7 hours 30 minutes would be
specified as 7.5. The total hours would similarly be returned as a decimal
value not hours and minutes, though it would not be difficult to convert the
return value so its expressed as hours and minutes:

Int([HoursOut]) & ":" Format(([HoursOut] - Int([HoursOut]))*60,"00")

Ken Sheridan
Stafford, England

 

[Danny J. Lesandrini]

Well, this is no simple task. Does the employee always work 6 days a week?
Do they get Saturday or Sunday off, and does it ever rotate? They probably
get Christmas off, but how about the day before? President's Day?

So you are going to need, at a minimum, a table of holiday dates. If you can
assume that a person always works 5 days a week and that Sat & Sun are non-
work days, that simplifies things, but if the schedule rotates in any way, then
you will need a bigger, better table of WorkDays for each employee and
somebody will need to keep it up-to-date.

Once you have a list of dates to work from then there's another assumption
to decide upon: How many hours per day does an employee work? Is it always
assumed to be 8 hours? If so, we're golden, but if not, then that bigger, better
table just got enormous.

I guess the point is that there's no simple function like WorkDateDiff(), but if
you can make some assumptions, then it might be possible to write something simple.

 

[Danny J. Lesandrini]

Below is some code I have lying around (had to find it), that figures out
if a date is a holiday (US only). You could add a check to this function
that considers a Saturday and/or Sunday as a "holiday" and then this
function could be used to count WorkDays.

--
Danny J. Lesandrini
(e-mail address removed)


Option Compare Database
Option Explicit

Public Function IsKnownHoliday(ByVal DateVal As Date) As Boolean
On Error Resume Next

Dim intDay As Integer
Dim intMonth As Integer

intDay = Day(DateVal)
intMonth = Month(DateVal)

IsKnownHoliday = False
Select Case intMonth
Case 1 'New Years Day
If intDay = 1 Then IsKnownHoliday = True

Case 5 'Memorial Day
IsKnownHoliday = (USMemorialDay(DateVal) = DateVal)

Case 7 'July 4th
If intDay = 4 Then IsKnownHoliday = True

Case 9 'Labor Day
IsKnownHoliday = (USLaborDay(DateVal) = DateVal)

Case 11 'Thanksgiving ... and day after Thanksgiving
If (USThanksgivingDay(DateVal) = DateVal) Then
IsKnownHoliday = True
ElseIf (USThanksgivingDay(DateVal) + 1 = DateVal) Then
IsKnownHoliday = True
End If

Case 12 'Christmas
If intDay = 25 Then IsKnownHoliday = True

End Select

End Function

Public Function USThanksgivingDay(WorkDay As Date)
' Return the date of Thanksgiving
' Thanksgiving Day, the fourth Thursday in November.

Dim Y As Integer, tmpDay As Date

Y = Year(WorkDay)
' Set tmpDay to latest possible date that holiday can occur:
' November 30
tmpDay = DateSerial(Y, 11, 30)
' Subtract a number from 0 to 6 to get the right day of the week.
' The "+ 2" term changes depending on the day of the week desired
' in the result:
' Sunday: +6; Monday: +5; ... ; Saturday: +0
tmpDay = tmpDay - ((tmpDay + 2) Mod 7)
USThanksgivingDay = tmpDay

End Function

Public Function USMemorialDay(WorkDay As Date)
' Return the date of Memorial Day
' Memorial Day, the last Monday in May.

Dim Y As Integer, tmpDay As Date

Y = Year(WorkDay)
' Set tmpDay to latest possible date that holiday can occur:
' May 31
tmpDay = DateSerial(Y, 5, 31)
' Subtract a number from 0 to 6 to get the right day of the week.
' The "+ 2" term changes depending on the day of the week desired
' in the result:
' Sunday: +6; Monday: +5; ... ; Saturday: +0
tmpDay = tmpDay - ((tmpDay + 5) Mod 7)
USMemorialDay = tmpDay

End Function

Public Function USLaborDay(WorkDay As Date)
' Return the date of Labor Day
' Labor Day, the first Monday in September.

Dim Y As Integer, tmpDay As Date

Y = Year(WorkDay)
' Set tmpDay to latest possible date that holiday can occur:
' September 7
tmpDay = DateSerial(Y, 9, 7)
' Subtract a number from 0 to 6 to get the right day of the week.
' The "+ 2" term changes depending on the day of the week desired
' in the result:
' Sunday: +6; Monday: +5; ... ; Saturday: +0
tmpDay = tmpDay - ((tmpDay + 5) Mod 7)
USLaborDay = tmpDay

End Function


www.amazecreations.com

 

[wisepin]

See Pwise post @

http://www.tek-tips.com/viewthread.cfm?qid=1538411

 

[Ken Sheridan]

Further to Danny's point re holidays an easy way to handle these is by using
a calendar table. As well as the WorkDate column you can include a Boolean
(Yes/No) IsHoliday column with a value of True for each date which is a
public or concessionary holiday. You can then count the Hours with:

HoursOutCount("*", "WorkDaysCalendar", "WorkDate Between #" &
Format([reasonDtFrom],"yyyy-mm-dd") & "# And #" &
Format([reasonDtTo],"yyyy-mm-dd") & "# And Not IsHoliday") * 8

You can create a calendar table very easily by serially filling down a
column with dates in Excel and then importing this into an Access table.
This will include all dates including Saturdays and Sundays of course, so to
mark Sundays as a holiday you can use a simple update query:

UPDATE WorkDaysCalendar
SET IsHoliday = TRUE
WHERE WEEKDAY(WorkDate,1) = 1;

You'll have to set IsHoliday to True manually for the public and
concessionary holidays as and when these dates are known.

Ken Sheridan
Stafford, England