Salary Increase per month

[HD1080]

I need to show a way of counting the team cost per month taking in to account salary increases, the way I'm doing this currently is to have an end date placed as if they've left the business on their last salary then a new start date placed for when they start their new salary. In reality, this is not a great way to do this as if I need to look at the raw data there will be duplicate names and a minefield trying to figure out actual start dates and end dates.

What I've done now is create new columns with the increase which is actually their total salary increase rather than the amount (Maybe the amount would make more sense rather than to divide it per month) then the to and from dates, blank end dates mean they're still employed.

So as an example The team cost from Jan to April would be 1,500 but would go up to 1650 from May. Another issue I'm facing is because a salary increase could start mid-month or first week of the month I wouldn't want it to count in both months.

 

[GraH]

Hi HD1080,

Welcome to the forum. Please be aware most of us who are offering frequent help to others, actually don't like retyping data from a screenshot. This forum offers thereto a great tool to share data that can be copied and pasted with a single click: "XL2BB". There is a link in my signature so you can download and install the add-in.

 

[HD1080]

Sorry. Here's the data.

SalCalc.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	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	Department	Start Date	End Date	Name	Monthly Salary	Starting Salary	Currently Employed	Increase Number	Current Salary	Increase 1	Increase Date From	Increase Date To	Increase 2	Increase Date From	Increase Date To	Increase 3	Increase Date From	Increase Date To	Increase 4	Increase Date From	Increase Date To	Increase 5	Increase Date From	Increase Date To	Increase 6	Increase Date From	Increase Date To	Increase 7	Increase Date From	Increase Date To	Increase 8	Increase Date From	Increase Date To	Increase 9	Increase Date From	Increase Date To	Increase 10	Increase Date From	Increase Date To
3	Team 3	10/11/2018		Simon	1,500.00	18,000.00	Yes	2	£25,000.00	21000.00	04/05/2019		25000	05/09/2019
11
12
13		Range Start	01/01/2019	01/02/2019	01/03/2019	01/04/2019	01/05/2019	01/06/2019	01/07/2019	01/08/2019	01/09/2019	01/10/2019	01/11/2019	01/12/2019	01/01/2020	01/02/2020	01/03/2020	01/04/2020	01/05/2020	01/06/2020	01/07/2020	01/08/2020	01/09/2020	01/10/2020	01/11/2020	01/12/2020
14		Range End	31/01/2019	28/02/2019	31/03/2019	30/04/2019	31/05/2019	30/06/2019	31/07/2019	31/08/2019	30/09/2019	31/10/2019	30/11/2019	31/12/2019	31/01/2020	29/02/2020	31/03/2020	30/04/2020	31/05/2020	30/06/2020	31/07/2020	31/08/2020	30/09/2020	31/10/2020	30/11/2020	31/12/2020
15	Department		January	February	March	April	May	June	July	August	September	October	November	December	January	February	March	April	May	June	July	August	September	October	November	December	January
16	Team 1
17	Team 2
18	Team 3
19	Team 4
20	Team 5
21
22
23
24
Sheet1

Cell Formulas
Range	Formula
G3	G3	=IF(C3="","Yes","No")
H3	H3	=IF(AK3<>"",10,IF(AH3<>"",9,IF(AE3<>"",8,IF(AB3<>"",7,IF(Y3<>"",6,IF(V3<>"",5,IF(S3<>"",4,IF(P3<>"",3,IF(M3<>"",2,IF(J3<>"",1,""))))))))))
I3	I3	=IF(AK3<>"",AK3,IF(AH3<>"",AH3,IF(AE3<>"",AE3,IF(AB3<>"",AB3,IF(Y3<>"",Y3,IF(V3<>"",V3,IF(S3<>"",S3,IF(P3<>"",P3,IF(M3<>"",M3,IF(J3<>"",J3,F3))))))))))
E3	E3	=F3/12
C14:Z14	C14	=EOMONTH(C13,0)

 

[Fluff]

Cross posted Salary increase per team across year

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.