Spreadsheet: 831816d1685933419-calculating-no-of-business-hours-for-enquiries-received-business-hours-calc-close
Sheet: Enquiry time to close

Ref Number | Enquiry# | Date Opened | Date Closed | Expected results | Previous Formula | Rounded Date Op* | Rounded Date Cl.* | 09:00:00 | 17:00:00
1 | Enquiry  1 | 2023-05-21 15:41:00 | 2023-05-23 16:55:00 | 1.99 |  | 2023-05-22 09:00:00 | 2023-05-23 16:55:00
2 | [NAME] | 2023-05-23 10:44:00 | 2023-05-23 14:24:00 | 0.46 |  | 2023-05-23 10:44:00 | 2023-05-23 14:24:00 |  | *I've included New Date Op and New Date Cl to hopefully help make the outside of business hours rounding easier to understand.
3 | Enquiry  3 | 2023-05-17 13:53:00 | 2023-05-23 15:57:00 | 4.26 |  | 2023-05-17 13:53:00 | 2023-05-23 15:57:00
4 | Enquiry  4 | 2023-05-19 12:38:00 | 2023-05-23 09:57:00 | 1.66 |  | 2023-05-19 12:38:00 | 2023-05-23 09:57:00
5 | Enquiry  5 | 2023-05-18 20:58:00 | 2023-05-23 16:36:00 | 2.95 |  | 2023-05-19 09:00:00 | 2023-05-23 16:36:00
6 | Enquiry  6 | 2023-05-22 21:37:00 | 2023-05-23 14:52:00 | 0.73 |  | 2023-05-23 09:00:00 | 2023-05-23 14:52:00 |  | 8 business hours = 1 business day
7 | Enquiry  7 | 2023-05-23 13:16:00 | 2023-05-23 14:10:00 | 0.11 |  | 2023-05-23 13:16:00 | 2023-05-23 14:10:00
8 | Enquiry  8 | 2023-05-21 09:17:00 | 2023-05-23 15:34:00 | 1.82 |  | 2023-05-22 09:00:00 | 2023-05-23 15:34:00
9 | Enquiry  9 | 2023-05-22 21:29:00 | 2023-05-23 15:21:00 | 0.79 |  | 2023-05-23 09:00:00 | 2023-05-23 15:21:00
10 | Enquiry  10 | 2023-05-20 15:42:00 | 2023-05-23 12:37:00 | 1.45 |  | 2023-05-22 09:00:00 | 2023-05-23 12:37:00
11 | Enquiry  11 | 2023-05-21 19:25:00 | 2023-05-23 11:06:00 | 1.26 |  | 2023-05-22 09:00:00 | 2023-05-23 11:06:00
12 | Enquiry  12 | 2023-05-20 14:12:00 | 2023-05-23 19:12:00 | 2 |  | 2023-05-22 09:00:00 | 2023-05-23 17:00:00
13 | Enquiry  13 | 2023-05-23 08:01:00 | 2023-05-23 08:02:00 | 0 |  | 2023-05-23 09:00:00 | 2023-05-22 17:00:00
14 | Enquiry  14 | 2023-05-27 10:00:00 | 2023-05-27 11:45:00 | 0 |  | 2023-05-29 09:00:00 | 2023-05-26 17:00:00
15 | Enquiry  15 | 2023-05-28 09:59:59.995000 | 2023-06-02 17:00:00 | 5 |  | 2023-05-29 09:00:00 | 2023-06-02 17:00:00
16 | Enquiry  16 | 2023-05-28 09:59:59.995000 | 2023-06-03 00:01:00 | 5 |  | 2023-05-29 09:00:00 | 2023-06-02 17:00:00
17 | enquiry  17 | 2023-06-03 09:30:00 | 2023-06-04 10:00:00 | 0 |  | 2023-06-05 09:00:00 | 2023-06-02 17:00:00
 |  | 2023-05-21 00:00:00 | 2023-05-28 00:00:00 | 5 |  | 2023-05-22 09:00:00 | 2023-05-26 17:00:00
 |  | 2023-06-05 07:00:00 | 2023-06-05 23:00:00 | 1 |  | 2023-06-05 09:00:00 | 2023-06-05 17:00:00
 |  | 2023-06-05 00:00:00 | 2023-06-06 06:00:00 | 1 |  | 2023-06-05 09:00:00 | 2023-06-05 17:00:00
 | Here I am trying to find the amount of business hours an enquiry was open for. Our business hours are Monday to Friday, 9am to 5pm. Enquiries can be opened and closed outside of business hours. If an enquiry is opened outside of business hours, I would like the Date Opened time to start from the next business hour. For example, an enquiry opened on 23/05/2023 10:26 PM, would therefore be open from 24/05/2023 9:00 AM. For enquiries closed, if they are closed outside of business hours,  I would like them to be caclulated from the end of the previous business day. So, for example if an enquiry was closed on Sunday - 4/06/2023 1:13 AM - I would like the close time to be Friday 2/06/2023 5:00 PM. negative numbers I will simply round up to 0. Please note - I am in Australia so dates are represented with dd/mm/yy