Spreadsheet: 697421d1601351433-count-office-and-non-office-hour-between-dates-excel-example_v4
Sheet: Sheet1

To calculate the no. of Office Hours (and Non-Office Hours) excluding the defined office hours (Mon-Fri, 09:00-18:00), weekends and Public Holidays
 | Engineer Onsite Date | Engineer Onsite Time | Engineer Offsite Date | Engineer Offsite Time | No of Office Hours * | No of Non-Office Hours * |  | Office Hour Definition | In | out
Example 1 - Sep 5 is Weekend (SAT) | 2020-09-04 00:00:00 | 17:30:00 | 2020-09-05 00:00:00 | 01:30:00 |  |  |  | Monday to Friday, 09:00 - 18:00 | 09:00:00 | 18:00:00
Example 2 - Oct 1 is Public Holiday | 2020-10-01 00:00:00 | 16:00:00 | 2020-10-01 00:00:00 | 17:30:00
Example 3 - Across Weekdays & Weekend | 2020-09-25 00:00:00 | 17:00:00 | 2020-09-28 00:00:00 | 02:30:00
Example 4 - Across Weekdays, Public Holidays, Weekend | 2020-09-30 00:00:00 | 16:30:00 | 2020-10-05 00:00:00 | 13:00:00 |  |  |  | Non Office Hour Definition
 |  |  |  |  |  |  |  | Monday to Friday, other than above working hours
 |  |  |  |  |  |  |  | Anytime in Saturday and Sunday
 |  |  |  |  |  |  |  | Public Holiday as below:
 |  |  |  |  |  |  |  | Jan 1, 2020 | 2020-01-01 00:00:00
 |  |  |  |  |  |  |  | Jan 25, 2020 | 2020-01-25 00:00:00
 |  |  |  |  |  |  |  | Jan 26, 2020 | 2020-01-26 00:00:00
 |  |  |  |  |  |  |  | Jan 27, 2020 | 2020-01-27 00:00:00
 |  |  |  |  |  |  |  | Jan 28, 2020 | 2020-01-28 00:00:00
 |  |  |  |  |  |  |  | Apr 4, 2020 | 2020-04-04 00:00:00
 |  |  |  |  |  |  |  | Apr 10, 2020 | 2020-04-10 00:00:00
 |  |  |  |  |  |  |  | Apr 11, 2020 | 2020-04-11 00:00:00
 |  |  |  |  |  |  |  | Apr 13, 2020 | 2020-04-13 00:00:00
 |  |  |  |  |  |  |  | Apr 30, 2020 | 2020-04-30 00:00:00
 |  |  |  |  |  |  |  | May 1, 2020 | 2020-05-01 00:00:00
 |  |  |  |  |  |  |  | Jun 25, 2020 | 2020-06-25 00:00:00
 |  |  |  |  |  |  |  | Jul 1, 2020 | 2020-07-01 00:00:00
 |  |  |  |  |  |  |  | Oct 1, 2020 | 2020-10-01 00:00:00
 |  |  |  |  |  |  |  | Oct 2, 2020 | 2020-10-02 00:00:00
 |  |  |  |  |  |  |  | Oct 26, 2020 | 2020-10-26 00:00:00
 |  |  |  |  |  |  |  | Dec 25, 2020 | 2020-12-25 00:00:00
 |  |  |  |  |  |  |  | Dec 26, 2020 | 2020-12-26 00:00:00
To calculate the no. of Office Hours (and Non-Office Hours) excluding the defined office hours (Mon-Fri, 09:00-18:00, Sat 09:00-13:00), Sunday and Public Holidays
 | Engineer Onsite Date | Engineer Onsite Time | Engineer Offsite Date | Engineer Offsite Time | No of Office Hours * | No of Non-Office Hours * |  | Office Hour Definition | In | out
Example 5 - Sep 5 is Weekend (SAT) | 2020-09-05 00:00:00 | 09:00:00 | 2020-09-05 00:00:00 | 10:00:00 |  |  |  | Monday to Friday, 09:00 - 18:00 | 09:00:00 | 18:00:00
Example 6 - Oct 1 is Public Holiday | 2020-10-01 00:00:00 | 16:00:00 | 2020-10-01 00:00:00 | 17:30:00 |  |  |  | Saturday | 09:00:00 | 13:00:00
Example 7 - Across Weekdays & Weekend | 2020-09-25 00:00:00 | 17:00:00 | 2020-09-28 00:00:00 | 02:30:00
Example 8 - Across Weekdays, Public Holidays, Weekend | 2020-09-30 00:00:00 | 16:30:00 | 2020-10-05 00:00:00 | 13:00:00 |  |  |  | Non Office Hour Definition
 |  |  |  |  |  |  |  | Monday to Saturday, other than above working hours
 |  |  |  |  |  |  |  | Anytime in Sunday
 |  |  |  |  |  |  |  | Public Holiday as below:
 |  |  |  |  |  |  |  | Jan 1, 2020 | 2020-01-01 00:00:00
 |  |  |  |  |  |  |  | Jan 25, 2020 | 2020-01-25 00:00:00
 |  |  |  |  |  |  |  | Jan 26, 2020 | 2020-01-26 00:00:00
 |  |  |  |  |  |  |  | Jan 27, 2020 | 2020-01-27 00:00:00
 |  |  |  |  |  |  |  | Jan 28, 2020 | 2020-01-28 00:00:00
 |  |  |  |  |  |  |  | Apr 4, 2020 | 2020-04-04 00:00:00
 |  |  |  |  |  |  |  | Apr 10, 2020 | 2020-04-10 00:00:00
 |  |  |  |  |  |  |  | Apr 11, 2020 | 2020-04-11 00:00:00
 |  |  |  |  |  |  |  | Apr 13, 2020 | 2020-04-13 00:00:00
 |  |  |  |  |  |  |  | Apr 30, 2020 | 2020-04-30 00:00:00
 |  |  |  |  |  |  |  | May 1, 2020 | 2020-05-01 00:00:00
 |  |  |  |  |  |  |  | Jun 25, 2020 | 2020-06-25 00:00:00
 |  |  |  |  |  |  |  | Jul 1, 2020 | 2020-07-01 00:00:00
 |  |  |  |  |  |  |  | Oct 1, 2020 | 2020-10-01 00:00:00
 |  |  |  |  |  |  |  | Oct 2, 2020 | 2020-10-02 00:00:00
 |  |  |  |  |  |  |  | Oct 26, 2020 | 2020-10-26 00:00:00
 |  |  |  |  |  |  |  | Dec 25, 2020 | 2020-12-25 00:00:00
 |  |  |  |  |  |  |  | Dec 26, 2020 | 2020-12-26 00:00:00