Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates Milestones
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates Milestones
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,D2:D7) 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. -- Cheers, Shane Devenshire Microsoft Excel MVP "F.G." wrote: Hello everyone, This might be simple but Im 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 Ive 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 Im 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates Milestones
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates Milestones
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates Milestones
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates Milestones
On Jun 6, 3:06*pm, Dave Peterson wrote:
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- 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates Milestones
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. "F.G." wrote: <<snipped 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) | |||
Indicating Milestones on a Line Chart | Charts and Charting in Excel | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
milestones in Excel 2000 | Excel Discussion (Misc queries) |