I am trying to create a budget in excel that will assign people's salaries to certain projects based on the time spent on them. It's simple enought except these projects can run a couple years and people will have a (assumed 4%) yearly salary increase. Complicating it further is that the increases are based on anniversary date and not a uniform date across the company. I have been going through the HR records by hand to determine the anniversary date to use but it is very time consuming as I have to redo this everytime I get a new project. Is there a way for me to dump people's salary informaton into a spreadsheet one time - say Column A=Employee Name; Column B=Month; Column C=Year and Column D=Monthly Salary (I don't mind doing that part by hand). And then using that data to determine what the average salary rate is for a given time period (Ideally I would just want to enter an employees name, project start and end date, and be given the average monthly salary). Anniversary date is always assumed to be the first day of the month.

Is there a way for me to dump people's salary informaton into a spreadsheet one time Probably. What have you done so far? Examples always help. MIKE http://www.skeptic.com/

A process, not a solution: 1 - Determine the number of months between the Project Start Date and the employee's next anniversary date. There are multiple ways to do this, starting with this info: http://support.microsoft.com/kb/214134 and moving on to a Google search where you'll find DATEDIF methods as well as many others. The salary during this period will be the employee's current salary. 2 - Determine the number (A) of anniversay dates that the project will span. Use A - 1 to determine how many 4% increases the employee will get that will cover a full 12 months. 3 - Determine the number of months between the Project End Date and the previous anniversary date. The salary during this period will be 4% more than the last full year's salary. 4 - Now ask yourself: "What do I mean by average monthly salary?" Do I mean the highest salary attained averaged over the entire project? Do I mean the average of the ending salary and the beginning salary? Do I mean the sum of the total salary for each time period, based on the number of months at that specific salary, averaged over the entire project? Each of those "averages" is going to give you a different answer, so you need to know what you are looking for before you start constructing formulae.