Instances of an occurance over 12 months
 

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

Instances of an occurance over 12 months
 

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:A1000TODAY()-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.
--
Gary''s Student - gsnu200779


"HANNAH82" wrote:

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

Instances of an occurance over 12 months
 

Thanks!

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


"Gary''s Student" wrote:

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:A1000TODAY()-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.
--
Gary''s Student - gsnu200779


"HANNAH82" wrote:

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

Instances of an occurance over 12 months
 

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

12/1/2007
3/1/2008

then:

=SUMPRODUCT(--(A1:A1000B1),--(A1:A1000<B2))
--
Gary''s Student - gsnu200779


"HANNAH82" wrote:

Thanks!

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


"Gary''s Student" wrote:

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:A1000TODAY()-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.
--
Gary''s Student - gsnu200779


"HANNAH82" wrote:

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