| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
SUM hours by month and day?
I have two worksheets.
The first worksheet is a work schedule, "Schedule2007", which includes a "Date" column, and 12 columns for "Overtime". Column B is the "Date" column with dates formatted as mm/dd/yyyy ranging from row 3 through 420 (July 1 - December 31). The "Overtime" columns are broken down as follows; AD:AF (Labeled "1st Half") = 4 hour shifts; AG:AI (Labeled "2nd Half") = 4 hour shifts; AJ:AO (Labeled "Full Shifts") = 8 hour shifts. Employees enter their initials into the "Overtime" columns based on which shifts they've worked on the corresponding dates in column B. However, employees can enter their initials in the "overtime" columns for future dates. My second sheet will be used to calculate ongoing overtime hours, by month, but I do not want to include hours for dates which have not yet been worked. For example, since today is 10/16/2007, I would like to see overtime hours to date for October, but not those signed up for after today's date. The second worksheet is labeled OvertimeTotalsByMonth and is formatted as follows: Column A = Employees (corresponding to the initials in the overtime columns on the "Schedule2007" worksheet, such as "ABC") Column B:H = "July", "August", "September", etc. Column I = "Total Overtime Hours" (year-to-date by employee) Is there a SUM formula I can use to A) reflect that the cells in the overtime columns on the first sheet = 4 or 8 hour shifts, and B) exclude dates that haven't occurred yet? I'm using Excel 2003. Sorry for the long winded question. Any help is appreciated. Thanks, Mike |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUM hours by month and day?
Rather than try to get this in the second sheet, let's try to make it work
first in the same sheet. Then we can copy formulas to the new sheet. =SUMPRODUCT(--(B3:B420<=TODAY),--(AD3:AD420"")) this should count how many initials are in AD for dates up today and =SUMPRODUCT(--(B3:B420<=TODAY),--(AD3:AD420""))*4 should give the total hours If we want a particular month =SUMPRODUCT(--(MONTH(B3:B420=1),--(AD3:AD420""))*4 should give a total for January Have a go and come back if more help needed best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Mike Slater" wrote in message ... I have two worksheets. The first worksheet is a work schedule, "Schedule2007", which includes a "Date" column, and 12 columns for "Overtime". Column B is the "Date" column with dates formatted as mm/dd/yyyy ranging from row 3 through 420 (July 1 - December 31). The "Overtime" columns are broken down as follows; AD:AF (Labeled "1st Half") = 4 hour shifts; AG:AI (Labeled "2nd Half") = 4 hour shifts; AJ:AO (Labeled "Full Shifts") = 8 hour shifts. Employees enter their initials into the "Overtime" columns based on which shifts they've worked on the corresponding dates in column B. However, employees can enter their initials in the "overtime" columns for future dates. My second sheet will be used to calculate ongoing overtime hours, by month, but I do not want to include hours for dates which have not yet been worked. For example, since today is 10/16/2007, I would like to see overtime hours to date for October, but not those signed up for after today's date. The second worksheet is labeled OvertimeTotalsByMonth and is formatted as follows: Column A = Employees (corresponding to the initials in the overtime columns on the "Schedule2007" worksheet, such as "ABC") Column B:H = "July", "August", "September", etc. Column I = "Total Overtime Hours" (year-to-date by employee) Is there a SUM formula I can use to A) reflect that the cells in the overtime columns on the first sheet = 4 or 8 hour shifts, and B) exclude dates that haven't occurred yet? I'm using Excel 2003. Sorry for the long winded question. Any help is appreciated. Thanks, Mike |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUM hours by month and day?
Thanks, Sandy
-- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sandy Mann" wrote in message ... You have a couple of typos Bernard, TODAY should be TODAY() and all three forumulas will accept blank cells as being <TODAY() and January -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Bernard Liengme" wrote in message ... Rather than try to get this in the second sheet, let's try to make it work first in the same sheet. Then we can copy formulas to the new sheet. =SUMPRODUCT(--(B3:B420<=TODAY),--(AD3:AD420"")) this should count how many initials are in AD for dates up today and =SUMPRODUCT(--(B3:B420<=TODAY),--(AD3:AD420""))*4 should give the total hours If we want a particular month =SUMPRODUCT(--(MONTH(B3:B420=1),--(AD3:AD420""))*4 should give a total for January Have a go and come back if more help needed best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Mike Slater" wrote in message ... I have two worksheets. The first worksheet is a work schedule, "Schedule2007", which includes a "Date" column, and 12 columns for "Overtime". Column B is the "Date" column with dates formatted as mm/dd/yyyy ranging from row 3 through 420 (July 1 - December 31). The "Overtime" columns are broken down as follows; AD:AF (Labeled "1st Half") = 4 hour shifts; AG:AI (Labeled "2nd Half") = 4 hour shifts; AJ:AO (Labeled "Full Shifts") = 8 hour shifts. Employees enter their initials into the "Overtime" columns based on which shifts they've worked on the corresponding dates in column B. However, employees can enter their initials in the "overtime" columns for future dates. My second sheet will be used to calculate ongoing overtime hours, by month, but I do not want to include hours for dates which have not yet been worked. For example, since today is 10/16/2007, I would like to see overtime hours to date for October, but not those signed up for after today's date. The second worksheet is labeled OvertimeTotalsByMonth and is formatted as follows: Column A = Employees (corresponding to the initials in the overtime columns on the "Schedule2007" worksheet, such as "ABC") Column B:H = "July", "August", "September", etc. Column I = "Total Overtime Hours" (year-to-date by employee) Is there a SUM formula I can use to A) reflect that the cells in the overtime columns on the first sheet = 4 or 8 hour shifts, and B) exclude dates that haven't occurred yet? I'm using Excel 2003. Sorry for the long winded question. Any help is appreciated. Thanks, Mike |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUM hours by month and day?
Sorry for the delay in responding.
Thank you both for your assistance. After some minor head scratching, I've got your formula working the way I need it to. Thanks again, Mike "Sandy Mann" wrote in message ... You have a couple of typos Bernard, TODAY should be TODAY() and all three forumulas will accept blank cells as being <TODAY() and January -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Bernard Liengme" wrote in message ... Rather than try to get this in the second sheet, let's try to make it work first in the same sheet. Then we can copy formulas to the new sheet. =SUMPRODUCT(--(B3:B420<=TODAY),--(AD3:AD420"")) this should count how many initials are in AD for dates up today and =SUMPRODUCT(--(B3:B420<=TODAY),--(AD3:AD420""))*4 should give the total hours If we want a particular month =SUMPRODUCT(--(MONTH(B3:B420=1),--(AD3:AD420""))*4 should give a total for January Have a go and come back if more help needed best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Mike Slater" wrote in message ... I have two worksheets. The first worksheet is a work schedule, "Schedule2007", which includes a "Date" column, and 12 columns for "Overtime". Column B is the "Date" column with dates formatted as mm/dd/yyyy ranging from row 3 through 420 (July 1 - December 31). The "Overtime" columns are broken down as follows; AD:AF (Labeled "1st Half") = 4 hour shifts; AG:AI (Labeled "2nd Half") = 4 hour shifts; AJ:AO (Labeled "Full Shifts") = 8 hour shifts. Employees enter their initials into the "Overtime" columns based on which shifts they've worked on the corresponding dates in column B. However, employees can enter their initials in the "overtime" columns for future dates. My second sheet will be used to calculate ongoing overtime hours, by month, but I do not want to include hours for dates which have not yet been worked. For example, since today is 10/16/2007, I would like to see overtime hours to date for October, but not those signed up for after today's date. The second worksheet is labeled OvertimeTotalsByMonth and is formatted as follows: Column A = Employees (corresponding to the initials in the overtime columns on the "Schedule2007" worksheet, such as "ABC") Column B:H = "July", "August", "September", etc. Column I = "Total Overtime Hours" (year-to-date by employee) Is there a SUM formula I can use to A) reflect that the cells in the overtime columns on the first sheet = 4 or 8 hour shifts, and B) exclude dates that haven't occurred yet? I'm using Excel 2003. Sorry for the long winded question. Any help is appreciated. Thanks, Mike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count hours per month | Excel Worksheet Functions | |||
| wages - multiply hours and minutes by number of hours worked | Excel Discussion (Misc queries) | |||
| Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis | Excel Discussion (Misc queries) | |||
| Sum of Hours Per Month | New Users to Excel | |||
| Add up the hours for each month | Excel Worksheet Functions | |||
Linear Mode
