Count dates falling in a certain range

[junoon]

Hi All,

I have a list of employee names & their joining dates.

i want to know which employees fall into which category i.e.

1] <90 days from today()
2] between 91-180 days from today()
3] >180 days

there are 3 cells in a row (on top right corner of data list ) labelled
:

<90 90-180 >180
[formula here] [formula here] [formula here]

Till now, i have created 4 columns:

1st column formula: today()-cell containing 1st date in list & dragged
it down to fill other cells.

2nd column formula: if(1st col<90, 1, 0) & dragged down to fill other
cells....

3rd column formula: if(and(1st col>=90,1st col=<180),2,0) & dragged
down.....

4th column formula: if(1st col>180,3,0) & dragged down to fill......


Then i have totalled the columns down using Countif function:

2nd col: Countif(2nd col_range,"1")

3rd col: Countif(3rd col_range,"2")

4th col: Countif(4th col_range,"3")

then i have copied the results into the 3 cells in the top right corner
& using the results i have made a pie Chart to show the ratio.....

I dont want to make extra columns as it does not look neat. What i want
is readymade formulas which i can directly enter into the 3 cells to
get the ratios automatically, but i have racked my brains & no luck
yet!

I have even tried using Advanced filter but dont know how to get it to
work....

Could any brainy people out there help me please????

Rgds,

Junoon

 

[daddylonglegs]

If your dates are in column A this formula, say in cell C1, will giv
you the number joining within the last 90 days

=COUNTIF(A:A,">"&TODAY()-91)

then this formula for the next 90 days

=COUNTIF(A:A,">"&TODAY()-181)-C1

then for the rest

=COUNTIF(A:A,"<"&TODAY()-180

 

[daddylonglegs]

.......If you want percentages then divide the above totals by the
number of dates in column A which can be determined by the formula

=COUNT(A:A)

 

[Pete_UK]

It would be a bit easier if you had specified the actual column that
the joining dates were in, so I have assumed the dates occupy D2 to
D100 - adjust as necessary. Try these array formulae*:

=SUM(IF(TODAY()-D2100<90,1,0))

=SUM(IF((TODAY()-D2100>=90)*(TODAY()-D2100<=180),1,0))

=SUM(IF(TODAY()-D2100>180,1,0))

*As these are array formulae, once you have typed them in (or if you
subsequently edit them) you must use CTRL-SHIFT-ENTER rather than just
ENTER. If you do this correctly, Excel will wrap curly braces { }
around the formula - you must not type these yourself.

This should give you what you want.

Hope this helps.

Pete

 

[junoon]

Hi,

The Names are in Col A, Dates are in Col B, then few blank rows, then
the 3 cells in which i want the formulas (say in columns G, H I)...

Hope this helps!

 

[junoon]

Well, i am entering your formulas , also in form of arrays but still i
donot get the same results which i get when i do it manually......

your results:

<90 >=90 & <=180 >180
0 0 1

whereas my manual count results are:

<90 >=90 & <=180 >180
126 64 40 = total is 230 dates
in the list....

 

[junoon]

OOPS.......Sorry friends,

Both of your formulas work fine......

Was taking Today() to calculate by mistake, whereas the date was
31-Aug-05...
replaced Today() with DATEVALUE("31-08-2005") to calculate the
results.....

Thanks & Take care!

Cheers.