# Date Ranges and Monthly Salary Calculation

#### TheSage1

##### New Member
I am looking for some insight into how to best approach the following:

The below table is sample data derived from an access query exported to an excel spreadsheet. (Note: The months columns are not from the access query, only added in the excel spreadsheet.) It contains selected employees with their respective employee id, name, pay group, position number, salary, and the effective dates of that particular position and salary associated with it. Unfortunately, I do not have access to the actual pay data, so part of my process is to estimate year to date salaries based on any changes in their appointments and salaries throughout the year. This would include new employees, and those that may have terminated during the year. This process is done on a monthly basis.

There are in any given month approximately 200 of these staff, so dragging formulas and updating them individually on separate tabs is time consuming and tedious. What I am attempting to do is to come up with a formula that will do the salary calculations estimates for each month. How the salaries are computed creates some of the complexity. If a given appointment and salary is in place for a full month, the calculated salary for that month is just the appointment salary divided by 12. However, if it is a partial month workdays must be computed.

In the example table below are entered the desired outcomes for monthly salary. In this example, a salary increase took place effective January 8 so I now must calculate the partial months. The estimated monthly salary for this employee would be \$75,000/12*(5/22) workdays at the \$75,000 rate plus \$75,179/12*(17/22) workdays at the \$75,179 rate. I have also included a sample of the calendar table for January that shows the workdays. Of course each month has a different number of workdays so I have named ranges for each month.

 EMPLID NAME PAY GROUP POS NBR APPT SALARY EFFDT THRU DATE JUL AUG SEP OCT NOV DEC JAN FEB 1234567 DOE, JOHN A 00000001 75,000 7/1/2016 1/7/2017 6,250 6,250 6,250 6,250 6,250 6,250 1,420 0 1234567 DOE, JOHN A 00000001 75,179 1/8/2017 2/28/2017 0 0 0 0 0 0 4,841 6,265 1234567 DOE, JOHN B 00000001 55,000 7/1/2016 1/7/2017 4,583 4,583 4,583 4,583 4,583 4,583 1,042 0 1234567 DOE, JOHN B 00000001 55,235 1/8/2017 2/28/2017 0 0 0 0 0 0 3,357 4,603

<tbody>
</tbody>

The solution must be able to identify 1) If any of the date range falls within a month. 2) If the date range does fall within a month then determine if it’s a full month or a partial month. If dates not within that month, the salary value will be 0. 3) If a full month divide salary by 12. 4) If a partial month, calculate the workdays.

Hope this scenario isn’t too convoluted. I have tried various combinations of VLOOKUPS, COUNTIFS, and Nested IFS, but I’m not getting consistent correct results so far. Any general direction or “out of the box” thinking is appreciated!

WORK DAY CALENDAR FOR JANUARY:
 DAY DATE WORK DAYS COMPLETE WORK DAYS REMAINING TOTAL WORK DAYS IN MONTH MONDAY 1/2/2017 1 21 22 TUESDAY 1/3/2017 2 20 22 WEDNESDAY 1/4/2017 3 19 22 THURSDAY 1/5/2017 4 18 22 FRIDAY 1/6/2017 5 17 22 MONDAY 1/9/2017 6 16 22 TUESDAY 1/10/2017 7 15 22 WEDNESDAY 1/11/2017 8 14 22 THURSDAY 1/12/2017 9 13 22 FRIDAY 1/13/2017 10 12 22 MONDAY 1/16/2017 11 11 22 TUESDAY 1/17/2017 12 10 22 WEDNESDAY 1/18/2017 13 9 22 THURSDAY 1/19/2017 14 8 22 FRIDAY 1/20/2017 15 7 22 MONDAY 1/23/2017 16 6 22 TUESDAY 1/24/2017 17 5 22 WEDNESDAY 1/25/2017 18 4 22 THURSDAY 1/26/2017 19 3 22 FRIDAY 1/27/2017 20 2 22 MONDAY 1/30/2017 21 1 22 TUESDAY 1/31/2017 22 0 22

<tbody>
</tbody>

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### jorismoerings

##### Well-known Member
Hi and welcome to MrExcel,

Take a look at this and see if this helps.
Assuming the 3357 on the last row of your example is a typo.

Last edited:

#### TheSage1

##### New Member
Hi and welcome to MrExcel,

Take a look at this and see if this helps.
Assuming the 3357 on the last row of your example is a typo.

Excel 2016 (Windows) 64 bit
ABCDEFGHIJKLMNO
1EMPLID NAME PAY GROUP POS NBR APPT SALARY EFFDT THRU DATE jul-16aug-16sep-16okt-16nov-16dec-16jan-17feb-17
21234567DOE, JOHN A 1750007-1-20167-1-2017 6,250 6,250 6,250 6,250 6,250 6,250 1,420 -
31234567DOE, JOHN A 1751798-1-201728-2-2017 - - - - - - 4,841 6,265
41234567DOE, JOHN B 1550007-1-20167-1-2017 4,583 4,583 4,583 4,583 4,583 4,583 1,042 -
51234567DOE, JOHN B 1552358-1-201728-2-2017 - - - - - - 3,557 4,603

</tbody>
Sheet1

Worksheet Formulas
CellFormula
H2=MAX(IF(AND(MAX(\$F2,H\$1)=H\$1,MIN(EOMONTH(H\$1,0),\$G2)=EOMONTH(H\$1,0)),1,(NETWORKDAYS(MAX(\$F2,H\$1),MIN(EOMONTH(H\$1,0),\$G2))/NETWORKDAYS(H\$1,EOMONTH(H\$1,0)))),0)*\$E2/12

</tbody>

<tbody>
</tbody>

Thank you so much. This works perfectly. I have used the Max function before in access queries, but did not think to use it in the case.

Replies
17
Views
893
Replies
5
Views
174
Replies
5
Views
396
Replies
3
Views
239
Replies
3
Views
531

### Forum statistics

1,126,929
Messages
5,621,656
Members
415,849
Latest member
PhoenixRising2015

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back