rEPOST: 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.

 

[Roger Govier]

Hi Jim

I copied you data onto a sheet, in cells A1:C17.
In D1 enter =MOD(C1-B1,1)*24 copy down through D217
This calculates the hours, and turns them into decimal hours. The MOD()
function allows for any scenario where the end time crosses midnight.
I then entered your list of required dates in cells F1:F5 and in cell G1
entered
=SUMPRODUCT(--($A$1:$A$17>F2),--($A$1:$A$17<=F1),D117)
and copied down through G2:G5
Returned results 5,12,4,0,0

 

[Jim]

ROCK AND ROLL!!!!! Perfect as perfect can be. Thanks

 

[Jim]

Okay, here is another one for you. The last solution you gave me worked
perfectly. On the same sheet as the production hours:

Week Ending: Total
Production
Hours
January 15, 2006 8
January 08, 2006 12
January 01, 2006 12
December 25, 2005 5
December 18, 2005 12

I would like to ask for help to come up with a total count (weekending):

Week ending: January 15, 2006
Status of Contacts #
EC - All 2
EC - Analog Only 3
EC - Dig Only 3
EC - Dig and HIS 0
EC - Dig and Phone 0
EC - HSI Only 0
EC - HSI and Phone 0
EC - Phone Only 0
EC - Other 0

of all inputted data from the from the following:

EC - EC - EC -
All Analog Only Dig Only
1/1/2006
1/2/2006 1 2 3
1/3/2006 3 1 2
1/4/2006 2 3 1
1/5/2006 1 2 3
1/6/2006 3 1 2
1/7/2006 2 3 1
1/8/2006 1 2 3
1/9/2006 3 1 2
1/10/2006 2 3 1
1/11/2006 1 2 3
1/12/2006 3 1 2
1/13/2006 2 3 1
1/14/2006 1 2 3
1/15/2006 3 1 2

 

[Roger Govier]

Hi Jim

The first part of the formula remains the same, as it is just checking
that the dates are between the ranges specified.

Just change the second part to refer the column that contains the
relevant data, and the test being against the column header holding the
value e.g. EC - All

say you had your weekending date in G1 and your week date in F1
and your status of contacts was in G2:10
enter in H2
=SUMPRODUCT(--($A$1:$A$17>$F$1),--($A$1:$A$17<=$G$1),--($B$1:$B$17=G2))
in H3 change it to
=SUMPRODUCT(--($A$1:$A$17>$F$1),--($A$1:$A$17<=$G$1),--($C$1:$C$17=G3))
and so on down the column until you get to H10, stepping up the column
letter by 1 each time and the cell reference in G by 1 row each time