Calculating absence periods in a rolling 12 months in excel

(Mark) wrote in message . com...

Thanks Frank

However have put calculations in and am getting '0' as a result.

The dates in column A are as follows (first date of absence period):

11/07/2003
15/02/2003
19/10/2003
10/05/2004
02/06/2004

The dates in column B are as follows (last date of absence period):

15/07/2003
24/02/2003
28/10/2003
14/05/2004
04/06/2004

Column C1 reads: 14/06/2004 (=now())
Column D1 reads: 01/06/2003 (using your calculation to get rolling
tweleve months)
Column E1 reads: 0 absence periods

Looking at columns A to B, those periods of absence that fall within
01/06/2003 and 14/06/2004 should be 4

Any thoughts?


Would also like to apply calculations to same data set to show in
cells:

Number of periods of absence in last three months
Number of periods of absence in last six months
Number of periods of absence in last nine months

Thanks again.
Mark

Hi Mark,

Try,

12 Months:

D1=DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())-(DAY(NOW())DAY(DATE(YEAR(
NOW())-1,MONTH(NOW()),DAY(NOW())-(DAY(NOW()))))))

3 Months:

D2=DATE(YEAR(NOW()),MONTH(NOW())-3,DAY(NOW())-(DAY(NOW())DAY(DATE(YEAR(
NOW()),MONTH(NOW())-3,DAY(NOW())-(DAY(NOW()))))))

6 Months:

D3==DATE(YEAR(NOW()),MONTH(NOW())-6,DAY(NOW())-(DAY(NOW())DAY(DATE(YEAR
(NOW()),MONTH(NOW())-6,DAY(NOW())-(DAY(NOW()))))))

9 Months:

=DATE(YEAR(NOW()),MONTH(NOW())-9,DAY(NOW())-(DAY(NOW())DAY(DATE(YEAR(NO
W()),MONTH(NOW())-9,DAY(NOW())-(DAY(NOW()))))))

Then, put this formula in E1 and copy down:

=SUMPRODUCT(--($A$1:$A$1000=D1),--($A$1:$A$1000=$C$1),--($B$1:$B$1000=
D1),--($B$1:$B$1000=$C$1))

Hope this helps!

In article ,
(Mark) wrote:

(Mark) wrote in message
. com...

Thanks Frank

However have put calculations in and am getting '0' as a result.

The dates in column A are as follows (first date of absence period):

11/07/2003
15/02/2003
19/10/2003
10/05/2004
02/06/2004

The dates in column B are as follows (last date of absence period):

15/07/2003
24/02/2003
28/10/2003
14/05/2004
04/06/2004

Column C1 reads: 14/06/2004 (=now())
Column D1 reads: 01/06/2003 (using your calculation to get rolling
tweleve months)
Column E1 reads: 0 absence periods

Looking at columns A to B, those periods of absence that fall within
01/06/2003 and 14/06/2004 should be 4

Any thoughts?


Would also like to apply calculations to same data set to show in
cells:

Number of periods of absence in last three months
Number of periods of absence in last six months
Number of periods of absence in last nine months

Thanks again.
Mark

Thanks Domenic - think I'm getting the hang of this now.

Domenic wrote in message ...
Hi Mark,

Try,

12 Months:

D1=DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())-(DAY(NOW())DAY(DATE(YEAR(
NOW())-1,MONTH(NOW()),DAY(NOW())-(DAY(NOW()))))))

3 Months:

D2=DATE(YEAR(NOW()),MONTH(NOW())-3,DAY(NOW())-(DAY(NOW())DAY(DATE(YEAR(
NOW()),MONTH(NOW())-3,DAY(NOW())-(DAY(NOW()))))))

6 Months:

D3==DATE(YEAR(NOW()),MONTH(NOW())-6,DAY(NOW())-(DAY(NOW())DAY(DATE(YEAR
(NOW()),MONTH(NOW())-6,DAY(NOW())-(DAY(NOW()))))))

9 Months:

=DATE(YEAR(NOW()),MONTH(NOW())-9,DAY(NOW())-(DAY(NOW())DAY(DATE(YEAR(NO
W()),MONTH(NOW())-9,DAY(NOW())-(DAY(NOW()))))))

Then, put this formula in E1 and copy down:

=SUMPRODUCT(--($A$1:$A$1000=D1),--($A$1:$A$1000=$C$1),--($B$1:$B$1000=
D1),--($B$1:$B$1000=$C$1))

Hope this helps!

In article ,
(Mark) wrote:

(Mark) wrote in message
. com...

Thanks Frank

However have put calculations in and am getting '0' as a result.

The dates in column A are as follows (first date of absence period):

11/07/2003
15/02/2003
19/10/2003
10/05/2004
02/06/2004

The dates in column B are as follows (last date of absence period):

15/07/2003
24/02/2003
28/10/2003
14/05/2004
04/06/2004

Column C1 reads: 14/06/2004 (=now())
Column D1 reads: 01/06/2003 (using your calculation to get rolling
tweleve months)
Column E1 reads: 0 absence periods

Looking at columns A to B, those periods of absence that fall within
01/06/2003 and 14/06/2004 should be 4

Any thoughts?


Would also like to apply calculations to same data set to show in
cells:

Number of periods of absence in last three months
Number of periods of absence in last six months
Number of periods of absence in last nine months

Thanks again.
Mark

In article ,
(Mark) wrote:

Thanks Domenic - think I'm getting the hang of this now.

You're welcome, Mark!