software

Hello,

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

Production In Production Out
December 09, 20056:00:00 PM9:00:00 PM
December 10, 20051:00:00 PM6:00:00 PM
December 11, 20051:00:00 PM5:00:00 PM
December 12, 2005
December 13, 2005
December 14, 2005
December 15, 2005
December 16, 20056:00:00 PM9:00:00 PM
December 17, 20051:00:00 PM6:00:00 PM
December 18, 20051:00:00 PM5:00:00 PM
December 19, 2005
December 20, 2005
December 21, 2005
December 22, 2005
December 23, 20056:00:00 PM8:00:00 PM
December 24, 200512:00:00 PM3: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.
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$17gt;F2),--($A$1:$A$17lt;=F1),D117)
and copied down through G2:G5
Returned results 5,12,4,0,0

--
Regards

Roger Govierquot;Jimquot; gt; wrote in message
...
gt; Hello,
gt;
gt; The first chart below shows dates and clock in time and clock out
gt; times
gt; (formatted as time).
gt;
gt; Production In Production Out
gt; December 09, 2005 6:00:00 PM 9:00:00 PM
gt; December 10, 2005 1:00:00 PM 6:00:00 PM
gt; December 11, 2005 1:00:00 PM 5:00:00 PM
gt; December 12, 2005
gt; December 13, 2005
gt; December 14, 2005
gt; December 15, 2005
gt; December 16, 2005 6:00:00 PM 9:00:00 PM
gt; December 17, 2005 1:00:00 PM 6:00:00 PM
gt; December 18, 2005 1:00:00 PM 5:00:00 PM
gt; December 19, 2005
gt; December 20, 2005
gt; December 21, 2005
gt; December 22, 2005
gt; December 23, 2005 6:00:00 PM 8:00:00 PM
gt; December 24, 2005 12:00:00 PM 3:00:00 PM
gt; December 25, 2005
gt;
gt; The next chart below shows where I would like the total hours to
gt; appear.
gt; However the chart below shows week ending dates. I need a formula
gt; that will
gt; calculate not only the date in the first column but also the previous
gt; 6 days
gt; (before the date).
gt;
gt; Note: The week ending date does change week to week so the formula
gt; would
gt; need to reference a cell number, not a specific date.
gt;
gt; Total
gt; Production
gt; Hour
gt; December 25, 2005
gt; December 18, 2005
gt; December 11, 2005
gt; December 04, 2005
gt; November 27, 2005
gt;
gt; Thanks for the help.
gt;
gt;
ROCK AND ROLL!!!!! Perfect as perfect can be. Thanks

quot;Roger Govierquot; wrote:

gt; Hi Jim
gt;
gt; I copied you data onto a sheet, in cells A1:C17.
gt; In D1 enter =MOD(C1-B1,1)*24 copy down through D217
gt; This calculates the hours, and turns them into decimal hours. The MOD()
gt; function allows for any scenario where the end time crosses midnight.
gt; I then entered your list of required dates in cells F1:F5 and in cell G1
gt; entered
gt; =SUMPRODUCT(--($A$1:$A$17gt;F2),--($A$1:$A$17lt;=F1),D117)
gt; and copied down through G2:G5
gt; Returned results 5,12,4,0,0
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;Jimquot; gt; wrote in message
gt; ...
gt; gt; Hello,
gt; gt;
gt; gt; The first chart below shows dates and clock in time and clock out
gt; gt; times
gt; gt; (formatted as time).
gt; gt;
gt; gt; Production In Production Out
gt; gt; December 09, 2005 6:00:00 PM 9:00:00 PM
gt; gt; December 10, 2005 1:00:00 PM 6:00:00 PM
gt; gt; December 11, 2005 1:00:00 PM 5:00:00 PM
gt; gt; December 12, 2005
gt; gt; December 13, 2005
gt; gt; December 14, 2005
gt; gt; December 15, 2005
gt; gt; December 16, 2005 6:00:00 PM 9:00:00 PM
gt; gt; December 17, 2005 1:00:00 PM 6:00:00 PM
gt; gt; December 18, 2005 1:00:00 PM 5:00:00 PM
gt; gt; December 19, 2005
gt; gt; December 20, 2005
gt; gt; December 21, 2005
gt; gt; December 22, 2005
gt; gt; December 23, 2005 6:00:00 PM 8:00:00 PM
gt; gt; December 24, 2005 12:00:00 PM 3:00:00 PM
gt; gt; December 25, 2005
gt; gt;
gt; gt; The next chart below shows where I would like the total hours to
gt; gt; appear.
gt; gt; However the chart below shows week ending dates. I need a formula
gt; gt; that will
gt; gt; calculate not only the date in the first column but also the previous
gt; gt; 6 days
gt; gt; (before the date).
gt; gt;
gt; gt; Note: The week ending date does change week to week so the formula
gt; gt; would
gt; gt; need to reference a cell number, not a specific date.
gt; gt;
gt; gt; Total
gt; gt; Production
gt; gt; Hour
gt; gt; December 25, 2005
gt; gt; December 18, 2005
gt; gt; December 11, 2005
gt; gt; December 04, 2005
gt; gt; November 27, 2005
gt; gt;
gt; gt; Thanks for the help.
gt; gt;
gt; gt;
gt;
gt;
gt;

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, 20068
January 08, 200612
January 01, 200612
December 25, 20055
December 18, 200512

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 Only3
EC - Dig Only3
EC - Dig and HIS0
EC - Dig and Phone0
EC - HSI Only0
EC - HSI and Phone0
EC - Phone Only0
EC - Other0

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 2quot;Roger Govierquot; wrote:

