Date Ranges and Monthly Salary Calculation

[TheSage1]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]EMPLID[/TD]
[TD]NAME[/TD]
[TD]PAY GROUP[/TD]
[TD]POS NBR[/TD]
[TD]APPT SALARY[/TD]
[TD]EFFDT[/TD]
[TD]THRU DATE[/TD]
[TD]JUL[/TD]
[TD]AUG[/TD]
[TD]SEP[/TD]
[TD]OCT[/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]DOE, JOHN[/TD]
[TD]A[/TD]
[TD]00000001[/TD]
[TD]75,000[/TD]
[TD]7/1/2016[/TD]
[TD]1/7/2017[/TD]
[TD]6,250[/TD]
[TD]6,250[/TD]
[TD]6,250[/TD]
[TD]6,250[/TD]
[TD]6,250[/TD]
[TD]6,250[/TD]
[TD]1,420[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]DOE, JOHN[/TD]
[TD]A[/TD]
[TD]00000001[/TD]
[TD]75,179[/TD]
[TD]1/8/2017[/TD]
[TD]2/28/2017[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4,841[/TD]
[TD]6,265[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]DOE, JOHN[/TD]
[TD]B[/TD]
[TD]00000001[/TD]
[TD]55,000[/TD]
[TD]7/1/2016[/TD]
[TD]1/7/2017[/TD]
[TD]4,583[/TD]
[TD]4,583[/TD]
[TD]4,583[/TD]
[TD]4,583[/TD]
[TD]4,583[/TD]
[TD]4,583[/TD]
[TD]1,042[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]DOE, JOHN[/TD]
[TD]B[/TD]
[TD]00000001[/TD]
[TD]55,235[/TD]
[TD]1/8/2017[/TD]
[TD]2/28/2017[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3,357[/TD]
[TD]4,603[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 469"]
<tbody>[TR]
[TD]DAY
[/TD]
[TD]DATE
[/TD]
[TD]WORK DAYS COMPLETE
[/TD]
[TD]WORK DAYS REMAINING
[/TD]
[TD]TOTAL WORK DAYS IN MONTH
[/TD]
[/TR]
[TR]
[TD]MONDAY
[/TD]
[TD]1/2/2017
[/TD]
[TD]1
[/TD]
[TD]21
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]TUESDAY
[/TD]
[TD]1/3/2017
[/TD]
[TD]2
[/TD]
[TD]20
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]WEDNESDAY
[/TD]
[TD]1/4/2017
[/TD]
[TD]3
[/TD]
[TD]19
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]THURSDAY
[/TD]
[TD]1/5/2017
[/TD]
[TD]4
[/TD]
[TD]18
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]FRIDAY
[/TD]
[TD]1/6/2017
[/TD]
[TD]5
[/TD]
[TD]17
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]MONDAY
[/TD]
[TD]1/9/2017
[/TD]
[TD]6
[/TD]
[TD]16
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]TUESDAY
[/TD]
[TD]1/10/2017
[/TD]
[TD]7
[/TD]
[TD]15
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]WEDNESDAY
[/TD]
[TD]1/11/2017
[/TD]
[TD]8
[/TD]
[TD]14
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]THURSDAY
[/TD]
[TD]1/12/2017
[/TD]
[TD]9
[/TD]
[TD]13
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]FRIDAY
[/TD]
[TD]1/13/2017
[/TD]
[TD]10
[/TD]
[TD]12
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]MONDAY
[/TD]
[TD]1/16/2017
[/TD]
[TD]11
[/TD]
[TD]11
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]TUESDAY
[/TD]
[TD]1/17/2017
[/TD]
[TD]12
[/TD]
[TD]10
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]WEDNESDAY
[/TD]
[TD]1/18/2017
[/TD]
[TD]13
[/TD]
[TD]9
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]THURSDAY
[/TD]
[TD]1/19/2017
[/TD]
[TD]14
[/TD]
[TD]8
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]FRIDAY
[/TD]
[TD]1/20/2017
[/TD]
[TD]15
[/TD]
[TD]7
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]MONDAY
[/TD]
[TD]1/23/2017
[/TD]
[TD]16
[/TD]
[TD]6
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]TUESDAY
[/TD]
[TD]1/24/2017
[/TD]
[TD]17
[/TD]
[TD]5
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]WEDNESDAY
[/TD]
[TD]1/25/2017
[/TD]
[TD]18
[/TD]
[TD]4
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]THURSDAY
[/TD]
[TD]1/26/2017
[/TD]
[TD]19
[/TD]
[TD]3
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]FRIDAY
[/TD]
[TD]1/27/2017
[/TD]
[TD]20
[/TD]
[TD]2
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]MONDAY
[/TD]
[TD]1/30/2017
[/TD]
[TD]21
[/TD]
[TD]1
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]TUESDAY
[/TD]
[TD]1/31/2017
[/TD]
[TD]22
[/TD]
[TD]0
[/TD]
[TD]22
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	EMPLID	NAME	PAY GROUP	POS NBR	APPT SALARY	EFFDT	THRU DATE	jul-16	aug-16	sep-16	okt-16	nov-16	dec-16	jan-17	feb-17
2	1234567	DOE, JOHN	A	1	75000	7-1-2016	7-1-2017	6,250	6,250	6,250	6,250	6,250	6,250	1,420	-
3	1234567	DOE, JOHN	A	1	75179	8-1-2017	28-2-2017	-	-	-	-	-	-	4,841	6,265
4	1234567	DOE, JOHN	B	1	55000	7-1-2016	7-1-2017	4,583	4,583	4,583	4,583	4,583	4,583	1,042	-
5	1234567	DOE, JOHN	B	1	55235	8-1-2017	28-2-2017	-	-	-	-	-	-	3,557	4,603
Sheet1

