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

"KenSheridan via AccessMonster.com" wrote:

> 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: Alaska1 on 28 Apr 2010 22:41
In Weekday I am actually using the number 5 for 5 days?

"Alaska1" wrote:

> 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
>
> "KenSheridan via AccessMonster.com" wrote:
>
> > 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: Alaska1 on 28 Apr 2010 22:52

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]))
"Alaska1" wrote:

> 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?
From: John W. Vinson on 29 Apr 2010 01:47
On Wed, 28 Apr 2010 19:41:01 -0700, Alaska1
<Alaska1(a)discussions.microsoft.com> wrote:

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

John W. Vinson [MVP]
From: Alaska1 on 29 Apr 2010 06:25
Thank you for your help.
Then in Weekday I should put 2,3,4,5, to count Monday through Friday?

"John W. Vinson" wrote:

> On Wed, 28 Apr 2010 19:41:01 -0700, Alaska1
> <Alaska1(a)discussions.microsoft.com> wrote:
>
> >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).
> --
>
> John W. Vinson [MVP]
> .
>
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