Future Date & Time within Business (Office Hours) only

1 Store Opens at 9 O'Clock (A2) in the Morning and Closes at 7Pm (19:00) (B2) in the evening. So effectively it remains open for 600 minutes (C2)
This could vary from month to month
2 So, The difference (C2) between Opening, and Closing Time in Minutes is 600 Minutes. Hourly Difference * 60 minutes
Background
3 A4 has point (time) of Sales
4 A5 has random future date with time
5 Minutes Difference between 3 & 4, through Cell A4-A5 is in Cell B5.
6 Minutes Difference in Cell B5 has to be counted during Office Hours , that is, within time between when store remains Open & Close [A2 , B2] , ignoring full 24 hours a day, or 12 hours in a day

Query
7 How to find future Date & Time (time only within day when Store is Open) forward based on B5 value, counting either taking Cell A4 as base. (or flexibly, A5, as base)

I've made some wrong attempts, in Cell D5. Cell D2, E2, F2. may not be useful.

IS there any shortcut using inbuilt Excel function (1997-2003) version ?

Or what should be the formula in D5 ?

Sample Workbook is attached. How to take care of Holiday, in addition to Weekends (Saturday & Sunday).

Here are two pieces of code that may help.

The first is workhours: You tell it the start time, number of hours, store open time, store close time and it computes when the completion time is accounting for weekends and (optionally) holidays.

Please Login or Register  to view this content.

The second is NetWorkHours: You tell it store open time, store close time, starting time, ending time and it computes the number of hours "in store."

Please Login or Register  to view this content.

Thank you for the customised UDF. I will explore, just now i saw it, but not getting desired, results, may be something i am doing wrong, or cell also need to be formatted properly. As in current case, DAte, and Time both are commonly entered in same cell, this UDF would require splitting both Time and Date.

I am clear with the second part of the UDF which deals with the net work hours = NetWorkHours.

But again for Net work hours between 09:20am to 11:25am, the hour difference should be 2 hours 5 minutes, but it gives result of 2.08333 result.

It is a matter of formatting .0833 is 5/1440 (1440 minutes in a day) or 5 minutes.