Instances of an occurance over 12 months

[HANNAH82]

Hi,

I'm trying to set up, in excel, a way of calculating the number of days an
employee has off sick within a rolling year. At present i have it set so it
calculates the number of working days between two dates, repeating this each
time the person is off. It then calculates the total days (using a simple
SUM of). I have also set it so it puts in todays date.

Any help would be apreciated!!!

Thanks

 

[Gary''s Student]

Put the dates the person is off sick in column A. The list can be in either
ascending or descending order. In another cell enter:

=SUMPRODUCT(--(A1:A1000>TODAY()-365))

For example:

4/14/2008
4/4/2008
4/3/2008
3/17/2008
2/25/2008
2/5/2008
1/11/2008
12/7/2007
11/10/2007
9/16/2007
7/19/2007
7/18/2007
6/12/2007
5/13/2007
5/12/2007
4/6/2007
3/3/2007
3/2/2007

The formula will return 15, ignoring entries older than 1 year.

 

[HANNAH82]

Thanks!

Is there anyway i can also count the number of occurances within any three
month period? Cheers

 

[Gary''s Student]

Let B1 contain a start date and B2 a stop date:

12/1/2007
3/1/2008

then:

=SUMPRODUCT(--(A1:A1000>B1),--(A1:A1000<B2))