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!
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!
