From: KenSheridan via AccessMonster.com on 11 May 2010 06:29 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 Alaska1 wrote:>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? -- Message posted via http://www.accessmonster.com From: Alaska1 on 17 May 2010 15:23 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? "KenSheridan via AccessMonster.com" wrote: > 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 > > Alaska1 wrote: > >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? > > -- > Message posted via http://www.accessmonster.com > > . > From: KenSheridan via AccessMonster.com on 17 May 2010 18:46 The start and end times of the working day are not relevant to the expression; it is operating on the basis of an 8 hour day regardless of when those 8 hours are worked within the day. I really think you'd be better off using a function which allows for more parameters to be employed. The function I posted earlier in the thread should give you an accurate result on the basis not only of the start and end times, but also the days of the working week and the start and end of the working day, e.g. MinutesWorked(#2010-05-14 15:30#,#2010-05-17 10:00#,#08:00#,#17:00#,2,3,4,5,6) returns 210, which is correct in this case as the start time is in the afternoon and the end time is in the morning, and there are no intervening working days between them. However, if the former were in the morning and/or the latter in the afternoon or there were intervening working days then the result would probably be erroneous as it takes no account of lunch breaks. I did draw attention to this in one of my earlier posts. You can, however, cater for this by also passing the start and end times of the lunch break into the function: Public Function MinutesWorked(StartTime As Date, _ EndTime As Date, _ DayStarts As Date, _ DayEnds As Date, _ LunchStarts As Date, _ LunchEnds 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) - DateDiff("n", LunchStarts, LunchEnds)) * intDayCount ' subtract unworked time on first day lngMinutes = lngMinutes - DateDiff("n", DayStarts, TimeValue(StartTime)) ' ignore lunch break if work started after lunch on first day If TimeValue(StartTime) >= LunchStarts Then lngMinutes = lngMinutes + DateDiff("n", LunchStarts, LunchEnds) End If ' subtract unworked time on last day lngMinutes = lngMinutes - DateDiff("n", TimeValue(EndTime), DayEnds) ' ignore lunch break if work ended before lunch on last day If TimeValue(EndTime) <= LunchEnds Then lngMinutes = lngMinutes + DateDiff("n", LunchStarts, LunchEnds) End If MinutesWorked = lngMinutes End Function You can see how it works by entering some values either side of a 12:00 – 13: 00 lunch break in the debug (aka immediate) window like so: ? MinutesWorked(#2010-05-14 15:30#,#2010-05-17 12:00#,#08:00#,#17:00#,#12:00#, #13:00#, 2,3,4,5,6) 330 ? MinutesWorked(#2010-05-14 15:30#,#2010-05-17 13:30#,#08:00#,#17:00#,#12:00#, #13:00#, 2,3,4,5,6) 360 ? MinutesWorked(#2010-05-14 13:00#,#2010-05-17 13:30#,#08:00#,#17:00#,#12:00#, #13:00#, 2,3,4,5,6) 510 ? MinutesWorked(#2010-05-14 11:30#,#2010-05-17 13:30#,#08:00#,#17:00#,#12:00#, #13:00#, 2,3,4,5,6) 540 Ken Sheridan Stafford, England Alaska1 wrote:>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: >[quoted text clipped - 34 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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201005/1