Monthly salary schedule using start date and termination date to populate

Tigvee · Sep 4, 2015

I have spent a significant amount of hours over the past couple months refining an equation. I'm 95% there but I ran into an issue I can't seem to fix.
Although I have searched the internet a bit for guidance, I have resisted at posting a thread here in hopes I could figure this out on my own.
I haven't been able to do it so I have prepared myself to be humbled since the gods at MrExcel most likely have an answer that will take them all of 2 mins to type.

The challenge:
1. To populate salary in a monthly schedule using employee start date and end date to drive the automated update of the table
a. Salary is paid 2x/month (15th & last day)
b. Once the start date is entered, the monthly salary can be inputted indefinitely
c. Once a termination date is entered, the salary doesn't populate past that month

Not too bad so far... but here are the hurdles:
1. If the start date or term date falls on a date that isn't exactly the start of the month, to calculate the prorated salary based on the number of business days worked thus far in the month
2. MY BUG -- The equation below works fine until you enter the last business day of the month
a. For example, 2/27 is the last business day in Feb 2015. However, the calculation will only start populating on 1st business day of March while there should be a workday rate for 1 day in Feb.

Current equation to this point:

Note I'm not married to this equation, it just kept building on itself until it became the monstrosity you see before you so if you have alternative solution, I'm open to suggestions...

=IF((IF($AB24="",MAX(((-NETWORKDAYS(BL$20,MAX(BK$20,$AA24)))-SIGN(-NETWORKDAYS(BL$20,MAX(BK$20,$AA24)))),0),MIN(MAX(((-NETWORKDAYS(BL$20,MAX(BK$20,$AA24)))-SIGN(-NETWORKDAYS(BL$20,MAX(BK$20,$AA24)))),0),MAX(MIN(((-NETWORKDAYS($AB24,BK$20))-SIGN(-NETWORKDAYS($AB24,BK$20))),((-NETWORKDAYS(BL$20,BK$20))-SIGN(-NETWORKDAYS($AB24,BK$20)))),0))))=((-NETWORKDAYS(BL$20,BK$20))-SIGN(-NETWORKDAYS(BL$20,BK$20))),$AD24/12,(IF($AB24="",MAX(((-NETWORKDAYS(BL$20,(MAX(BK$20,$AA24))))-SIGN(-NETWORKDAYS(BL$20,(MAX(BK$20,$AA24))))),0),MIN(MAX((-NETWORKDAYS(BL$20,(MAX(BK$20,$AA24))))-SIGN(-NETWORKDAYS(BL$20,(MAX(BK$20,$AA24)))),0),MAX(MIN(((-NETWORKDAYS($AB24,BK$20))-SIGN(-NETWORKDAYS($AB24,BK$20))),((-NETWORKDAYS(BL$20,BK$20)))-SIGN(-NETWORKDAYS(BL$20,BK$20))),0))))*$AO24)

Key:
AA24 = Start Date
AB24 = Term Date
AD24 = Annual Salary
AO24 = Workday Rate
BL20 = 1/31/2015
BK20 = 12/31/2014

Example:
Annual Salary Start End Dec Jan Feb Mar
Joe Blow 48000 1/1/15 (blank) 4000 4000 4000 and so on into the future
Jane Row 96000 2/27/15 (blank) calc 1 day 8000
Jeff Tow 24000 12/26/14 3/24/15 calc 4 days 2000 2000 calc 17 days
(since holidays are paid)

Thank you in advance for your help on this one... and a special thanks for always being the best resource out there for any excel related queries I can think of on any given day!
The contribution on this forum has saved me countless hours of work!

DRSteele · Sep 4, 2015

I think most would agree that we might be better able to help if you post some sample data (which you can do by following the link in my signature below).

I will say that whenever I see a formula with a dozen nested function statements I tend to believe there is a more fathomable and efficient method.

Tigvee · Sep 4, 2015

Excel 2012

L

R

X

Y

Z

AA

AB

AC

AD

AE

BJ

BK

BL

BM

BN

BO

BP

BQ

5

Annual Hrs

6

2,079.84

19

ADP

Dec-14

Jan-15

Feb-15

Mar-15

Apr-15

May-15

Jun-15

Jul-15

20

Payroll Name

Emp Type

Hire Date

Position Status

Position Start Date

Termination Date

Rehire Date

Annual Salary

Regular Pay Rate Amount

Rate 2 (Hourly)

12/31/2014

1/31/2015

