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 29 Apr 2010 07:08
Only if using the MinutesWorked function would you use a value list, but 2,3,
4,5 would be Monday to Thursday. You need 2,3,4,5,6. If we assume a nine-to-
five working day for instance the expression would be something like this:

MinutesWorked[DateTimeStart],[DateTimeEnd],#09:00#,#17:00#,2,3,4,5,6)

Or to get the value in hours as a decimal number:

MinutesWorked[DateTimeStart],[DateTimeEnd],#09:00#,#17:00#,2,3,4,5,6)/60

Or to get the value as hours:minutes:

MinutesWorked[DateTimeStart],[DateTimeEnd],#09:00#,#17:00#,2,3,4,5,6)\60 & ":
" & Format(MinutesWorked[DateTimeStart],[DateTimeEnd],#09:00#,#17:00#,2,3,4,5,
6) Mod 60,"00")

In each case the expression is a single line. Here it will be split over
several by your newsreader.

where DateTimeStart and DateTimeEnd are the fields of date/time data in which
the start and end times of the task are stored.

Ken Sheridan
Stafford, England

Alaska1 wrote:
>Thank you for your help.
>Then in Weekday I should put 2,3,4,5, to count Monday through Friday?
>
>> >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).

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

From: KenSheridan via AccessMonster.com on 29 Apr 2010 07:11
Correction: I missed the opening parenthesis in each case. Each expression
should start:

MinutesWorked([DateTimeStart]

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 29 Apr 2010 07:30
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

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

From: David W. Fenton on 29 Apr 2010 19:27
John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in
news:aa7it5totkpfsdvstslj39fi2g771o2cu4(a)4ax.com:

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

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?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Alaska1 on 10 May 2010 17:03
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.



"KenSheridan via AccessMonster.com" wrote:

> 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
>
> Alaska1 wrote:
> >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
> 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