gt; Hi Jim
gt;
gt; I copied you data onto a sheet, in cells A1:C17.
gt; In D1 enter =MOD(C1-B1,1)*24 copy down through D217
gt; This calculates the hours, and turns them into decimal hours. The MOD()
gt; function allows for any scenario where the end time crosses midnight.
gt; I then entered your list of required dates in cells F1:F5 and in cell G1
gt; entered
gt; =SUMPRODUCT(--($A$1:$A$17gt;F2),--($A$1:$A$17lt;=F1),D117)
gt; and copied down through G2:G5
gt; Returned results 5,12,4,0,0
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;Jimquot; gt; wrote in message
gt; ...
gt; gt; Hello,
gt; gt;
gt; gt; The first chart below shows dates and clock in time and clock out
gt; gt; times
gt; gt; (formatted as time).
gt; gt;
gt; gt; Production In Production Out
gt; gt; December 09, 2005 6:00:00 PM 9:00:00 PM
gt; gt; December 10, 2005 1:00:00 PM 6:00:00 PM
gt; gt; December 11, 2005 1:00:00 PM 5:00:00 PM
gt; gt; December 12, 2005
gt; gt; December 13, 2005
gt; gt; December 14, 2005
gt; gt; December 15, 2005
gt; gt; December 16, 2005 6:00:00 PM 9:00:00 PM
gt; gt; December 17, 2005 1:00:00 PM 6:00:00 PM
gt; gt; December 18, 2005 1:00:00 PM 5:00:00 PM
gt; gt; December 19, 2005
gt; gt; December 20, 2005
gt; gt; December 21, 2005
gt; gt; December 22, 2005
gt; gt; December 23, 2005 6:00:00 PM 8:00:00 PM
gt; gt; December 24, 2005 12:00:00 PM 3:00:00 PM
gt; gt; December 25, 2005
gt; gt;
gt; gt; The next chart below shows where I would like the total hours to
gt; gt; appear.
gt; gt; However the chart below shows week ending dates. I need a formula
gt; gt; that will
gt; gt; calculate not only the date in the first column but also the previous
gt; gt; 6 days
gt; gt; (before the date).
gt; gt;
gt; gt; Note: The week ending date does change week to week so the formula
gt; gt; would
gt; gt; need to reference a cell number, not a specific date.
gt; gt;
gt; gt; Total
gt; gt; Production
gt; gt; Hour
gt; gt; December 25, 2005
gt; gt; December 18, 2005
gt; gt; December 11, 2005
gt; gt; December 04, 2005
gt; gt; November 27, 2005
gt; gt;
gt; gt; Thanks for the help.
gt; gt;
gt; gt;
gt;
gt;
gt;

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$17gt;$F$1),--($A$1:$A$17lt;=$G$1),--($B$1:$B$17=G2))
in H3 change it to
=SUMPRODUCT(--($A$1:$A$17gt;$F$1),--($A$1:$A$17lt;=$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

--
Regards

Roger Govierquot;Jimquot; gt; wrote in message
...
gt; Okay, here is another one for you. The last solution you gave me
gt; worked
gt; perfectly. On the same sheet as the production hours:
gt;
gt; Week Ending: Total
gt; Production
gt; Hours
gt; January 15, 2006 8
gt; January 08, 2006 12
gt; January 01, 2006 12
gt; December 25, 2005 5
gt; December 18, 2005 12
gt;
gt; I would like to ask for help to come up with a total count
gt; (weekending):
gt;
gt; Week ending: January 15, 2006
gt; Status of Contacts #
gt; EC - All 2
gt; EC - Analog Only 3
gt; EC - Dig Only 3
gt; EC - Dig and HIS 0
gt; EC - Dig and Phone 0
gt; EC - HSI Only 0
gt; EC - HSI and Phone 0
gt; EC - Phone Only 0
gt; EC - Other 0
gt;
gt; of all inputted data from the from the following:
gt;
gt; EC - EC - EC -
gt; All Analog Only Dig Only
gt; 1/1/2006
gt; 1/2/2006 1 2 3
gt; 1/3/2006 3 1 2
gt; 1/4/2006 2 3 1
gt; 1/5/2006 1 2 3
gt; 1/6/2006 3 1 2
gt; 1/7/2006 2 3 1
gt; 1/8/2006 1 2 3
gt; 1/9/2006 3 1 2
gt; 1/10/2006 2 3 1
gt; 1/11/2006 1 2 3
gt; 1/12/2006 3 1 2
gt; 1/13/2006 2 3 1
gt; 1/14/2006 1 2 3
gt; 1/15/2006 3 1 2
gt;
gt;
gt; quot;Roger Govierquot; wrote:
gt;
gt;gt; Hi Jim
gt;gt;
gt;gt; I copied you data onto a sheet, in cells A1:C17.
gt;gt; In D1 enter =MOD(C1-B1,1)*24 copy down through D217
gt;gt; This calculates the hours, and turns them into decimal hours. The
gt;gt; MOD()
gt;gt; function allows for any scenario where the end time crosses midnight.
gt;gt; I then entered your list of required dates in cells F1:F5 and in cell
gt;gt; G1
gt;gt; entered
gt;gt; =SUMPRODUCT(--($A$1:$A$17gt;F2),--($A$1:$A$17lt;=F1),D117)
gt;gt; and copied down through G2:G5
gt;gt; Returned results 5,12,4,0,0
gt;gt;
gt;gt; --
gt;gt; Regards
gt;gt;
gt;gt; Roger Govier
gt;gt;
gt;gt;
gt;gt; quot;Jimquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hello,
gt;gt; gt;
gt;gt; gt; The first chart below shows dates and clock in time and clock out
gt;gt; gt; times
gt;gt; gt; (formatted as time).
gt;gt; gt;
gt;gt; gt; Production In Production
gt;gt; gt; Out
gt;gt; gt; December 09, 2005 6:00:00 PM 9:00:00 PM
gt;gt; gt; December 10, 2005 1:00:00 PM 6:00:00 PM
gt;gt; gt; December 11, 2005 1:00:00 PM 5:00:00 PM
gt;gt; gt; December 12, 2005
gt;gt; gt; December 13, 2005
gt;gt; gt; December 14, 2005
gt;gt; gt; December 15, 2005
gt;gt; gt; December 16, 2005 6:00:00 PM 9:00:00 PM
gt;gt; gt; December 17, 2005 1:00:00 PM 6:00:00 PM
gt;gt; gt; December 18, 2005 1:00:00 PM 5:00:00 PM
gt;gt; gt; December 19, 2005
gt;gt; gt; December 20, 2005
gt;gt; gt; December 21, 2005
gt;gt; gt; December 22, 2005
gt;gt; gt; December 23, 2005 6:00:00 PM 8:00:00 PM
gt;gt; gt; December 24, 2005 12:00:00 PM 3:00:00 PM
gt;gt; gt; December 25, 2005
gt;gt; gt;
gt;gt; gt; The next chart below shows where I would like the total hours to
gt;gt; gt; appear.
gt;gt; gt; However the chart below shows week ending dates. I need a formula
gt;gt; gt; that will
gt;gt; gt; calculate not only the date in the first column but also the
gt;gt; gt; previous
gt;gt; gt; 6 days
gt;gt; gt; (before the date).
gt;gt; gt;
gt;gt; gt; Note: The week ending date does change week to week so the formula
gt;gt; gt; would
gt;gt; gt; need to reference a cell number, not a specific date.
gt;gt; gt;
gt;gt; gt; Total
gt;gt; gt; Production
gt;gt; gt; Hour
gt;gt; gt; December 25, 2005
gt;gt; gt; December 18, 2005
gt;gt; gt; December 11, 2005
gt;gt; gt; December 04, 2005
gt;gt; gt; November 27, 2005
gt;gt; gt;
gt;gt; gt; Thanks for the help.
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;