Salary Distribution and 3 percent increment every year on hire date

ruchperformive

New Member
Joined
Mar 2, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have data set consisting information like Salary, Hire Date, Termination Date, Status of employee.

We want to distribute that salary in 12 months and also want to factor 3% increment every year from their joining date. Also want to factor in condition that if any employee joins in current year we dont want to give 3% salary raise.

Currently this is semi auto process and my goal is to make it full automized by pro rating salary based on Hire and termination date and 3% increments YOY in there base salary until they are active employee

1. First calculation pro rates the salary in 12 month based on hire and termination date
salary_Increment_Calculation.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Annual SalaryPosition StatusYears of ServiceHire DateTermination Date10/31/202011/30/202012/31/20201/31/20212/28/20213/31/20214/30/20215/31/20216/30/20217/31/20218/31/20219/30/202110/31/202111/30/202112/31/20211/31/20222/28/20223/31/20224/30/20225/31/20226/30/20227/31/20228/31/20229/30/2022
236275.32Active1.14/20/20203,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.94
365048.85Inactive10.611/22/201011/22/20215,420.745,420.745,420.745,420.745,420.745,420.745,420.745,420.745,420.745,420.745,420.745,420.745,420.740.000.000.000.000.000.000.000.000.000.000.00
4166701.3Active3.32/19/201813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.7813,891.78
592204.91Active9.512/6/20117,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.747,683.74
6258068.5Active7.611/13/201321,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.7121,505.71
724355.88Active1.115/28/20192,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.662,029.66
852080.5Active0.611/9/20200.004,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.044,340.04
9149509.8Active3.23/19/201812,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.1512,459.15
1081746.7Active3.512/18/20176,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.226,812.22
11168088.5Inactive1.116/3/20194/14/202214,007.3714,007.3714,007.3714,007.3714,007.3714,007.3714,007.3714,007.3714,007.3714,007.3714,007.3714,007.3714,007.3714,007.3714,007.3714,007.3714,007.3714,007.370.000.000.000.000.000.00
1236275.32Active1.116/7/20193,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.943,022.94
1386323.41Active1.05/11/20207,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.627,193.62
1480734.83Active2.710/4/20186,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.906,727.90
15126587.9Active#NUM!1/2/20220.000.000.000.000.000.000.000.000.000.000.000.000.000.000.0010,548.9910,548.9910,548.9910,548.9910,548.9910,548.9910,548.9910,548.9910,548.99
Sheet1
Cell Formulas
RangeFormula
B2:B15B2=IF(ISBLANK(E2),"Active","Inactive")
C2:C15C2=DATEDIF(D2,TODAY(),"y")&"."&DATEDIF(D2,TODAY(),"ym")
G2:AD15G2=IF(ISBLANK($E2),IF(G$1>=$D2,$A2/12,0),IF(AND(G$1>=$D2,G$1<$E2),$A2/12,0))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2:AD15Expression=NOT(ISBLANK(#REF!))textNO


2. desired and Second calculation fulfills 3% increment YOY based on hire date.

salary_Increment_Calculation.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
19Desired ResultMontyly Amount10/31/202011/30/202012/31/20201/31/20212/28/20213/31/20214/30/20215/31/20216/30/20217/31/20218/31/20219/30/202110/31/202111/30/202112/31/20211/31/20222/28/20223/31/20224/30/20225/31/20226/30/20227/31/20228/31/20229/30/2022
2036275.32Active1.14/20/2020$ 3,022.94$3,023$3,023$3,023$3,023$3,023$3,023$3,114$3,114$3,114$3,114$3,114$3,114$3,114$3,114$3,114$3,114$3,114$3,114$3,207$3,207$3,207$3,207$3,207$3,207
2165048.85Inactive10.611/22/201011/22/2021$ 5,420.74$5,421$5,583$5,583$5,583$5,583$5,583$5,583$5,583$5,583$5,583$5,583$5,583$5,583$5,751$5,751$5,751$5,751$5,751$5,751$5,751$5,751$5,751$5,751$5,751
22166701.3Active3.32/19/2018$ 13,891.78$13,892$13,892$13,892$13,892$14,309$14,309$14,309$14,309$14,309$14,309$14,309$14,309$14,309$14,309$14,309$14,309$14,738$14,738$14,738$14,738$14,738$14,738$14,738$14,738
2392204.91Active9.512/6/2011$ 7,683.74$7,684$7,684$7,914$7,914$7,914$7,914$7,914$7,914$7,914$7,914$7,914$7,914$7,914$7,914$8,152$8,152$8,152$8,152$8,152$8,152$8,152$8,152$8,152$8,152
24258068.5Active7.611/13/2013$ 21,505.71$21,506$22,151$22,151$22,151$22,151$22,151$22,151$22,151$22,151$22,151$22,151$22,151$22,151$22,815$22,815$22,815$22,815$22,815$22,815$22,815$22,815$22,815$22,815$22,815
2524355.88Active1.115/28/2019$ 2,029.66$2,030$2,030$2,030$2,030$2,030$2,030$2,030$2,091$2,091$2,091$2,091$2,091$2,091$2,091$2,091$2,091$2,091$2,091$2,091$2,153$2,153$2,153$2,153$2,153
2652080.5Active0.611/9/2020$ 4,340.04FALSE$4,340$4,340$4,340$4,340$4,340$4,340$4,340$4,340$4,340$4,340$4,340$4,340$4,470$4,470$4,470$4,470$4,470$4,470$4,470$4,470$4,470$4,470$4,470
27149509.8Active3.23/19/2018$ 12,459.15$12,459$12,459$12,459$12,459$12,459$12,833$12,833$12,833$12,833$12,833$12,833$12,833$12,833$12,833$12,833$12,833$12,833$13,218$13,218$13,218$13,218$13,218$13,218$13,218
2881746.7Active3.512/18/2017$ 6,812.22$6,812$6,812$7,017$7,017$7,017$7,017$7,017$7,017$7,017$7,017$7,017$7,017$7,017$7,017$7,227$7,227$7,227$7,227$7,227$7,227$7,227$7,227$7,227$7,227
29168088.5Inactive1.116/3/20194/14/2022$ 14,007.37$14,007$14,007$14,007$14,007$14,007$14,007$14,007$14,007$14,428$14,428$14,428$14,428$14,428$14,428$14,428$14,428$14,428$14,428$14,428$14,428$14,860$14,860$14,860$14,860
3036275.32Active1.116/7/2019$ 3,022.94$3,023$3,023$3,023$3,023$3,023$3,023$3,023$3,023$3,114$3,114$3,114$3,114$3,114$3,114$3,114$3,114$3,114$3,114$3,114$3,114$3,207$3,207$3,207$3,207
3186323.41Active1.05/11/2020$ 7,193.62$7,194$7,194$7,194$7,194$7,194$7,194$7,194$7,409$7,409$7,409$7,409$7,409$7,409$7,409$7,409$7,409$7,409$7,409$7,409$7,632$7,632$7,632$7,632$7,632
3280734.83Active2.710/4/2018$ 6,727.90$6,930$6,930$6,930$6,930$6,930$6,930$6,930$6,930$6,930$6,930$6,930$6,930$7,138$7,138$7,138$7,138$7,138$7,138$7,138$7,138$7,138$7,138$7,138$7,138
33126587.9Active#NUM!1/2/2022$ 10,548.99FALSEFALSEFALSE$0$0$0$0$0$0$0$0$0$0$0$0$10,549$10,549$10,549$10,549$10,549$10,549$10,549$10,549$10,549
Sheet1
Cell Formulas
RangeFormula
B20:B33B20=IF(ISBLANK(E20),"Active","Inactive")
C20:C33C20=DATEDIF(D20,TODAY(),"y")&"."&DATEDIF(D20,TODAY(),"ym")
I20:V26,H33:U33,H27:V32,H20:H25,W20:AD33I20=IF(MONTH(I$19)=MONTH($D20),H20+(H20*0.03),H20)
G20:G33G20=IF(G$19>=$D20,IF(MONTH(G$19)=MONTH($D20),($F20)+($F20*0.03),F20))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G20:AD33Expression=NOT(ISBLANK(#REF!))textNO
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,141,417
Messages
5,706,311
Members
421,441
Latest member
VapesRub

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