Using the DateDiff function based on 5 days

[Alaska1]

I am using the DateDiff function in a report. I am using it based on 16
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?

 

[ilan karaoÄŸlu]

I am using the DateDiff function in a report. I am using it based on 16
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?

 

[Alaska1]

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.

 

[Alaska1]

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


.

 

[John W. Vinson]

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.

 

[Alaska1]

Thank you for all help. It is based on time they process their work on an 8
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

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


.

 

[Alaska1]

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

Thank you for all help. It is based on time they process their work on an 8
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

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


.

 

[Alaska1]

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]))

 

[John W. Vinson]

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).

 

[Alaska1]

Thank you for your help.
Then in Weekday I should put 2,3,4,5, to count Monday through Friday?

 

[David W. Fenton]

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?

 

[Alaska1]

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:

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]))

I am using the DateDiff function in a report. I am using it based on 16
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


.

 

[Alaska1]

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:

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.

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?