Hi Everyone,

Seeking for your assistance on this problem..

Given:
downtime: 3/11/2014 11:50:00 PM
uptime: 3/12/2014 1:30:00 AM

business days:
1 = Sunday, 2 = Monday, 3 = Tuesday...7 = Saturday

Store Business Hours Schedule:
Business Day StartHR EndHR
1 (sunday) 10:00 22:00
2 (monday) 9:00 21:00
3 (tuesday) 9:00 21:00
4 (wednesday) 9:00 21:00
5 (thursday) 9:00 21:00
6 (friday) 9:00 21:00
7 (saturday) 10:00 22:00


Problems:
To calculate the duration of the downtime (example is from 3/11/2014 11:50:00 PM to 3/12/2014 1:30:00 AM) if it falls into store business hours schedule. If not, return the value into "No downtime during non-business hours"


NOTE:
1. You don't have to consider any holiday
2. I will use the formula in diferrent stores with different Store Business Hours Schedules


Hoping for your immediate feedback on this as I am working on our SLA Report. Thank you in advance and God bless.