Calculating absence periods in a rolling 12 months in excel

[Mark]

Help!

Tring to put a spreadsheet together that calculates how many absence
periods fall in a rolling 12 months. Have created a listing of
absences for an individual for example, with columns for first date of
absence and last date of absence and number of working days lost. Now
wish to somehow calculate the total number of absence periods (not
days) that fall in the last 12 months in one cell.

12 months prior to todays date (=now()). !

Any help would be appreciated.

Many thanks
Mark

 

[Frank Kabel]

Hi
if the starting date is in column A and the ending date in column B try
the following formulas
C1:
=NOW()
D1:
=DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())-(DAY(NOW())<>DAY(DATE(YEAR(
NOW())-1,MONTH(NOW()),DAY(NOW())-(DAY(NOW())))

E1:
=SUMPRODUCT(--(A1:A1000<=D1),--(A1:A1000>=C1))+SUMPRODUCT(--(A1:A1000<C
1),--(B1:B1000>=C1))

 

[tghcogo]

Hi

This might be too obvious, have you tried COUNT OR COUNTIF functions

TGHCOG

 

[Mark]

(e-mail address removed) (Mark) wrote in message
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

 

[Domenic]

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!

 

[Mark]

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

 

[Domenic]

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

You're welcome, Mark!