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