Summing clock in and out by date

[Jim]

Hello,

The first chart below shows dates and clock in time and clock out times
(formatted as time).

Production In Production Out
December 09, 2005 6:00:00 PM 9:00:00 PM
December 10, 2005 1:00:00 PM 6:00:00 PM
December 11, 2005 1:00:00 PM 5:00:00 PM
December 12, 2005
December 13, 2005
December 14, 2005
December 15, 2005
December 16, 2005 6:00:00 PM 9:00:00 PM
December 17, 2005 1:00:00 PM 6:00:00 PM
December 18, 2005 1:00:00 PM 5:00:00 PM
December 19, 2005
December 20, 2005
December 21, 2005
December 22, 2005
December 23, 2005 6:00:00 PM 8:00:00 PM
December 24, 2005 12:00:00 PM 3:00:00 PM
December 25, 2005

The next chart below shows where I would like the total hours to appear.
However the chart below shows week ending dates. I need a formula that will
calculate not only the date in the first column but also the previous 6 days
(before the date).

Note: The week ending date does change week to week so the formula would
need to reference a cell number, not a specific date.

Total
Production
Hour
December 25, 2005
December 18, 2005
December 11, 2005
December 04, 2005
November 27, 2005

Thanks for the help.

 

[vezerid]

The following *array* formula (must be committed with Shift+Ctrl+Enter)
will calculate the total hours for the week prior to a date in K1.

=SUM(IF(K1-$A$1:$A$100<7, VLOOKUP($A$1:$A$100,
$A$1:$C$100,3,0)-VLOOKUP($A$1:$A$100, $A$1:$C$100,2,0),0))

Format the cell as [h]:mm:ss

Does this help?
Kostis Vezerides