I am hoping someone could provide assistance on writing an in-cell formula to evaluate a record by comparing it's cells (Type, Received Date & Completion Date) with a list of Blackout Date Periods by Type, and calculate the hours.

I have a list of Data records organized by Type, Received Data, and Completion Date. I have another list of Blackout records organized by Type, Start Date, and End Date.

I wish to write a single formula to calculate the hours that the Data record falls within the blackout periods for each type.


Blackout Periods

A B C
Type Start End
2 A Jan 16, 2011 May 15, 2011
3 A May 25, 2011 Jul 11, 2011
4 A Jul 26, 2011 Dec 31, 2011

Data Records

A B C D
Type Received Completed Blackout Period Hours
2 A Jun 12, 2010 Mar 11, 2011 1296
3 A Aug 2, 2010 May 10, 2011
4 A Sep 26, 2010 Apr 21, 2011
5 A Sep 26, 2010 Apr 21, 2011
6 A Sep 26, 2010 Apr 20, 2011
7 A Oct 8, 2010 Apr 28, 2011
8 A Apr 12, 2011 Sep 24, 2011
9 A Apr 15, 2011 Sep 28, 2011
10 A Apr 19, 2011 Sep 29, 2011
11 A Apr 20, 2011 Sep 30, 2011


Here is the formula I created to evaluate the first data record against the first blackout period. The result is 1296 hours.

=IF(DATA!A2="A",IF(DATA!B2<Black_Out_Periods!$B2,IF(DATA!C2>Black_Out_Periods!$C2,((DATA!C2-DATA!B2)*24),IF(DATA!C2<Black_Out_Periods!$B2,0,((DATA!C2-Black_Out_Periods!$B2)*24))),IF(DATA!B2>Black_Out_Periods!$C2,0,IF(DATA!C2>Black_Out_Periods!$C2,((Black_Out_Periods!C2-DATA!B2)*24),(((DATA!C2-DATA!B2)*24))))))

This formula provides the correct number of hours that match Type A and the first blackout period. Creating a new cell with a formula matching the second blackout period is not feasible, as I have 16 Types (A-P) and each type has various blackout periods (from one to eight periods). The total blackout periods for all types is over 200 and there are over 35,000 data records.

There are 6 specific conditions that can exist for each blackout period.

1. The data record covers the full blackout period, therefore the entire black out period is used.

2. The entire data record period occurs before the blackout period (or not during a blackout period)

3. The entire data record period occurs after the blackout period (or not during a blackout period)

4. The data record begins before a blackout period starts but ends during the blackout period, therefore only the time between the beginning of the blackout period and the end of the data record is used.

5. The data record begins during a blackout period but ends after the blackout period is over, therefore only the time between the beginning of the data record and the end of the blackout period is used.

6. The blackout period covers the full data record period, therefore the entire data record period is used.

Additional thoughts:
The three blackout periods for type A are listed above, Jan16-May15, May25-Jun11, and Jul26-Dec31.

The sample calculation (in cell D2) is comparing the data record on row 2 against the first blackout period. The formula determines that the data record meets the criteria in condition #4, therefore it calculates hours from the beginning of blackout period 1 to the end of the data record (Jan16 - Mar11). This one record returns 1296 hours.

An additional example:

Lets take the data record on row 8. This record begins during the first blackout period, includes the entire second blackout period, and ends during the third blackout period. This would return three sets of hours during calculation.

Data Record #8 (Apr12-Sep24)

Blackout Period 1 (Jan16-May15)
- Meets condition #5, Blackout End Date - Data Record RCV Date, May15-Apr12, 792 Hours

Blackout Period 2 (May25-Jun11)
- Meets condition #1, Blackout End Date - Blackout Start Date, Jun11-May25, 1128 Hours

Blackout Period 3 (Jul26-Dec31)
- Meets condition #4, Data Record End Date - Blackout Start Date, Jun11-May25, 1440 Hours

The sum of the three sets of hours (792+1128+1440) 3360 hours, would be the hours associated with Data Record #8 that encompased the three blackout periods.

The solution to my question is how to write a single formula to sum the associated blackout period hours using the blackout periods that meet the type criteria. I have 16 types, and each type has multiple blackout periods.

Let me know if I can provide additional data.

Thomas S. Tucker