Overlapping time calculations and automatic time updates

[Arlette]

I would so much appreciate any help with Microsoft Excel in having the
software automatically calculate the number of time periods to apply
for salary cost purposes.


Here is the issue:


Employee Y

Annual Merit increase dates are:

03/01/2007 - 02/28/2008 $100,000
03/01/2008 - 02/28/2009 $105,000


Project Dates are:

07/01/2007 - 6/30/2008


For this project period (07/01/2007 - 6/30/2008), Employee Y's salary
would be

8 months of $100,000 = $66,667
4 months of $105,000 = $35,000
Total $101,667

Have you any idea which formula we could use to calculate this? We
would also like to include a formula that will change the dates
automatically when necessary?

Thank a million!

 

[peter]

Hi,
See if this helps...
=ROUND((firstyr_end-proj_start)/30,0) should return 66,666.67
=ROUND((proj_end-secondyr_start)/30,0) should return 35,000
where...
firstyr_end=2/28/08
secondyr_start=3/1/08
proj_start=7/107
proj_end=6/30/08

Hope this helps,

peter