Calculating overlapping time periods
 

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 a

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


Project Dates a

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?

Thank a million!

Calculating overlapping time periods
 

Arlette:

Try these in the following cells. It works for the whole months. It won't
work for split months and oyu need to decide on days etc for that policy.

[A2]= 3/1/2007
[B2]= 2/29/2008 <<<< Leap year
[C2]=100000
[A3]= 3/1/2008
[B3]= 2/28/2009
[C3]=105000


[A6]=Project Dates A

[A8]=7/1/2007
[B8]= 6/30/2008

[A10]=Rates
[A11]= =A8
[B11]= =MIN(B8,B2)
[C11]= =DATEDIF(A11,B11,"m")+1
[D11]= =ROUND(C2*C11/12,2)
[A12]= =IF(B11<=B3,B11+1,0)
[A12]= =MIN(B8,B3)
[A12]= =DATEDIF(A12,B12,"m")+1
[A12]= =ROUND(C12/12*C3,2)
[D13]= =SUM(D11:D12)

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Arlette" wrote:

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 a

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


Project Dates a

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?

Thank a million!