Help on hours

[Ahmed Sidat]

Hi. I need some help. I have 3 columns. The first column is the employee number i.e. '00014272'etc., the second column is the hours column (Overtime hours) i.e. '14' or '-1.5' etc., and the third column is the date i.e. '01/06/2004'. What I am trying to do is count the number of hours, going back 1 yr from today's date i.e. (now()-365). This will go back 1yr from the current date, so for example going back from todays date would 03/03/04. I want to count the number of hours for any employee within this period. I also want to count the number of hours before this period, so any hours before (example:03/03/04) need to be counted too. I have tried various formulas but none of them seem to work.. Any help would be greatly appreciated. Thanks

[richardreye]

I'm not positive that I have understood your questionin it's fullness but here is my solution from what I have as an understanding of your problem.

Assumptions:

- Data starts in A1 and goes to C10

to get the number of hours for a year use

=SUMPRODUCT(--(A1:A10=12),--(C1:C10>=(NOW()-365)),B1:B10)

or

=SUMPRODUCT(--(A1:A10=12),--(C1:C10>=(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))),B1:B10)

to get the hours over a year old simply change C1:C10>= to C1:C10<

HTH

[Ahmed Sidat]

Thanks. That works but it doesnt work when I change the > to <..!! It comes up as 0..?!?

[Ahmed Sidat]

Thanks man. Got it working. Say there was minus hours in there. Is there any way of adding jus the minus hours..??

[richardreye]

change the last section from

...B1:B10)

to

...--(B1:B10<0),B1:B10)

Cheers!

P.S - do a search on the internet for SUMPRODUCT because it is a function well worth learning.