A

#### Alaska1

hours to caluclate process time of work. I need to include a 5 day work

week. if a worker gets something on Friday at 4:00pm and does not complete

until Monday morning. How do I calculate that time?

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

A

hours to caluclate process time of work. I need to include a 5 day work

week. if a worker gets something on Friday at 4:00pm and does not complete

until Monday morning. How do I calculate that time?

I

iletide ÅŸunu yazdÄ± said:

hours to caluclate process time of work. I need to include a 5 day work

week. if a worker gets something on Friday at 4:00pm and does not

complete

until Monday morning. How do I calculate that time?

A

date of 4/26/2010 which is a friday to a monday. It is calculating in the 8

hours for saturday and sunday.

A

calculate the days at 8 hours but exclude weekends.

KenSheridan via AccessMonster.com said: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.

[quoted text clipped - 5 lines]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:Ken Sheridan

Stafford, England

--

Message posted via AccessMonster.com

.

J

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.

A

hour day which the datediff works but does not count it if they get it on

friday and finish on monday. It will skew their process time

I will try using this one

Try something like this:

DateDiff("h",IIf(WeekDay([DateTimeStart])=6, [DateTimeStart]+2,

[DateTimeStart]), [DateTimeEnd])

Thanks again

KenSheridan via AccessMonster.com said: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

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.

[quoted text clipped - 113 lines]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 FridayKen Sheridan

Stafford, England

--

Message posted via AccessMonster.com

.

A

Alaska1 said:

hour day which the datediff works but does not count it if they get it on

friday and finish on monday. It will skew their process time

I will try using this one

Try something like this:

DateDiff("h",IIf(WeekDay([DateTimeStart])=6, [DateTimeStart]+2,

[DateTimeStart]), [DateTimeEnd])

Thanks again

KenSheridan via AccessMonster.com said: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

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

.

A

at 8 hours if they enter something into the database on Friday and Finish on

Monday.

Expr1:

Sum(DateDiff("h",[OrderDateClerk],[CompletedandReturnedDate])-16*DateDiff("d",[OrderDateClerk],[CompletedandReturnedDate]))

J

In Weekday I am actually using the number 5 for 5 days?

No. The Weekday() function returns 1 for Sunday, 2 for Monday, ..., 5 for

Thursday, 6 for Friday and 7 for Saturday. The code is checking if the

starting point is on a Friday (6).

A

Thank you for your help.

Then in Weekday I should put 2,3,4,5, to count Monday through Friday?

Then in Weekday I should put 2,3,4,5, to count Monday through Friday?

D

No. The Weekday() function returns 1 for Sunday, 2 for Monday,

..., 5 for Thursday, 6 for Friday and 7 for Saturday. The code is

checking if the starting point is on a Friday (6).

Isn't all of that very clearly explained in the Help file? That is,

am I wrong that somebody is not doing their homework here?

A

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.

KenSheridan via AccessMonster.com said:Try this:

Sum(DateDiff("h",[OrderDateClerk],[CompletedandReturnedDate])-16*DateDiff("d",

[OrderDateClerk],[CompletedandReturnedDate])-IIf(WeekDay([OrderDateClerk]) =

6 And DateDiff("d",[OrderDateClerk],[CompletedandReturnedDate])>0,16,0))

Ken Sheridan

Stafford, England

Here is my original code which works fine but will include the weekend days

at 8 hours if they enter something into the database on Friday and Finish on

Monday.

Expr1:

Sum(DateDiff("h",[OrderDateClerk],[CompletedandReturnedDate])-16*DateDiff("d",[OrderDateClerk],[CompletedandReturnedDate]))

hours to caluclate process time of work. I need to include a 5 day work

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

.

A

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 said: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

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.

[quoted text clipped - 16 lines]Try this:week. if a worker gets something on Friday at 4:00pm and does not complete

until Monday morning. How do I calculate that time?