Complex Calculation business hours

[typebr2024]

Dear expert,
I would greatly appreciate it if someone else could assist me with this complex calculation.

I am attempting to calculate the 8 business hours from the creation date and hour of record, taking into account the exclusion of time for the weekends, holidays, and within business hours


Example1


Start Date & Time: 01/03/2024 08:00:00 AM
Desired Due Date: 01/03/2024 16:00:00 PM

Example2
Start Date & Time: 01/03/2024 11:00:00 AM
Weekends: Saturday and Sunday
Holiday = 04/03/2024
Desired Due Date: 05/03/2024 09:00:00 AM

Working hours: 08:00 AM to 18:00 PM
Holidays: (I have a list of holidays) for the test we can consider 04/03/2024
Business Days: Monday to Friday
Weekends: Saturday and Sunday

[Minty]

Have a look at the similar threads at the bottom of the page.
This is a very frequently asked question.

There is a sample db here https://www.access-programmers.co.uk...tabase.298179/

Most of them deal with working days, but the process will be very similar.

[Bulzie]

Can do in Code I'm sure. Would need a table of Holiday dates. VBA code starts on first day, is it a Sat, Sun or Holiday, skip it, move to next day, if not count the hours, Move to next day, etc till you hit the end date. You will need to account for if say the first day starts at noon so you only add 12 hours and not 24, etc. For example 2, would the 1/3 date only allow 6 hours if the work day ends at 5pm since you started at 11am? Do you have to account for lunch break, etc.