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):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Pay Effective Date[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]$1000.00[/TD]
[/TR]
[TR]
[TD]6/1/2013[/TD]
[TD]$1500.00[/TD]
[/TR]
[TR]
[TD]6/15/2013[/TD]
[TD]$2000.00[/TD]
[/TR]
[TR]
[TD]6/18/2013[/TD]
[TD]$2500.00[/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="class: outer_border, width: 100, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR="class: outer_border"]
[TD="width: 83, align: center"]01/01/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR="class: outer_border"]
[TD="width: 83, align: center"]01/01/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1000[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR="class: outer_border"]
[TD="width: 83, align: center"]06/01/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1500[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR="class: outer_border"]
[TD="width: 83, align: center"]15/06/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]2000[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR="class: outer_border"]
[TD="width: 83, align: center"]18/06/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]2500[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR="class: outer_border"]
[TD="width: 83, align: center"]30/06/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]2500[/TD]
[/TR]
</tbody>[/TABLE]

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