2/28/2015

3/31/2015

4/30/2015

5/31/2015

6/30/2015

7/31/2015

21

last, first1

FT

07/08/13

Active

07/08/13

64,000

2,667

30.77

5,333.34

22

last, first2

FT

02/09/15

Terminated

02/09/15

07/31/15

30,500

1,271

14.66

-

2,541.68

23

last, first3

FT

12/29/08

Active

12/29/08

77,500

3,229

37.26

6,458.34

24

last, first4

FT

11/03/08

Active

11/03/08

48,040

2,002

23.10

4,003.32

25

last, first5

PT

02/09/15

Terminated

02/09/15

07/31/15

22,534

10

-

1,877.85

26

last, first6

FT

10/19/05

Active

10/19/05

73,500

3,063

35.34

6,125.00

27

last, first7

FT

02/14/11

Active

02/14/11

98,800

4,117

47.50

8,233.34

28

last, first8

FT

10/21/13

Active

10/21/13

81,200

3,383

39.04

6,766.68

29

last, first9

FT

08/17/09

Active

08/17/09

47,500

1,979

22.84

3,958.32

30

last, first10

FT

04/19/10

Active

04/19/10

74,350

3,098

35.75

6,195.84

31

last, first11

FT

03/09/15

Active

03/09/15

55,000

2,292

26.44

-

4,583.32

32

last, first12

FT

04/27/15

Active

04/27/15

65,000

2,708

31.25

-

5,416.66

33

last, first13

FT

11/01/10

Active

11/01/10

80,000

3,333

38.46

6,666.66

34

last, first14

PT

10/21/13

Terminated

10/21/13

06/04/15

33,801

15

2,816.78

-

35

last, first15

FT

06/29/15

Active

06/29/15

70,000

2,917

33.66

-

5,833.32

36

last, first16

FT

10/04/10

Active

10/04/10

48,500

2,021

23.32

4,041.66

Current Emp

Tigvee · Sep 4, 2015

Excel 2012

L

R

X

Y

Z

AA

AB

AC

AD

AE

BJ

BK

BL

BM

BN

BO

BP

BQ

34

last, first14

PT

10/21/13

Terminated

10/21/13

06/04/15

33,801

15

2,816.78

-

Current Emp

