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 11 May 2010 06:29
You'd have to count the minutes (using "n" as the interval character, as "m"
is for months) and divide by 60:

Sum(DateDiff("n",[OrderDateClerk],[CompletedandReturnedDate]) -16*DateDiff
("d",[OrderDateClerk],[CompletedandReturnedDate]) -IIf(WeekDay(
[OrderDateClerk]) = 6 And DateDiff("d", [OrderDateClerk],
[CompletedandReturnedDate]) > 0,16,0))/60

Which would give the result in hours as a decimal number. To give the result
in hours:minutes format you'd use integer division to return the hours and
the Mod operator to return the minutes:

Sum(DateDiff("n",[OrderDateClerk],[CompletedandReturnedDate]) -16*DateDiff
("d",[OrderDateClerk],[CompletedandReturnedDate]) -IIf(WeekDay(
[OrderDateClerk]) = 6 And DateDiff("d", [OrderDateClerk],
[CompletedandReturnedDate]) > 0,16,0))\60 & ":" & Format(Sum(DateDiff("n",
[OrderDateClerk],[CompletedandReturnedDate]) -16*DateDiff("d",[OrderDateClerk]
,[CompletedandReturnedDate]) -IIf(WeekDay([OrderDateClerk]) = 6 And DateDiff
("d", [OrderDateClerk],[CompletedandReturnedDate]) > 0,16,0)) Mod 60,"00")

Note that the latter would return a string, so you can't do arithmetic on the
returned value. If you do need to do arithmetic it should be done on the
value returned as a decimal number. The final result can then be formatted
in hours:minutes.

Ken Sheridan
Stafford, England

Alaska1 wrote:
>Thank you. It is calculating the work days.
>
>I noticed if the work time is only a half hour it counts it as zero. How do
>I adjust for the half hour? It seems to be counting on hours.
>
>> Try this:
>>
>[quoted text clipped - 16 lines]
>> >> week. if a worker gets something on Friday at 4:00pm and does not complete
>> >> until Monday morning. How do I calculate that time?

--
Message posted via http://www.accessmonster.com

From: Alaska1 on 17 May 2010 15:23
Thank you,

It looks like the formula I am using is calculating based on 9 to 5. Should
it be doing that. If work starts at 8:00am. If you use the datdiff it
should just calculate 8 I need to use the formula for minutes?

"KenSheridan via AccessMonster.com" wrote:

> You'd have to count the minutes (using "n" as the interval character, as "m"
> is for months) and divide by 60:
>
> Sum(DateDiff("n",[OrderDateClerk],[CompletedandReturnedDate]) -16*DateDiff
> ("d",[OrderDateClerk],[CompletedandReturnedDate]) -IIf(WeekDay(
> [OrderDateClerk]) = 6 And DateDiff("d", [OrderDateClerk],
> [CompletedandReturnedDate]) > 0,16,0))/60
>
> Which would give the result in hours as a decimal number. To give the result
> in hours:minutes format you'd use integer division to return the hours and
> the Mod operator to return the minutes:
>
> Sum(DateDiff("n",[OrderDateClerk],[CompletedandReturnedDate]) -16*DateDiff
> ("d",[OrderDateClerk],[CompletedandReturnedDate]) -IIf(WeekDay(
> [OrderDateClerk]) = 6 And DateDiff("d", [OrderDateClerk],
> [CompletedandReturnedDate]) > 0,16,0))\60 & ":" & Format(Sum(DateDiff("n",
> [OrderDateClerk],[CompletedandReturnedDate]) -16*DateDiff("d",[OrderDateClerk]
> ,[CompletedandReturnedDate]) -IIf(WeekDay([OrderDateClerk]) = 6 And DateDiff
> ("d", [OrderDateClerk],[CompletedandReturnedDate]) > 0,16,0)) Mod 60,"00")
>
> Note that the latter would return a string, so you can't do arithmetic on the
> returned value. If you do need to do arithmetic it should be done on the
> value returned as a decimal number. The final result can then be formatted
> in hours:minutes.
>
> Ken Sheridan
> Stafford, England
>
> Alaska1 wrote:
> >Thank you. It is calculating the work days.
> >
> >I noticed if the work time is only a half hour it counts it as zero. How do
> >I adjust for the half hour? It seems to be counting on hours.
> >
> >> Try this:
> >>
> >[quoted text clipped - 16 lines]
> >> >> week. if a worker gets something on Friday at 4:00pm and does not complete
> >> >> until Monday morning. How do I calculate that time?
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>
From: KenSheridan via AccessMonster.com on 17 May 2010 18:46
The start and end times of the working day are not relevant to the
expression; it is operating on the basis of an 8 hour day regardless of when
those 8 hours are worked within the day. I really think you'd be better off
using a function which allows for more parameters to be employed. The
function I posted earlier in the thread should give you an accurate result on
the basis not only of the start and end times, but also the days of the
working week and the start and end of the working day, e.g.

