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 