Prev: how do I strip spaces and replace with dashes
Next: Setting up an Email to automatically be sent based off a date
From: KenSheridan via AccessMonster.com on 28 Apr 2010 12:12
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

Alaska1 wrote:
>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
http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1

From: Alaska1 on 28 Apr 2010 13:59
I need an 8 hour day working 5 days a week. I need a formula that will
calculate the days at 8 hours but exclude weekends.

"KenSheridan via AccessMonster.com" wrote:

> 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
>
> Alaska1 wrote:
> >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
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1
>
> .
>
From: John W. Vinson on 28 Apr 2010 15:58
On Wed, 28 Apr 2010 10:59:01 -0700, Alaska1
<Alaska1(a)discussions.microsoft.com> wrote:

>I need an 8 hour day working 5 days a week. I need a formula that will
>calculate the days at 8 hours but exclude weekends.
>

http://www.mvps.org/access/datetime/date0006.htm

has two. It's not trivial - you'll also want to exclude non-weekend work
holidays.
--

John W. Vinson [MVP]
From: KenSheridan via AccessMonster.com on 28 Apr 2010 16:21
Which is exactly what the function I posted will handle. You pass the start
and end date/time values into it, along with the start and end times of the
standard working day and the days of the working week, 2,3,4,5,6 in this case.


It returns the result in minutes as its usual with these sort of computations
to work at the smallest significant unit. To convert this to hours as a
decimal number just divide the result by 60; to convert it to hours and
minutes use integer division and the Mod operator as I described.

It might be possible to cobble together an expression (not formula BTW) to do
it, but it would probably be very convoluted. The function on the other hand
is relatively simple. All you have to do is paste it into a standard module,
be sure to save the module under a different name from that of the function,
e.g. basDateStuff, and call it in a report or its underlying query just as
you'd call a built in function.

Ken Sheridan
Stafford, England

Alaska1 wrote:
>I need an 8 hour day working 5 days a week. I need a formula that will
>calculate the days at 8 hours but exclude weekends.
>
>> 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
>[quoted text clipped - 113 lines]
>> >> Ken Sheridan
>> >> Stafford, England

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1

From: KenSheridan via AccessMonster.com on 28 Apr 2010 16:29
Judging by their first post I think they want the time difference, not number
of days. If not perhaps they'll clarify.

Ken Sheridan
Stafford, England

John W. Vinson wrote:
>>I need an 8 hour day working 5 days a week. I need a formula that will
>>calculate the days at 8 hours but exclude weekends.
>
>http://www.mvps.org/access/datetime/date0006.htm
>
>has two. It's not trivial - you'll also want to exclude non-weekend work
>holidays.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: how do I strip spaces and replace with dashes
Next: Setting up an Email to automatically be sent based off a date