| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rEPOST: Summing clock in and out by date
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rEPOST: Summing clock in and out by date
Hi Jim
I copied you data onto a sheet, in cells A1:C17. In D1 enter =MOD(C1-B1,1)*24 copy down through D2:D17 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$17F2),--($A$1:$A$17<=F1),D1:D17) and copied down through G2:G5 Returned results 5,12,4,0,0 -- Regards Roger Govier "Jim" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rEPOST: Summing clock in and out by date
ROCK AND ROLL!!!!! Perfect as perfect can be. Thanks
"Roger Govier" wrote: Hi Jim I copied you data onto a sheet, in cells A1:C17. In D1 enter =MOD(C1-B1,1)*24 copy down through D2:D17 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$17F2),--($A$1:$A$17<=F1),D1:D17) and copied down through G2:G5 Returned results 5,12,4,0,0 -- Regards Roger Govier "Jim" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rEPOST: Summing clock in and out by date
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" wrote: Hi Jim I copied you data onto a sheet, in cells A1:C17. In D1 enter =MOD(C1-B1,1)*24 copy down through D2:D17 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$17F2),--($A$1:$A$17<=F1),D1:D17) and copied down through G2:G5 Returned results 5,12,4,0,0 -- Regards Roger Govier "Jim" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rEPOST: Summing clock in and out by date
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 -- Regards Roger Govier "Jim" wrote in message ... 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" wrote: Hi Jim I copied you data onto a sheet, in cells A1:C17. In D1 enter =MOD(C1-B1,1)*24 copy down through D2:D17 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$17F2),--($A$1:$A$17<=F1),D1:D17) and copied down through G2:G5 Returned results 5,12,4,0,0 -- Regards Roger Govier "Jim" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
