Salary Distribution and 3 percent increment every year on hire date

[ruchperformive]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Annual Salary	Position Status	Years of Service	Hire Date	Termination Date		10/31/2020	11/30/2020	12/31/2020	1/31/2021	2/28/2021	3/31/2021	4/30/2021	5/31/2021	6/30/2021	7/31/2021	8/31/2021	9/30/2021	10/31/2021	11/30/2021	12/31/2021	1/31/2022	2/28/2022	3/31/2022	4/30/2022	5/31/2022	6/30/2022	7/31/2022	8/31/2022	9/30/2022
2	36275.32	Active	1.1	4/20/2020			3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94
3	65048.85	Inactive	10.6	11/22/2010	11/22/2021		5,420.74	5,420.74	5,420.74	5,420.74	5,420.74	5,420.74	5,420.74	5,420.74	5,420.74	5,420.74	5,420.74	5,420.74	5,420.74	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
4	166701.3	Active	3.3	2/19/2018			13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78	13,891.78
5	92204.91	Active	9.5	12/6/2011			7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74	7,683.74
6	258068.5	Active	7.6	11/13/2013			21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71	21,505.71
7	24355.88	Active	1.11	5/28/2019			2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66	2,029.66
8	52080.5	Active	0.6	11/9/2020			0.00	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04	4,340.04
9	149509.8	Active	3.2	3/19/2018			12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15	12,459.15
10	81746.7	Active	3.5	12/18/2017			6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22	6,812.22
11	168088.5	Inactive	1.11	6/3/2019	4/14/2022		14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	14,007.37	0.00	0.00	0.00	0.00	0.00	0.00
12	36275.32	Active	1.11	6/7/2019			3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94	3,022.94
13	86323.41	Active	1.0	5/11/2020			7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62	7,193.62
14	80734.83	Active	2.7	10/4/2018			6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90	6,727.90
15	126587.9	Active	#NUM!	1/2/2022			0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	10,548.99	10,548.99	10,548.99	10,548.99	10,548.99	10,548.99	10,548.99	10,548.99	10,548.99
Sheet1

Cell Formulas
Range	Formula
B2:B15	B2	=IF(ISBLANK(E2),"Active","Inactive")
C2:C15	C2	=DATEDIF(D2,TODAY(),"y")&"."&DATEDIF(D2,TODAY(),"ym")
G2:AD15	G2	=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
Cell	Condition		Cell Format	Stop If True
G2:AD15	Expression	=NOT(ISBLANK(#REF!))	text	NO

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

salary_Increment_Calculation.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
19	Desired Result					Montyly Amount	10/31/2020	11/30/2020	12/31/2020	1/31/2021	2/28/2021	3/31/2021	4/30/2021	5/31/2021	6/30/2021	7/31/2021	8/31/2021	9/30/2021	10/31/2021	11/30/2021	12/31/2021	1/31/2022	2/28/2022	3/31/2022	4/30/2022	5/31/2022	6/30/2022	7/31/2022	8/31/2022	9/30/2022
20	36275.32	Active	1.1	4/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
21	65048.85	Inactive	10.6	11/22/2010	11/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
22	166701.3	Active	3.3	2/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
23	92204.91	Active	9.5	12/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
24	258068.5	Active	7.6	11/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
25	24355.88	Active	1.11	5/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
26	52080.5	Active	0.6	11/9/2020		$ 4,340.04	FALSE	$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
27	149509.8	Active	3.2	3/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
28	81746.7	Active	3.5	12/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
29	168088.5	Inactive	1.11	6/3/2019	4/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
30	36275.32	Active	1.11	6/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
31	86323.41	Active	1.0	5/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
32	80734.83	Active	2.7	10/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
33	126587.9	Active	#NUM!	1/2/2022		$ 10,548.99	FALSE	FALSE	FALSE	$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
Range	Formula
B20:B33	B20	=IF(ISBLANK(E20),"Active","Inactive")
C20:C33	C20	=DATEDIF(D20,TODAY(),"y")&"."&DATEDIF(D20,TODAY(),"ym")
I20:V26,H33:U33,H27:V32,H20:H25,W20:AD33	I20	=IF(MONTH(I$19)=MONTH($D20),H20+(H20*0.03),H20)
G20:G33	G20	=IF(G$19>=$D20,IF(MONTH(G$19)=MONTH($D20),($F20)+($F20*0.03),F20))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G20:AD33	Expression	=NOT(ISBLANK(#REF!))	text	NO