SUM hours by month and day?

[Mike Slater]

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

 

[Bernard Liengme]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Bernard Liengme]

Thanks, Sandy

 

[Michael Slater]

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