Finding the number of working hours between two dates/times

[joelindquist]

This looks like it's going to get ugly!

I have two date/time fields [Date_Opened,Date_Closed].

I need to find the time in days, hours, minutes that it took to resolve the case (ie. Date_Closed - Date_Opened)

Here's the catch. We only work from 7-6, Mon-Fri.

I am playing with the datediff function, but have no way to exclude the hours that are not worked. Also, it has to be very accurate as people will be evaluated on this measure.

Thanks,
-Joe Lindquist

 

[evalesthy]

Could you use Michael Reds FAQ #181-261 (Access -- Other Topics -- Module Stuff -- "Calculate Working Days between two dates&quot and take that result multiplied by number of hours in working day to get your result?

 

[MichaelRed]

That would work for the whole days, but you will also need to so the time of the first day and last day sepreatly, so the DeltaDays Args need to be reviewed CAREFULLY to get the working days counted to be correct. THEN do the work time for the StartDate and EndDate and add them.

It DOES seem strange to be picky about the details (minutes and seconds) for tasks which (aparently) take several (or more) days to accomplish - and yet limit the daily hours to start work / end work, and do not subtract items like vacation, lunch, breaks ... ?????

If evaluations are based on the criteria of hours required, I would certainly want these considered.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[joelindquist]

Thanks Michael. You've saved my #$@ once again. I am working on a query to get resolution time for service tickets, therefore lunch breaks are not considered. Vacation days, however will be, and I am using your code to which I was directed by evalesthy.

Thanks again,
-Joe