Time calculation in business hours

[memocn59]

Hi all, i need to do some date/time calculation, in order to do this i need to consider the next: my business days/hours are Monday-Friday from 8:30 am to 2:pm and from 4:00 pm to 7:00 pm and saturdays from 9:00 am to 2:00 pm... i need to calculate between a requirement is received and the moment when that requirement is solved but just considering my work schedules... lets say i received a call on monday at 6:pm and i solved the requirement on tuesday at 10:00pm the resulting time should be 2.5 hours

[zmbd]

memocn59:
What have you tried so far?
What does your database current look like (table structures)?
What version of Access are you using?
How experienced are you with quries, SQL, and VBA?

[memocn59]

thanks for replying, i have 3 tables, clients, services and client´s copiers, but all the time calculations is performed on the services table, first i have the open service which is set to "Now()" and then i have the end service which is manually filled... i am starting to study sql and VBA, before i tried to find a way to solve this problem only with macros and the basic functions of access but now i thinkg that is not possible... before i tried to use a DateDiff function but that returned me some values that doesn´t work for me because it consider the whole day. I am using access 2013... i have trouble trying to tell access just to consider the days and hours i need. Any advice of where to look or search would be useful, thanks

[NeoPa]

Not as simple as is often believed.

I'd follow the following process :

1. Determine the number of full weeks that start on a Monday midnight.
2. Multiply that number by how many work-hours you have in a week (47.5).
3. Determine how many full days from the start to Monday midnight.
4. Take two from that to get full weekdays, multiply that by 8.5 then add 5 for the weekend.
5. Add that value to your total so far.
6. Determine how many full days from the last Monday midnight until the end.
7. Multiply that by 8.5 but subtract 3.5 if the end is a Sunday.
8. Add that value to your total so far.
9. Now you're left with the start and end days only to calculate.
10. Calculate how many hours are involved for each (This relies on which day it is and, if it's a weekday, whether before or after the midday gap).
11. Add these results to your total so far and you have your sum total.

Hopefully you get the idea and can progress from there on your own, but let us know if you need more help for the start and end days.