I assume the '8 hours' means you have an 8 hour working day. The expression
I gave you will compute the difference in hours form the time on the Friday
to that on the Monday, excluding the Saturday and Sunday. Enter this in the
debug window (as one line), with the date literals substituted for the
variables:
? DateDiff("h",IIf(WeekDay(#2010-04-23 22:00#)=6,#2010-04-23 22:00#+2, #2010-
04-23 22:00#), IIf(WeekDay(#2010-04-23 22:00#)=6 And WeekDay(#2010-04-26 06:
00#)=6, #2010-04-26 06:00#+2, #2010-04-26 06:00#))
This will return 8, i.e. the two hours from 10.00 PM on the Friday to
Midnight, plus the six hours on the Monday from Midnight to 06.00 AM. This
takes no account of the length of the working day, however, so how are you
allowing for this when calling the DateDiff function?
One way you could handle this and at the same time give greater flexibility
would be to create a function which accepts the start and end work date/time
values, the start and end times of the working day and the days of the
working week:
Public Function MinutesWorked(StartTime As Date, _
EndTime As Date, _
DayStarts As Date, _
DayEnds As Date, _
ParamArray WorkDays() As Variant) As Long
Dim varDay As Variant
Dim dtmDay As Date
Dim intDayCount As Integer
Dim lngMinutes As Long
' get number of workdays
For dtmDay = DateValue(StartTime) To DateValue(EndTime)
For Each varDay In WorkDays
If Weekday(dtmDay, vbSunday) = varDay Then
intDayCount = intDayCount + 1
Exit For
End If
Next varDay
Next dtmDay
' get total minutes for all workdays
lngMinutes = DateDiff("n", DayStarts, DayEnds) * intDayCount
' subtract unworked time on first day
lngMinutes = lngMinutes - DateDiff("n", DayStarts, TimeValue(StartTime))
' subtract unworked time on last day
lngMinutes = lngMinutes - DateDiff("n", TimeValue(EndTime), DayEnds)
MinutesWorked = lngMinutes
End Function
The working days are entered as values from 1 to 7 (Sunday to Saturday), so
for a 5-day working week, Monday to Friday, you'd call the function as below
in the debug window (using literal dates here for the start and end date/time
values in this example, though in reality you'd use the field names when
calling the function in a query or report). A working day from 9:00 AM to 5:
00 PM is assumed in the examples. So for a start on Friday ending on Monday:
? MinutesWorked(#2010-04-23 16:00#,#2010-04-26 12:00#,#09:00#,#17:00#,2,3,4,5,
6)
which returns 240
For a start on Thursday ending on Friday:
? MinutesWorked(#2010-04-22 15:00#,#2010-04-23 10:30#,#09:00#,#17:00#,2,3,4,5,
6)
which returns 210
For a start on Tuesday ending on Wednesday:
? MinutesWorked(#2010-04-27 15:40#,#2010-04-28 11:15#,#09:00#,#17:00#,2,3,4,5,
6)
which returns 215
The function returns the value in minutes. This can be converted to Hours:
minutes by using integer division to get the hours and the Mod operator to
get the remaining minutes, like so:
? MinutesWorked(#2010-04-27 15:40#,#2010-04-28 11:15#,#09:00#,#17:00#,2,3,4,5,
6)\60 & ":" & MinutesWorked(#2010-04-27 15:40#,#2010-04-28 11:15#,#09:00#,#17:
00#,2,3,4,5,6) Mod 60
which returns 3:35
BTW the function would allow for time worked beyond the normal working day,
but only on the first or last days. Say the end time on the final day is 6:
15 PM for instance:
? MinutesWorked(#2010-04-27 15:40#,#2010-04-28 18:15#,#09:00#,#17:00#,2,3,4,5,
6)
which returns 635
Start or end times outside the normal working day in days apart from the
first and last would not be accounted for as these do not appear in the
values passed into the function at all. These days would be treated as a
normal 8 hour (or whatever is specified) day. Also not covered are lunch
breaks within a working day; the complete time from start to finish is
assumed as work time.
Ken Sheridan
Stafford, England
Thank you. I tried both formulas and used a start date of 4/23/2010 and end
date of 4/26/2010 which is a friday to a monday. It is calculating in the 8
hours for saturday and sunday.
PS: That assumes anything started on a Friday is not completed until Monday
of course. If a task could start and finish on the Friday try this:
[quoted text clipped - 5 lines]
Ken Sheridan
Stafford, England
--
Message posted via AccessMonster.com
.