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 col180,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

If your dates are in column A this formula, say in cell C1, will give
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
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=528784

.......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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=528784

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 a

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

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.

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()-D2:D100<90,1,0))

=SUM(IF((TODAY()-D2:D100=90)*(TODAY()-D2:D100<=180),1,0))

=SUM(IF(TODAY()-D2:D100180,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

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!




Pete_UK wrote:
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()-D2:D100<90,1,0))

=SUM(IF((TODAY()-D2:D100=90)*(TODAY()-D2:D100<=180),1,0))

=SUM(IF(TODAY()-D2:D100180,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