Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
Summing clock in and out by date
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|