Help With Helper Cells

[zendog1960]

Good Morning,

I have pasted the spreadsheet in question. In the far-right, white area I have the factors for how much vacation time an employee gets each week. What I need to happen is, for example, the first 2 weeks in May the employee was in their 2nd year. However, the week of the 15th they will then be in their 3rd year and the factor changes. The Employee would change the cell below B4 to reflect the change. So here's the question: How can I stop the change from 2 to 3 years and not have it reflect previous dates but do affect all future dates?

I get by with excel pretty good but this one baffles me. Any and all help would be extremely appreciated.

New VaCay Sick log.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
4	Employed			Employee				2021		Sick Carry Over			Vacation Carry Over
5	2									28.70			30.78
6	Stipend	Payday	Week Ending…	Total Hours	Hrs Sick	Hrs Vacation	Hrs Holiday	Total Hours Worked	Factor	Sick Balance		Factor	Vacation Balance	Employed	Vacation per YR	Factor
7		P	1-May	40				40	1.33	30.03		1.54	32.32	1	40	1.54
8			8-May	40				40	1.33	31.37		1.54	33.86	2	40	1.54
9	S	P	15-May	40	8			32	1.07	24.43		1.54	35.40	3	45	1.73
10			22-May											4	50	1.92
11		P	29-May											5	50	1.92
12														6	60	2.31
Sick & Vacation Time

Cell Formulas
Range	Formula
I7:I11	I7	=IF(E7<>"",E7-(SUM(F7:H7)),"")
J7:J11	J7	=IFERROR(I7/30,"")
K7	K7	=IF(E7<>"",IF(E7=0,K5,(J7+K5)-F7),"")
K8:K11	K8	=IF(E8<>"",IF(E8=0,K7,(J8+K7)-F8),"")
D8:D11	D8	=D7+7
N7	N7	=IFERROR(IF(G7=0,$N5+M7,N5-G7),"")
N8:N11	N8	=IFERROR(IF(G8=0,N7+M8,N7-G8),"")
M7:M12	M7	=IF(E7="","",DGET($O$6:$Q$12,3,$B$4:$D$5))
Q7:Q12	Q7	=$P7/$R$7

 

[Fluff]

How about putting the start date in B5 & using this

+Fluff 1.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
4	Employed			Employee				2021		Sick Carry Over			Vacation Carry Over
5	14/05/2018									28.7			30.8
6	Stipend	Payday	Week Ending…	Total Hours	Hrs Sick	Hrs Vacation	Hrs Holiday	Total Hours Worked	Factor	Sick Balance		Factor	Vacation Balance	Employed	Vacation per YR	Factor
7		P	01/05/2021	40				40	1.33	30.03		1.54	32.32	1	40	1.54
8			08/05/2021	40				40	1.33	31.37		1.54	33.86	2	40	1.54
9	S	P	15/05/2021	40	8			32	1.07	24.43		1.73	35.59	3	45	1.73
10			22/05/2021											4	50	1.92
11		P	29/05/2021											5	50	1.92
12														6	60	2.31
Summary

Cell Formulas
Range	Formula
I7:I11	I7	=IF(E7<>"",E7-(SUM(F7:H7)),"")
J7:J11	J7	=IFERROR(I7/30,"")
K7	K7	=IF(E7<>"",IF(E7=0,K5,(J7+K5)-F7),"")
K8:K11	K8	=IF(E8<>"",IF(E8=0,K7,(J8+K7)-F8),"")
D8:D11	D8	=D7+7
N7	N7	=IFERROR(IF(G7=0,$N5+M7,N5-G7),"")
N8:N11	N8	=IFERROR(IF(G8=0,N7+M8,N7-G8),"")
M7:M12	M7	=IF(E7="","",VLOOKUP(DATEDIF($B$5,D7,"y"),$O$7:$Q$12,3,0))

 

[zendog1960]

Thanks Fluff! That will work. Just need to tweak a few things now.

 

[Fluff]

You're welcome & thanks for the feedback.