i need some .xls help..

[rattlsnak]

ok, i have over 2000 people on this list, so i simply want to plug in a name, or seniority number and see where this person will be on the seniority list at any given year value i plug in .

if it takes three or more plug ins to do this, thats fine. (in other words, plug in number, get an answer, then use that answer to plug in somewhere to get another answer)

[legion]

You've exhausted my Excel skills. I'd use a database and some procedural code to solve that problem. For something so small I'd just use Access and VB.

[Don Plumley]

I did a quick and dirty one in five minutes. It's in your inbox.

Legion - email me and I'll send you the sheet. Basically, I create a named value called "offset" by adding n years to today's date. Calculate years of service by subtracting the date of hire from the offset. Then use an IF statement to ask if the age is 60 or less. If true, value is 0, if false, value is -999. Then add the years of service to the result of the IF statement. That way, if the pilot is over 60, their years of service goes to a big negative number, but pilots under 60 have the years of service as normal. Sort on this figure.

[Z-man]

It's quite simple.

Convert the mm/dd/yy into Julian date format - YYYYDDD Today is J-date: 2006073. Excel can do that for you: use the 'format cells - number - date ' option to do so. Pick the format: YYYYDDD.

So (#4) mike's DOH is: 1981022.

So, say cell A4 is DOH and cell B4 is today's date. (All of column B is the same - B1, B2, B3...etc. is today's date.) Senority is column C4. Forumla for cell C4is: =B4- A4.

Propegate that forumla across the whole spreadsheet, as in C1 = B1 - A1, C2 = B2 - A2...and so on. You can use the cut/paste function for this - it will automatically change the row number.

In Mike's case, cell C4 turns out to be (2006073 - 1981022) = 25051. Sort by Column C and voila - you have your employees ordererd by senority.

Note: you can use today's date, or the date one day after your most recent hire - senority order won't change, just the number will increase or decrease depending on the date you use in column C (I suggest you use today's date, and when you need to redo the spreadsheet, update it with the current date)

-Z.

[Don Plumley]

Z - it's not that hard. Let Excel do the date match, use variables so you don't have to propagate anything, use an IF function to time out the old pilots.

[Z-man]

Originally posted by Don Plumley
Z - it's not that hard. Let Excel do the date match, use variables so you don't have to propagate anything, use an IF function to time out the old pilots.

It's the variable that you propegate - it's a simple click/cut/paste. And Excel can automatically convert the date to Julian. Granted, Excel is smart enough to calculate date differences, but I like working with J-date better. (Programming background)

-Z

[Don Plumley]

Here, I'll post the formulas:

offset_date = cell where you enter the number of future years, could be zero, could be 5, etc.

offset is "=(TODAY()+(offset_date*365))"

service is "(offset-D8)/365" where D8 is the relative cell for date of hire

too_old is "=IF((offset-E8)/365<=60,0,-999)" where E8 is the relative cell for the date of birth

service sort is service + too_old

Sort on "service sort"

Unfortunately, it does not take leap years into account. I'll have to work on that...

[Don Plumley]

Will Julian dates help with the leap year problem?? (non-programming background)

[Z-man]

Originally posted by rattlsnak
ok, here is a sample,, (all the names have been changed!

Sen# Employee # Name D.O.H. Birthday

1 234 tom 7/28/1979 8/28/1949
2 453 jeff 4/27/1980 6/23/1947
3 456 tim 12/1/1980 1/5/1952
4 786 mike 1/22/1981 9/14/1954
5 3465 mark 1/21/1982 12/11/1952
6 478 chris 4/9/1982 10/22/1949
7 877 james 5/24/1982 11/14/1950
8 00898 steve 6/1/1982 5/30/1957

I have them all in seperate columns. (seniority in A, employee in B, name in C, etc) Date of hire revelance, equals seniority order, so that column is really not important, but as you can see number 6 will retire before a few before him.

Your senority #'s don't add up - how can Mark (#5) have a sen# of 3465 when chris' Sen # is 478, yet they were hired 4 months apart?!? I am assuming that DOB factors in somehow...

-Z.

[Z-man]

Originally posted by Don Plumley
Will Julian dates help with the leap year problem?? (non-programming background)

Relative to the other employees, yes - Jdate should work. You'll always have the proper order. A person hired in 1980 will always have a higher sen# than a person hired in 1981, despite 1980 being a leap year. (2006073 - 1980XXX will always be greater than 200673 - 1981XXX) And two people hired in 1980 will always have the appropriate order as well.

Note: you cannot use the "Z-man J-date result" to show anything but order of seniority. You can't really divide that number by anything to get a 'years of service' or anything else. To do that, you'd have to split apart the Jdate into years and days, and do calculations based on those two separate numbers.

-Z

[Don Plumley]

Originally posted by Z-man
Your senority #'s don't add up - how can Mark (#5) have a sen# of 3465 when chris' Sen # is 478, yet they were hired 4 months apart?!? I am assuming that DOB factors in somehow...

-Z.

There are two columns - the first single digit is the seniority, the second column is the employee #.

[Z-man]

Originally posted by Don Plumley
There are two columns - the first single digit is the seniority, the second column is the employee #.

Still doesn't add up right...
-Z

[Don Plumley]

No, Mark comes before Chris. Mark is #5, Chris is #6. The other figures are random employee numbers.

The DOB only figures into the time-out (over 60 factor).

[Z-man]

Originally posted by Don Plumley
No, Mark comes before Chris. Mark is #5, Chris is #6. The other figures are random employee numbers.

The DOB only figures into the time-out (over 60 factor).

D'oh. I got it now.

Actually, if you just sort the list by DOH, you'll have them all in seniority order!

-Z.

[Z-man]

Double post.

Man, I've having a bad day... already...
-Z.

[vash]

snake, what is your email? i will send you my simple one.

you will be able to rank either by number of years service or age. the data>sort>decending button is your friend

[Don Plumley]

I improved my quick and dirty version to include a control button that invokes the sorting macro. Simply enter the years in the future you wish to determine the seniority, and press the button "Rank".

For those interested, the spreadsheet is posted here.

[rattlsnak]

don, looks like you re getting there!!. Now i need a cell, that will add up the people. or better yet, i just sent it to you.

Thanks!

[vash]

very similar to the one i cranked out. nice, don!

[Don Plumley]

You should see the one I just cranked out....

Used the Seniority Number to find the person, then took advantage of the "Match" function to determine the rank more directly. Then had to figure out VB code to run a button on a different sheet.

Now to figure out how to count the valid pilots and tell someone if they are too old...

Edit: Got it. Did that with the CountIF function....