Dates Milestones

[F.G.]

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<>"")*(Date>DATE(2006+1,1,18)*(Date<DATE(2006+2,1,18))))
For +2 year:
=SUMPRODUCT((Name<>"")*(Date>DATE(2006+2,1,18)*(Date<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

 

[ShaneDevenshire]

Hi,

Here is one interesting approach:

Suppose your dates are in the range B2:B24, Names in column A. In the range
D1:F7 set up the following:

Cum Count
6/6/2008 21 2
6/6/2007 19 2
6/6/2006 17 3
6/6/2005 14 1
6/6/2004 13 4
6/6/2003 9 9


D2 contains the formula =TODAY()
In D3 enter the formula =EDATE(D2,-12)
Copy this down to D7.

In E2 enter the formula =FREQUENCY($B$2:$B$24,D2)
Copy this down to E7. This will be a cumulative total of all persons who
were hired later than the next date down on the list.

Highlight hte range F2:F7 and type the following formula but don't press Enter
=FREQUENCY($B$2:$B$24,D27)
Press Shift Ctrl Enter.
This column counts the number of people 1, 2, 3, 4, 5 and more years with
the company.

I know you didn't ask for both of these but its educational.

 

[Dave Peterson]

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

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?

















--

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]

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

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.













--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

The dates are OK, I've entered them.
The formula is not returning errors due to formating but it is not
calculating what i want.
Thanks
FG

 

[Dave Peterson]

I'd try making a test worksheet--but with lots less data. Maybe you'll see
where you went wrong.

But I don't have another guess why the formulas aren't returning what you want.