Calculate monthly pay from table, with pay changes

[skiumah]

Hello all, first post.

I would like to calculate monthly gross pay from a lookup table which may contain one or more changes in pay during a given month. The formula should not assume a given number of changes, or the month during which they occur. Here is an example pay table I would like to use (with fictitious amounts):

Pay Effective Date	Amount
1/1/2013	$1000.00
6/1/2013	$1500.00
6/15/2013	$2000.00
6/18/2013	$2500.00

<tbody>
</tbody>

Pay changes may occur in the middle of the month as a result of years of service, promotion, cost of living adjustments, etc.

For this example, monthly gross pay for 2013 would be:

January 2013 through May 2013: $1000.00
June 2013: (14/30)*1500 + (3/30)*2000 + (13/30)*2500
July 2013 through December 2013: $2500.00

The above assumes a 30-day month. I am not sure if my payroll uses a 30 divisor for every month or if they calculate the number of days "correctly"--everywhere I can find such an example so far, it has occurred in June.

I am generally familiar with the vlookup command, but I am not sure how (or if) to apply it in this type of situation, one in which the month of concern is June 2013 and there are a non-predefined number of changes occurring during that month (i.e., there are years in which pay does not change on June 18, and I would prefer to have no entry for June 18 for those years).

In a subsequent step I am able to use vlookup to calculate the correct federal income tax withholding, as long as monthly gross income is calculated with the formulas above. But I cannot think of a formula that does this based solely on a month-year input and the sample "Pay Effective Date" table.

I appreciate any advice. Thanks!

 

[skiumah]

Updates:

- Payroll does indeed use a standard 30-day work month as far as I can tell from some 31-day month examples (I couldn't find any examples in February, though), so I do not need to dynamically calculate the number of days in a given month.

- This may be self-evident from the example, but =VLOOKUP(inputDate,array,2) returns the correct monthly Amount value unless there are one or more pay changes in the middle of a given month. I am trying to think of a way to set up an INDEX/MATCH structure inside a conditional statement, in order to capture these instances and correctly calculate the x/30 amounts, but perhaps there is a more elegant way to do this.

 

[iyyi]

	A	B
1	01/01/2013 <tbody> </tbody>
2	01/01/2013 <tbody> </tbody>	1000
3	06/01/2013 <tbody> </tbody>	1500
4	15/06/2013 <tbody> </tbody>	2000
5	18/06/2013 <tbody> </tbody>	2500
6	30/06/2013 <tbody> </tbody>	2500

<tbody>
</tbody>

If you your data arranged like this the next formula will give you the total salary from January to June:
=SUMPRODUCT((A2:A6-A1:A5)*((B1:B5)/30))

 

[iyyi]

Change A6 to 01/01/2014 to get the full year

 

[skiumah]

Thanks, that gives me some ideas to work with, but I am trying to calculate the total for each individual month separately. So for June I would have to incorporate a conditional statement somehow, without "hard-coding" which cells are added.

 

[iyyi]

Thanks, that gives me some ideas to work with, but I am trying to calculate the total for each individual month separately. So for June I would have to incorporate a conditional statement somehow, without "hard-coding" which cells are added.

If you have your dates and amounts in A2:B5, and the start date of the month in B6 then you can write in C1 the formula:
=INDEX(B$2:B$5,MATCH(B$7+ROW()-1,A$2:A$5))/30
Drag it 30 rows down and you will have the salary for each day of the month. sum it and you get the total salary for the month