Cell Formulas
Range	Formula
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

 

[TheSage1]

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

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
EMPLID	NAME	PAY GROUP	POS NBR	APPT SALARY	EFFDT	THRU DATE
DOE, JOHN	A
DOE, JOHN	A
DOE, JOHN	B
DOE, JOHN	B

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]jul-16[/TD]
[TD="align: right"]aug-16[/TD]
[TD="align: right"]sep-16[/TD]
[TD="align: right"]okt-16[/TD]
[TD="align: right"]nov-16[/TD]
[TD="align: right"]dec-16[/TD]
[TD="align: right"]jan-17[/TD]
[TD="align: right"]feb-17[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]7-1-2016[/TD]
[TD="align: right"]7-1-2017[/TD]
[TD="align: right"] 6,250 [/TD]
[TD="align: right"] 6,250 [/TD]
[TD="align: right"] 6,250 [/TD]
[TD="align: right"] 6,250 [/TD]
[TD="align: right"] 6,250 [/TD]
[TD="align: right"] 6,250 [/TD]
[TD="align: right"] 1,420 [/TD]
[TD="align: right"] - [/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]75179[/TD]
[TD="align: right"]8-1-2017[/TD]
[TD="align: right"]28-2-2017[/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 4,841 [/TD]
[TD="align: right"] 6,265 [/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]55000[/TD]
[TD="align: right"]7-1-2016[/TD]
[TD="align: right"]7-1-2017[/TD]
[TD="align: right"] 4,583 [/TD]
[TD="align: right"] 4,583 [/TD]
[TD="align: right"] 4,583 [/TD]
[TD="align: right"] 4,583 [/TD]
[TD="align: right"] 4,583 [/TD]
[TD="align: right"] 4,583 [/TD]
[TD="align: right"] 1,042 [/TD]
[TD="align: right"] - [/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]55235[/TD]
[TD="align: right"]8-1-2017[/TD]
[TD="align: right"]28-2-2017[/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 3,557 [/TD]
[TD="align: right"] 4,603 [/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=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[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.