Cell Formulas
Range	Formula
BK34	=IF((IF($AA34="",MAX(((-NETWORKDAYS(BK$20,MAX(BJ$20,$Z34)))-SIGN(-NETWORKDAYS(BK$20,MAX(BJ$20,$Z34)))),0),MIN(MAX(((-NETWORKDAYS(BK$20,MAX(BJ$20,$Z34)))-SIGN(-NETWORKDAYS(BK$20,MAX(BJ$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BJ$20))-SIGN(-NETWORKDAYS($AA34,BJ$20))),((-NETWORKDAYS(BK$20,BJ$20))-SIGN(-NETWORKDAYS($AA34,BJ$20)))),0))))=((-NETWORKDAYS(BK$20,BJ$20))-SIGN(-NETWORKDAYS(BK$20,BJ$20))),$AC34/12,(IF($AA34="",MAX(((-NETWORKDAYS(BK$20,(MAX(BJ$20,$Z34))))-SIGN(-NETWORKDAYS(BK$20,(MAX(BJ$20,$Z34))))),0),MIN(MAX((-NETWORKDAYS(BK$20,(MAX(BJ$20,$Z34))))-SIGN(-NETWORKDAYS(BK$20,(MAX(BJ$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BJ$20))),((-NETWORKDAYS(BK$20,BJ$20)))-SIGN(-NETWORKDAYS(BK$20,BJ$20))),0))))*$AN34)
BL34	=IF((IF($AA34="",MAX(((-NETWORKDAYS(BL$20,MAX(BK$20,$Z34)))-SIGN(-NETWORKDAYS(BL$20,MAX(BK$20,$Z34)))),0),MIN(MAX(((-NETWORKDAYS(BL$20,MAX(BK$20,$Z34)))-SIGN(-NETWORKDAYS(BL$20,MAX(BK$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BK$20))-SIGN(-NETWORKDAYS($AA34,BK$20))),((-NETWORKDAYS(BL$20,BK$20))-SIGN(-NETWORKDAYS($AA34,BK$20)))),0))))=((-NETWORKDAYS(BL$20,BK$20))-SIGN(-NETWORKDAYS(BL$20,BK$20))),$AC34/12,(IF($AA34="",MAX(((-NETWORKDAYS(BL$20,(MAX(BK$20,$Z34))))-SIGN(-NETWORKDAYS(BL$20,(MAX(BK$20,$Z34))))),0),MIN(MAX((-NETWORKDAYS(BL$20,(MAX(BK$20,$Z34))))-SIGN(-NETWORKDAYS(BL$20,(MAX(BK$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BK$20))),((-NETWORKDAYS(BL$20,BK$20)))-SIGN(-NETWORKDAYS(BL$20,BK$20))),0))))*$AN34)
BM34	=IF((IF($AA34="",MAX(((-NETWORKDAYS(BM$20,MAX(BL$20,$Z34)))-SIGN(-NETWORKDAYS(BM$20,MAX(BL$20,$Z34)))),0),MIN(MAX(((-NETWORKDAYS(BM$20,MAX(BL$20,$Z34)))-SIGN(-NETWORKDAYS(BM$20,MAX(BL$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BL$20))-SIGN(-NETWORKDAYS($AA34,BL$20))),((-NETWORKDAYS(BM$20,BL$20))-SIGN(-NETWORKDAYS($AA34,BL$20)))),0))))=((-NETWORKDAYS(BM$20,BL$20))-SIGN(-NETWORKDAYS(BM$20,BL$20))),$AC34/12,(IF($AA34="",MAX(((-NETWORKDAYS(BM$20,(MAX(BL$20,$Z34))))-SIGN(-NETWORKDAYS(BM$20,(MAX(BL$20,$Z34))))),0),MIN(MAX((-NETWORKDAYS(BM$20,(MAX(BL$20,$Z34))))-SIGN(-NETWORKDAYS(BM$20,(MAX(BL$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BL$20))),((-NETWORKDAYS(BM$20,BL$20)))-SIGN(-NETWORKDAYS(BM$20,BL$20))),0))))*$AN34)
BN34	=IF((IF($AA34="",MAX(((-NETWORKDAYS(BN$20,MAX(BM$20,$Z34)))-SIGN(-NETWORKDAYS(BN$20,MAX(BM$20,$Z34)))),0),MIN(MAX(((-NETWORKDAYS(BN$20,MAX(BM$20,$Z34)))-SIGN(-NETWORKDAYS(BN$20,MAX(BM$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BM$20))-SIGN(-NETWORKDAYS($AA34,BM$20))),((-NETWORKDAYS(BN$20,BM$20))-SIGN(-NETWORKDAYS($AA34,BM$20)))),0))))=((-NETWORKDAYS(BN$20,BM$20))-SIGN(-NETWORKDAYS(BN$20,BM$20))),$AC34/12,(IF($AA34="",MAX(((-NETWORKDAYS(BN$20,(MAX(BM$20,$Z34))))-SIGN(-NETWORKDAYS(BN$20,(MAX(BM$20,$Z34))))),0),MIN(MAX((-NETWORKDAYS(BN$20,(MAX(BM$20,$Z34))))-SIGN(-NETWORKDAYS(BN$20,(MAX(BM$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BM$20))),((-NETWORKDAYS(BN$20,BM$20)))-SIGN(-NETWORKDAYS(BN$20,BM$20))),0))))*$AN34)
BO34	=IF((IF($AA34="",MAX(((-NETWORKDAYS(BO$20,MAX(BN$20,$Z34)))-SIGN(-NETWORKDAYS(BO$20,MAX(BN$20,$Z34)))),0),MIN(MAX(((-NETWORKDAYS(BO$20,MAX(BN$20,$Z34)))-SIGN(-NETWORKDAYS(BO$20,MAX(BN$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BN$20))-SIGN(-NETWORKDAYS($AA34,BN$20))),((-NETWORKDAYS(BO$20,BN$20))-SIGN(-NETWORKDAYS($AA34,BN$20)))),0))))=((-NETWORKDAYS(BO$20,BN$20))-SIGN(-NETWORKDAYS(BO$20,BN$20))),$AC34/12,(IF($AA34="",MAX(((-NETWORKDAYS(BO$20,(MAX(BN$20,$Z34))))-SIGN(-NETWORKDAYS(BO$20,(MAX(BN$20,$Z34))))),0),MIN(MAX((-NETWORKDAYS(BO$20,(MAX(BN$20,$Z34))))-SIGN(-NETWORKDAYS(BO$20,(MAX(BN$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BN$20))),((-NETWORKDAYS(BO$20,BN$20)))-SIGN(-NETWORKDAYS(BO$20,BN$20))),0))))*$AN34)
BP34	=IF((IF($AA34="",MAX(((-NETWORKDAYS(BP$20,MAX(BO$20,$Z34)))-SIGN(-NETWORKDAYS(BP$20,MAX(BO$20,$Z34)))),0),MIN(MAX(((-NETWORKDAYS(BP$20,MAX(BO$20,$Z34)))-SIGN(-NETWORKDAYS(BP$20,MAX(BO$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BO$20))-SIGN(-NETWORKDAYS($AA34,BO$20))),((-NETWORKDAYS(BP$20,BO$20))-SIGN(-NETWORKDAYS($AA34,BO$20)))),0))))=((-NETWORKDAYS(BP$20,BO$20))-SIGN(-NETWORKDAYS(BP$20,BO$20))),$AC34/12,(IF($AA34="",MAX(((-NETWORKDAYS(BP$20,(MAX(BO$20,$Z34))))-SIGN(-NETWORKDAYS(BP$20,(MAX(BO$20,$Z34))))),0),MIN(MAX((-NETWORKDAYS(BP$20,(MAX(BO$20,$Z34))))-SIGN(-NETWORKDAYS(BP$20,(MAX(BO$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BO$20))),((-NETWORKDAYS(BP$20,BO$20)))-SIGN(-NETWORKDAYS(BP$20,BO$20))),0))))*$AN34)
BQ34	=IF((IF($AA34="",MAX(((-NETWORKDAYS(BQ$20,MAX(BP$20,$Z34)))-SIGN(-NETWORKDAYS(BQ$20,MAX(BP$20,$Z34)))),0),MIN(MAX(((-NETWORKDAYS(BQ$20,MAX(BP$20,$Z34)))-SIGN(-NETWORKDAYS(BQ$20,MAX(BP$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BP$20))-SIGN(-NETWORKDAYS($AA34,BP$20))),((-NETWORKDAYS(BQ$20,BP$20))-SIGN(-NETWORKDAYS($AA34,BP$20)))),0))))=((-NETWORKDAYS(BQ$20,BP$20))-SIGN(-NETWORKDAYS(BQ$20,BP$20))),$AC34/12,(IF($AA34="",MAX(((-NETWORKDAYS(BQ$20,(MAX(BP$20,$Z34))))-SIGN(-NETWORKDAYS(BQ$20,(MAX(BP$20,$Z34))))),0),MIN(MAX((-NETWORKDAYS(BQ$20,(MAX(BP$20,$Z34))))-SIGN(-NETWORKDAYS(BQ$20,(MAX(BP$20,$Z34)))),0),MAX(MIN(((-NETWORKDAYS($AA34,BP$20))),((-NETWORKDAYS(BQ$20,BP$20)))-SIGN(-NETWORKDAYS(BQ$20,BP$20))),0))))*$AN34)

