Calculation of monthly payments per employee
 

I am looking for a formula that gives me the amount to be paid at the end of
each month per employee knowing that:

- If the month starts on 16th or ends on 15th, half the allowance should be
paid (1/2)
- The first month, the travel costs inbound have to be added to the allowance
- Travel costs outbound should be paid in the month that is inserted in
column J
- Contracts can be extended (the end dates are inserted in columns C, D, E)
the calculation should also include these dates if there are any

Column A: Start date contract (16/03/2010)
Column B: End date contract (30/06/2010)
Column C: End date extension 1 (15/09/2010)
Column D: End date extension 2 (empty)
Column E: End date extension 3 (empty)
Column F: Monthly allowance (600‚¬)
Column G: Travel cost inbound (500‚¬)
Column H: Travel cost outbound (450‚¬)
Column I: Month payment outbound travel (October 2010)

SOLUTION
Column J: March 2010 (800‚¬)
Column K: April 2010 (600‚¬)
Column L: May 2010 (600‚¬)
Column M: June 2010 (600‚¬)
Column N: July 2010 (600‚¬)
Column O: August 2010 (600‚¬)
Column P: September 2010 (300‚¬)
Column Q: October 2010 (450‚¬)

I hope someone can help.

Thank you,

Deborah

Calculation of monthly payments per employee
 

Deborah,
The formula I created depends on having the Analysis Toolkit add-in
activated because it extensively uses the EoMonth() function.
To verify that the Analysis Toolkit is active, from the top menu, go to
TOOLS ADD-INS...
When the Add-ins window appears, make sure the Analysis ToolPak is
checked. Hit OK to exit the Add-in window. This will allow the EoMonth()
function to work. [EoMonth stands for End-of-Month].

I created an if statement that works for the example you gave.
In English (sort of) it says...
- If the current month = Start Month then
if the Start Day is 15 then
1/2 Allowance + Inbound cost
otherwise
100% Allowance + Inbound cost
ELSE
- If the current month = Outbound Travel Month then
Outbound cost
ELSE
- If the current month = End of Contract Month then
if the End Day is < 16 then
1/2 Allowance
otherwise
100% Allowance
ELSE
- If the current month < End of Contract Month AND
the current month Start Month then
Allowance

Assuming that DATES appear in Row 1 starting in column J (ie: Mar-2010,
Apr-2010, etc)...
Assuming the data and formulas start in row 2...

Cell J2 should be:
=IF(EOMONTH(J$1,0)=EOMONTH($A2,0),IF(DAY($A2)15,R OUND($F2/2,2)+$G2,$F2+$G2),IF(EOMONTH(J$1,0)=EOMONTH($I2,0) ,$H2,IF(EOMONTH(J$1,0)=EOMONTH(MAX($B2:$E2),0),IF( DAY(MAX($B2:$E2))<16,ROUND($F2/2,2),$F2),IF(AND(EOMONTH(J$1,0)<EOMONTH(MAX($B2:$E 2),0),EOMONTH(J$1,0)EOMONTH($A2,0)),$F2,0))))

Copy this formula across as anm columns as desired.
It will work as long as...
- Columns A thru I are as described in your example
- Columns J thru ??? have a DATE in row 1

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Deborah" wrote:

I am looking for a formula that gives me the amount to be paid at the end of
each month per employee knowing that:

- If the month starts on 16th or ends on 15th, half the allowance should be
paid (1/2)
- The first month, the travel costs inbound have to be added to the allowance
- Travel costs outbound should be paid in the month that is inserted in
column J
- Contracts can be extended (the end dates are inserted in columns C, D, E)
the calculation should also include these dates if there are any

Column A: Start date contract (16/03/2010)
Column B: End date contract (30/06/2010)
Column C: End date extension 1 (15/09/2010)
Column D: End date extension 2 (empty)
Column E: End date extension 3 (empty)
Column F: Monthly allowance (600‚¬)
Column G: Travel cost inbound (500‚¬)
Column H: Travel cost outbound (450‚¬)
Column I: Month payment outbound travel (October 2010)

SOLUTION
Column J: March 2010 (800‚¬)
Column K: April 2010 (600‚¬)
Column L: May 2010 (600‚¬)
Column M: June 2010 (600‚¬)
Column N: July 2010 (600‚¬)
Column O: August 2010 (600‚¬)
Column P: September 2010 (300‚¬)
Column Q: October 2010 (450‚¬)

I hope someone can help.

Thank you,

Deborah

Calculation of monthly payments per employee
 

FYI,
If you're using Excel 2007, I understand that the Analysis ToolPak has been
incorporated into the main functions and does NOT need to be activated.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Deborah" wrote:

I am looking for a formula that gives me the amount to be paid at the end of
each month per employee knowing that:

- If the month starts on 16th or ends on 15th, half the allowance should be
paid (1/2)
- The first month, the travel costs inbound have to be added to the allowance
- Travel costs outbound should be paid in the month that is inserted in
column J
- Contracts can be extended (the end dates are inserted in columns C, D, E)
the calculation should also include these dates if there are any

Column A: Start date contract (16/03/2010)
Column B: End date contract (30/06/2010)
Column C: End date extension 1 (15/09/2010)
Column D: End date extension 2 (empty)
Column E: End date extension 3 (empty)
Column F: Monthly allowance (600‚¬)
Column G: Travel cost inbound (500‚¬)
Column H: Travel cost outbound (450‚¬)
Column I: Month payment outbound travel (October 2010)

SOLUTION
Column J: March 2010 (800‚¬)
Column K: April 2010 (600‚¬)
Column L: May 2010 (600‚¬)
Column M: June 2010 (600‚¬)
Column N: July 2010 (600‚¬)
Column O: August 2010 (600‚¬)
Column P: September 2010 (300‚¬)
Column Q: October 2010 (450‚¬)

I hope someone can help.

Thank you,

Deborah