Date Ranges and Monthly Salary Calculation

TheSage1

New Member
Joined
Mar 20, 2017
Messages
2
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.

EMPLIDNAMEPAY GROUPPOS NBRAPPT SALARYEFFDTTHRU DATEJULAUGSEPOCTNOVDECJANFEB
1234567DOE, JOHNA0000000175,0007/1/20161/7/20176,2506,2506,2506,2506,2506,2501,4200
1234567DOE, JOHNA0000000175,1791/8/20172/28/20170000004,8416,265
1234567DOE, JOHNB0000000155,0007/1/20161/7/20174,5834,5834,5834,5834,5834,5831,0420
1234567DOE, JOHNB0000000155,2351/8/20172/28/20170000003,3574,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>
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
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.


<b>Excel 2016 (Windows) 64 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">EMPLID </td><td style=";">NAME </td><td style=";">PAY GROUP </td><td style=";">POS NBR </td><td style=";">APPT SALARY </td><td style=";">EFFDT </td><td style=";">THRU DATE </td><td style="text-align: right;;">jul-16</td><td style="text-align: right;;">aug-16</td><td style="text-align: right;;">sep-16</td><td style="text-align: right;;">okt-16</td><td style="text-align: right;;">nov-16</td><td style="text-align: right;;">dec-16</td><td style="text-align: right;;">jan-17</td><td style="text-align: right;;">feb-17</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1234567</td><td style=";">DOE, JOHN </td><td style=";">A </td><td style="text-align: right;;">1</td><td style="text-align: right;;">75000</td><td style="text-align: right;;">7-1-2016</td><td style="text-align: right;;">7-1-2017</td><td style="text-align: right;;"> 6,250 </td><td style="text-align: right;;"> 6,250 </td><td style="text-align: right;;"> 6,250 </td><td style="text-align: right;;"> 6,250 </td><td style="text-align: right;;"> 6,250 </td><td style="text-align: right;;"> 6,250 </td><td style="text-align: right;;"> 1,420 </td><td style="text-align: right;;">            -   </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1234567</td><td style=";">DOE, JOHN </td><td style=";">A </td><td style="text-align: right;;">1</td><td style="text-align: right;;">75179</td><td style="text-align: right;;">8-1-2017</td><td style="text-align: right;;">28-2-2017</td><td style="text-align: right;;">            -   </td><td style="text-align: right;;">            -   </td><td style="text-align: right;;">            -   </td><td style="text-align: right;;">            -   </td><td style="text-align: right;;">            -   </td><td style="text-align: right;;">            -   </td><td style="text-align: right;;"> 4,841 </td><td style="text-align: right;;"> 6,265 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1234567</td><td style=";">DOE, JOHN </td><td style=";">B </td><td style="text-align: right;;">1</td><td style="text-align: right;;">55000</td><td style="text-align: right;;">7-1-2016</td><td style="text-align: right;;">7-1-2017</td><td style="text-align: right;;"> 4,583 </td><td style="text-align: right;;"> 4,583 </td><td style="text-align: right;;"> 4,583 </td><td style="text-align: right;;"> 4,583 </td><td style="text-align: right;;"> 4,583 </td><td style="text-align: right;;"> 4,583 </td><td style="text-align: right;;"> 1,042 </td><td style="text-align: right;;">            -   </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1234567</td><td style=";">DOE, JOHN </td><td style=";">B </td><td style="text-align: right;;">1</td><td style="text-align: right;;">55235</td><td style="text-align: right;;">8-1-2017</td><td style="text-align: right;;">28-2-2017</td><td style="text-align: right;;">            -   </td><td style="text-align: right;;">            -   </td><td style="text-align: right;;">            -   </td><td style="text-align: right;;">            -   </td><td style="text-align: right;;">            -   </td><td style="text-align: right;;">            -   </td><td style="text-align: right;;"> 3,557 </td><td style="text-align: right;;"> 4,603 </td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H2</th><td style="text-align:left">=MAX(<font color="#0000FF">IF(<font color="#FF0000">AND(<font color="#00FF00">MAX(<font color="#800080">$F2,H$1</font>)=H$1,MIN(<font color="#800080">EOMONTH(<font color="#008080">H$1,0</font>),$G2</font>)=EOMONTH(<font color="#800080">H$1,0</font>)</font>),1,(<font color="#00FF00">NETWORKDAYS(<font color="#800080">MAX(<font color="#008080">$F2,H$1</font>),MIN(<font color="#008080">EOMONTH(<font color="#FF00FF">H$1,0</font>),$G2</font>)</font>)/NETWORKDAYS(<font color="#800080">H$1,EOMONTH(<font color="#008080">H$1,0</font>)</font>)</font>)</font>),0</font>)*$E2/12</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

TheSage1

New Member
Joined
Mar 20, 2017
Messages
2
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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

<thead>
</thead><tbody>
</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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,303
Messages
5,623,865
Members
415,997
Latest member
ragomes

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top