MinutesWorked(#2010-05-14 15:30#,#2010-05-17 10:00#,#08:00#,#17:00#,2,3,4,5,6)


returns 210, which is correct in this case as the start time is in the
afternoon and the end time is in the morning, and there are no intervening
working days between them. However, if the former were in the morning and/or
the latter in the afternoon or there were intervening working days then the
result would probably be erroneous as it takes no account of lunch breaks. I
did draw attention to this in one of my earlier posts. You can, however,
cater for this by also passing the start and end times of the lunch break
into the function:

Public Function MinutesWorked(StartTime As Date, _
EndTime As Date, _
DayStarts As Date, _
DayEnds As Date, _
LunchStarts As Date, _
LunchEnds 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) - DateDiff("n",
LunchStarts, LunchEnds)) * intDayCount

' subtract unworked time on first day
lngMinutes = lngMinutes - DateDiff("n", DayStarts, TimeValue(StartTime))
' ignore lunch break if work started after lunch on first day
If TimeValue(StartTime) >= LunchStarts Then
lngMinutes = lngMinutes + DateDiff("n", LunchStarts, LunchEnds)
End If


' subtract unworked time on last day
lngMinutes = lngMinutes - DateDiff("n", TimeValue(EndTime), DayEnds)
' ignore lunch break if work ended before lunch on last day
If TimeValue(EndTime) <= LunchEnds Then
lngMinutes = lngMinutes + DateDiff("n", LunchStarts, LunchEnds)
End If

MinutesWorked = lngMinutes

End Function

You can see how it works by entering some values either side of a 12:00 – 13:
00 lunch break in the debug (aka immediate) window like so:

? MinutesWorked(#2010-05-14 15:30#,#2010-05-17 12:00#,#08:00#,#17:00#,#12:00#,
#13:00#, 2,3,4,5,6)
330
? MinutesWorked(#2010-05-14 15:30#,#2010-05-17 13:30#,#08:00#,#17:00#,#12:00#,
#13:00#, 2,3,4,5,6)
360
? MinutesWorked(#2010-05-14 13:00#,#2010-05-17 13:30#,#08:00#,#17:00#,#12:00#,
#13:00#, 2,3,4,5,6)
510
? MinutesWorked(#2010-05-14 11:30#,#2010-05-17 13:30#,#08:00#,#17:00#,#12:00#,
#13:00#, 2,3,4,5,6)
540

Ken Sheridan
Stafford, England

Alaska1 wrote:
>Thank you,
>
>It looks like the formula I am using is calculating based on 9 to 5. Should
>it be doing that. If work starts at 8:00am. If you use the datdiff it
>should just calculate 8 I need to use the formula for minutes?
>
>> You'd have to count the minutes (using "n" as the interval character, as "m"
>> is for months) and divide by 60:
>[quoted text clipped - 34 lines]
>> >> >> week. if a worker gets something on Friday at 4:00pm and does not complete
>> >> >> until Monday morning. How do I calculate that time?

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

First  |  Prev  | 
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