Tigvee · Sep 4, 2015

Line 34 example -- calcing last workday in month or overlap to next month now works but calc is now not accurate for midmonth ranges
(issue => 6/1 to 6/4 = 4 BDs but cell is calcing off 3 BD)

Tigvee · Sep 4, 2015

Thanks for the HTML maker add-in!
I look forward to the feedback!

Tigvee · Sep 8, 2015

DRSteele said:
I think most would agree that we might be better able to help if you post some sample data (which you can do by following the link in my signature below).

I will say that whenever I see a formula with a dozen nested function statements I tend to believe there is a more fathomable and efficient method.

I haven't seen a reply yet... what's going on at MrExcel??? Hopefully people are just on a Labor Day long weekend hangover.

Tigvee · Sep 9, 2015

Alright, so I posted my sample data with a thorough explanation... lets see these fathomable and efficient methods come to life!

Tigvee · Sep 15, 2015

I'm hoping to bump this thread... I'm surprised I received no responses to this post.
If anyone could help, it would be greatly appreciated!

Monthly salary schedule using start date and termination date to populate

Tigvee

New Member

DRSteele

Well-known Member

Tigvee

New Member

Tigvee

New Member

Tigvee

New Member

Tigvee

New Member

Tigvee

New Member

Tigvee

New Member

Tigvee

New Member

Similar threads

Share this page

Monthly salary schedule using start date and termination date to populate

New Member

Well-known Member

New Member

New Member

New Member

New Member

New Member

New Member

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock