Compare 1st Half & 2nd Half of 2008.

[Jerry L]

Column A = dates...the date an employee worked
Column B = numbers....the hours an employee worked overtime

I have 70 employees / worksheets.
I've been asked to find the total hours worked for each employee for the 1st
half and 2nd half of the year.
Then determine weather more hours were worked the 1st or 2nd half of the year.
I am thinking I should use 2 IF/Then statements
If 12/31/07 < A1 > 7/1/08, then add B1
If 6/30/08 < A1 > 1/1/09, then add B1

Entries in Column will not exceed 100 per worksheet A1:A100.

Your help greatly appreciated.
Jerry

 

[Chip Pearson]

To calculate the sum for the first half of the year, use

=SUMPRODUCT((A1:A100>DATE(YEAR(NOW())-1,12,31))*(A1:A100<DATE(YEAR(NOW()),7,1))*B1:B100)


To calculate the sum for the second half of the year, use

=SUMPRODUCT((A1:A100>=DATE(YEAR(NOW()),7,1))*B1:B100)

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Glenn]

Column A = dates...the date an employee worked
Column B = numbers....the hours an employee worked overtime

I have 70 employees / worksheets.
I've been asked to find the total hours worked for each employee for the 1st
half and 2nd half of the year.
Then determine weather more hours were worked the 1st or 2nd half of the year.
I am thinking I should use 2 IF/Then statements
If 12/31/07 < A1 > 7/1/08, then add B1
If 6/30/08 < A1 > 1/1/09, then add B1

Entries in Column will not exceed 100 per worksheet A1:A100.

Your help greatly appreciated.
Jerry

First half:

=SUMPRODUCT((YEAR(A1:A100)=2008)*(MONTH(A1:A100)<7)*(B1:B100))


Second half:

=SUMPRODUCT((YEAR(A1:A100)=2008)*(MONTH(A1:A100)>6)*(B1:B100))

 

[Jerry L]

FANTASTIC !!
Chip you saved me hours of work.

Thanks,
Jerry