i need some .xls help..
 

OK, who is good at Excel? I'm usually pretty good at it, but i have a project that has me stumped..

I was a finance major in college. Had to do some pretty fancy stuff in Excel. I can try to help.

I'll stand in line behind legion. Have made many a huge Excel model in my time....more than I care to remember. Happy to help if I can.

legion, i sent you a PM with my email address...

Sorry, at 10:00 last night the wife demanded I go to bed. ;)

damn wife OR woohooo!

If I were stranded on a deserted island and could choose only one tool to have with me, it would be Microsoft Excel. :D

I know a bit about the product - work with it everyday. What do you need to know?

-Z.

Embarassed to admit my group of engineering analysts come to me with Excel questions. :(

"Yes, how can I help you?"

That's the thing. Excel has a lot of built-in functions for a lot of different disciplines. I know finance stuff, but not the engineering stuff.

What discipline are you looking for help with?

basically what i am trying to do is determine where "you" will be 1,5,10 etc,. years from now on a seniority list. I have a listing of @2,000 people with seniority dates and birthday dates. I need a function that will figure that out. i.e.- an employee who is number 500 on the list wants to know what his seniority number will be in five years. I have seniority numbers, names, and birthdays (using 60 as a retirement age for this project) all in seperate columns. So the function will have to determine where "he" is on the list, and then determine how many people above on the list will retire in the number of years he plugs in, and then give him a new seniorty number.. ?

I also have Access, if it can be done easier on that, but would rather Excel.

What is the forumula for senority? Is it something like age + years of service subtracted from a number? (I think that's how they do it at my workplace). IF so, supply the numbers here.

-Z.

Seniority is strictly date of hire, nothing else. So someone who is 30 can be senior to someone who is 50 who just started with the company.
Thats the formula section i cant figure out because half of the people who will retire, are junior to the people who are 40ish.
Can it send it to you by email?

i think i got it.
i made a dummy one, that is very simple. i can email it to you.

basically
name, hired date, dob, seniority, age.

then i had a cell floating on the side with todays date. subtract hired date from todays date, and do the same for age. those cells you have to format to a number and round it to the nearest whole number. that number will be in days. divide by 365 to get years if you want. then use the data button on the toolbar, and sort by decending order, choose the seniority column as your criteria. it will flip everyone from newest hire to oldest hire. and the age will not be affected.

send it..

When you say age isnt affected, im not sure i know what you mean, because age is the determing factor for the retirement.

If I have 10 people on the list, person number 7 could retire before person number 1 because of age, regardless of hire date/seniority.

repost.. deleted

I'm thinking this one over, and I'm a code monkey for a living, and I can't think of a good Excel solution. I'm assuming that each employee is by row. The thing is, the calculation for one row is dependent on all other rows.

I can send part of it to anyone who wants to try. Let me know..

I was noodling on this for a few minutes. You could set up one sheet with the data and validation calculations. Then another sheet is the data entry/query screen that does a named range lookup on the data set.

Then on the query screen, you have today's rank. The user enter's "X" years from today, this gets added to "today" and the data set is resorted, with those that are too old (time out with age > Y) having a manual count figure that forces them to the bottom of the sort.

Part of me thinks it would be easier to write a quick VB program that does the lookup on an Excel spreadsheet. But the stubborn part of me thinks it can be done in Excel. Think about a giant set of nested IF statements...

Marc - it depends on how automated you want this. If you can do the sort yourself, then it does not look too hard. Or I've totally underestimated this...

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.

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)

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.

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.

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.

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

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

;)

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

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.

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

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

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

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.

Double post.

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

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

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.

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!

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

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