Maybe those cells that look like dates aren't all dates. They could be plain
old text entries--and so the don't do what you hope.

If you type:
=counta(date)
and
=count(date)
in different cells, do you get the same number returned.

=counta() will count the cells with any old entry -- text or dates.

=count() will count the cells that contain numbers -- that includes dates.

"F.G." wrote:

On Jun 6, 1:05 pm, Dave Peterson wrote:
What does it do wrong?

Are you using range names (Date and name)? Do they refer to the same size
range?

And what happens on the 18th of January. Do you need = or <= in one of those
factors?





"F.G." wrote:

Hello everyone,

This might be simple but I'm stuck:

Name Date
Nam1 02/18/07
Nam2 02/18/09
Nam3 03/14/10
Name4 05/06/2006

Cut off date is 01/18/2006 "Date(2008,01,18)"

Now I need to find how many are +1 year, +2 year and so on until +5
years
The formula I've trying to use is
For +1 year
=SUMPRODUCT((Name<"")*(DateDATE(2006+1,1,18)*(Da te<DATE(2006+2,1,18))))
For +2 year:
=SUMPRODUCT((Name<"")*(DateDATE(2006+2,1,18)*(Da te<DATE(2006+3,1,18))))

And so on until 5th

But for some reason it is not working ... agrrrRRr!!!

What I'm doing wrong?

In other words from a table I have to find how many people will reach
or reached yearly milestones for 5 years starting 01/18/2006. It is
yearly not by exact date. So if reaches 1 year after 01/18/2007 but
before 01/18/2008 it will still count as 1 year.

I hope I was clear
Thanks

FG

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks Shane for your for going above and explaining the calculations.

Dave,

The problem is, or let me explain it with example:
3 people reached the "Longevity" somewhere in 2008. These records are
not counted in 2006, 2007 nor 2010 but for some reason with this
formula they're being counted on 2008 correctly but in 2009 as well
and I don't know why ƒ¼

Now to make things more zesty I have to go back and redo everything
with June - July instead regular calendar year.

To put this in perspective so you understand what I'm looking for:
I have to project that I'm in Jan, 18th 2006 and I need to find out
the number of personnel moving to the next 5year step increase based
on time from that day (maximum would be Jan 18th 2010 because it is a
5 year period). Example if someone is hired in June 2005 he will reach
the next step in June 2010. This is to be used for budget negotiation
with union.

I think I bothered you enough; I will just go and do it the old
fashion way - counting one by one.

It was already driving me crazy the way it was, now they want June -
July, well it will take some days I think.

Thanks you for your help
FG

--

Dave Peterson