Excel formula
 

I am looking for a payroll formula. The formula will combine data from two
separate pay periods.
For each day worked, employee receives $65 not to exceed $1,300 in total
from two pays.
Example:
Payroll 1, employee works 12 days and receives $780.00 ($65 x 12)
Payroll 2, employee works 12 days, this works out to $780, but exceeds the
maximum $1300 payment allowed.
ie: The second pay needs a result of either the actual calculation if the
total of 2 pays is 1300 or less or a calculation of 1300 less 780, indicating
the actual payment allowed, ie: $520

Excel formula
 

arlene,

this should do the trick. do let me know if it works.

paycheck for second period = if(sum(P1,P2)<=1300, P2, 1300-P1)

P1 - payroll for period 1
P2 - payroll for period 2

regards,

"Arlene" wrote:

I am looking for a payroll formula. The formula will combine data from two
separate pay periods.
For each day worked, employee receives $65 not to exceed $1,300 in total
from two pays.
Example:
Payroll 1, employee works 12 days and receives $780.00 ($65 x 12)
Payroll 2, employee works 12 days, this works out to $780, but exceeds the
maximum $1300 payment allowed.
ie: The second pay needs a result of either the actual calculation if the
total of 2 pays is 1300 or less or a calculation of 1300 less 780, indicating
the actual payment allowed, ie: $520

Excel formula
 

This worked perfectly, thank you very much.

"Kiran" wrote:

arlene,

this should do the trick. do let me know if it works.

paycheck for second period = if(sum(P1,P2)<=1300, P2, 1300-P1)

P1 - payroll for period 1
P2 - payroll for period 2

regards,

"Arlene" wrote:

I am looking for a payroll formula. The formula will combine data from two
separate pay periods.
For each day worked, employee receives $65 not to exceed $1,300 in total
from two pays.
Example:
Payroll 1, employee works 12 days and receives $780.00 ($65 x 12)
Payroll 2, employee works 12 days, this works out to $780, but exceeds the
maximum $1300 payment allowed.
ie: The second pay needs a result of either the actual calculation if the
total of 2 pays is 1300 or less or a calculation of 1300 less 780, indicating
the actual payment allowed, ie: $520

Excel formula
 

Try this:

=IF(Pay1+Pay21300,1300-Pay1,Pay2)

--
Biff
Microsoft Excel MVP


"Arlene" wrote in message
...
I am looking for a payroll formula. The formula will combine data from two
separate pay periods.
For each day worked, employee receives $65 not to exceed $1,300 in total
from two pays.
Example:
Payroll 1, employee works 12 days and receives $780.00 ($65 x 12)
Payroll 2, employee works 12 days, this works out to $780, but exceeds the
maximum $1300 payment allowed.
ie: The second pay needs a result of either the actual calculation if the
total of 2 pays is 1300 or less or a calculation of 1300 less 780,
indicating
the actual payment allowed, ie: $520

Excel formula
 

If days worked are in D1 and D2, then:

P1: =max(1300,65*D1)

P2: =min(65*D2,1300-P1)


----- original message -----

"Arlene" wrote:
I am looking for a payroll formula. The formula will combine data from two
separate pay periods.
For each day worked, employee receives $65 not to exceed $1,300 in total
from two pays.
Example:
Payroll 1, employee works 12 days and receives $780.00 ($65 x 12)
Payroll 2, employee works 12 days, this works out to $780, but exceeds the
maximum $1300 payment allowed.
ie: The second pay needs a result of either the actual calculation if the
total of 2 pays is 1300 or less or a calculation of 1300 less 780,
indicating
the actual payment allowed, ie: $520

Excel formula
 

Errata....

"JoeU2004" wrote:
If days worked are in D1 and D2, then:
P1: =max(1300,65*D1)
P2: =min(65*D2,1300-P1)

Duh, P1 should be =min(1300,65*D1).


----- original message -----

"JoeU2004" wrote in message
...
If days worked are in D1 and D2, then:

P1: =max(1300,65*D1)

P2: =min(65*D2,1300-P1)


----- original message -----

"Arlene" wrote:
I am looking for a payroll formula. The formula will combine data from
two
separate pay periods.
For each day worked, employee receives $65 not to exceed $1,300 in total
from two pays.
Example:
Payroll 1, employee works 12 days and receives $780.00 ($65 x 12)
Payroll 2, employee works 12 days, this works out to $780, but exceeds
the
maximum $1300 payment allowed.
ie: The second pay needs a result of either the actual calculation if
the
total of 2 pays is 1300 or less or a calculation of 1300 less 780,
indicating
the actual payment allowed, ie: $520