Re: Calculating absence periods in a rolling 12 months in excel

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Domenic (domenic22_at_sympatico.ca)
Date: 06/14/04 

Date: Mon, 14 Jun 2004 08:25:36 -0400

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 <af64d2e4.0406140120.6a1600b@posting.google.com>,
 ryanm3@wyeth.com (Mark) wrote:

> ryanm3@wyeth.com (Mark) wrote in message
> news:<af64d2e4.0406100718.4b4cfbc2@posting.google.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

Relevant Pages

  • Re: Slightly OT: Who cares if you listen?
    ...  I find the apparent absence of a narrative ... line which one can follow, the lack of apparent repeats, the absence of ... Mark & Steven Bornfeld DDShttp://www.dentaltwins.com ...
    (rec.music.classical.guitar)
  • Re: Where have all my sci.optics friends migrated?
    ... had a pretty active group, now that I return after long absence it has ... Has everyone gone to some forum ... Welcome back, Mark! ...
    (sci.optics)
  • Re: Calculating absence periods in a rolling 12 months in excel
    ... Frank Kabel ... Mark wrote:> 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. ...
    (microsoft.public.excel.misc)
  • Re: Calculating absence periods in a rolling 12 months in excel
    ... Thanks Domenic - think I'm getting the hang of this now. ... > Hi Mark, ... >> Looking at columns A to B, those periods of absence that fall within ... >> Would also like to apply calculations to same data set to show in ...
    (microsoft.public.excel.misc)
  • Re: Calculating absence periods in a rolling 12 months in excel
    ... 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): ...
    (microsoft.public.excel.misc)