Payroll calculation
 

Hi all!,
Need to compile a single formula that will compute payroll fractions for
crew on board ship who are paid on a calendar month basis. For eaxample

Start date End Date Ttl Month Ttl Pay Monthly
Ttl Pay
Days Days Pay

15-Feb-06 28-Feb-06 28.00 14.00 1,000.00 500.00
01-Mar-06 31-Mar-06 31.00 31.00 1,000.00 1,000.00
01-Apr-06 30-Apr-06 30.00 30.00 1,000.00 1,000.00
01-May-06 31-May-06 31.00 31.00 1,000.00 1,000.00
01-Jun-06 30-Jun-06 30.00 30.00 1,000.00 1,000.00
01-Jul-06 17-Jul-06 31.00 17.00 1,000.00 548.39
Ttl Pay days 153.00 5,048.39

15 Feb 06 is the date they join the ship and 17 Jul 06 is the date they
leave the ship. The user will enter a single pay period of say From 15 Feb 06
to 17 July 06 and the calendar month pay of say USD 1,000 and get the result
of 5,048.39 for the whole pay period.

Many thanks in advance/sgl

Payroll calculation
 

If the start date, end date and monthly rate are in B1, b2 and b3, then
=ROUND(B3*((YEAR(B2)*12+MONTH(B2)-YEAR(B1)*12-MONTH(B1))-(DAY(B1)-1)/((DATE(YEAR(B1),MONTH(B1)+1,0))-(B1-DAY(B1)))+DAY(B2)/((DATE(YEAR(B2),MONTH(B2)+1,0))-(B2-DAY(B2)))),2)
should calculate the earnings per your description.
The approach is to pay full months from hire date up to (not including) the
term date. Then add the proportional part of the term month and subtract the
proportional part of the hire month. The calculations of the form b1-day(b1)
determine the final date of the prior month; date(year(b1),month(b1)+1,0)
calculates the final date of this month. The difference is the number of
days in this month.

"sgl" wrote:

Hi all!,
Need to compile a single formula that will compute payroll fractions for
crew on board ship who are paid on a calendar month basis. For eaxample

Start date End Date Ttl Month Ttl Pay Monthly
Ttl Pay
Days Days Pay

15-Feb-06 28-Feb-06 28.00 14.00 1,000.00 500.00
01-Mar-06 31-Mar-06 31.00 31.00 1,000.00 1,000.00
01-Apr-06 30-Apr-06 30.00 30.00 1,000.00 1,000.00
01-May-06 31-May-06 31.00 31.00 1,000.00 1,000.00
01-Jun-06 30-Jun-06 30.00 30.00 1,000.00 1,000.00
01-Jul-06 17-Jul-06 31.00 17.00 1,000.00 548.39
Ttl Pay days 153.00 5,048.39

15 Feb 06 is the date they join the ship and 17 Jul 06 is the date they
leave the ship. The user will enter a single pay period of say From 15 Feb 06
to 17 July 06 and the calendar month pay of say USD 1,000 and get the result
of 5,048.39 for the whole pay period.

Many thanks in advance/sgl

Payroll calculation
 

Absolutely brilliant!! Could't have worked this puzzler out myself in any
form or way .. Thanks a lot/sgl

"bpeltzer" wrote:

If the start date, end date and monthly rate are in B1, b2 and b3, then
=ROUND(B3*((YEAR(B2)*12+MONTH(B2)-YEAR(B1)*12-MONTH(B1))-(DAY(B1)-1)/((DATE(YEAR(B1),MONTH(B1)+1,0))-(B1-DAY(B1)))+DAY(B2)/((DATE(YEAR(B2),MONTH(B2)+1,0))-(B2-DAY(B2)))),2)
should calculate the earnings per your description.
The approach is to pay full months from hire date up to (not including) the
term date. Then add the proportional part of the term month and subtract the
proportional part of the hire month. The calculations of the form b1-day(b1)
determine the final date of the prior month; date(year(b1),month(b1)+1,0)
calculates the final date of this month. The difference is the number of
days in this month.

"sgl" wrote:

Hi all!,
Need to compile a single formula that will compute payroll fractions for
crew on board ship who are paid on a calendar month basis. For eaxample

Start date End Date Ttl Month Ttl Pay Monthly
Ttl Pay
Days Days Pay

15-Feb-06 28-Feb-06 28.00 14.00 1,000.00 500.00
01-Mar-06 31-Mar-06 31.00 31.00 1,000.00 1,000.00
01-Apr-06 30-Apr-06 30.00 30.00 1,000.00 1,000.00
01-May-06 31-May-06 31.00 31.00 1,000.00 1,000.00
01-Jun-06 30-Jun-06 30.00 30.00 1,000.00 1,000.00
01-Jul-06 17-Jul-06 31.00 17.00 1,000.00 548.39
Ttl Pay days 153.00 5,048.39

15 Feb 06 is the date they join the ship and 17 Jul 06 is the date they
leave the ship. The user will enter a single pay period of say From 15 Feb 06
to 17 July 06 and the calendar month pay of say USD 1,000 and get the result
of 5,048.39 for the whole pay period.

Many thanks in advance/sgl