Elapsed Workday Minutes Between 2 Date Timestamps

I have an excel sheet with about 50,000 records where I need to find the number of minutes between two date timestamps but I need to exclude any minutes that occurred during the times we are not working.

Our schedule is M-F 8:30am-5:30pm, Saturdays 8:30am-1:30pm We don't work Sundays or holidays.

As an example
Cell B2: [7/3/2020 2:16:21 PM]
Cell C2: [7/6/2020 9:20:23 AM]

(C2-B2)*24*60 = 4,024.03, however that includes all minutes. I need to exclude:
- hours after 5:30pm on Friday
- Saturday hours because July 4 was a holiday
- Sunday, we're closed
- hours before 8:30am on Monday because we hadn't opened yet

The manually calculated answer for this one should be about 244 minutes.

Originally Posted by bteague

Our schedule is M-F 8:30am-5:30pm, Saturdays 8:30am-1:30pm We don't work Sundays or holidays.

3/2/2020 17:59:32-3/2/2020 18:05:42
No minute should be count after 17:30

D2

Formula:

Please Login or Register  to view this content.

Custom format to [m]:ss