Conditional formula

[EduPAz]

Hi,

Can someone please help me with this formula?

I have this info:
Column A = employee name
Column B = hiring day
Column C = vacation start date
**Column D = Xtra vacation days
Column E = Value from column D + 15 (default)


**If an employee has worked more than 4 years, put in column D 1 day
If an employee has worked more than 5 years, put in column D 2 days
If an employee has worked more than 5, 6, 7..., put in column D 3 days. The maximum number of xtra days is 3

Thanks,

Edu

 

[jtakw]

If an employee has worked more than 5 years, put in column D 2 days
If an employee has worked more than 5, 6, 7..., put in column D 3 days. The maximum number of xtra days is 3

Hi,

Please clarify above in red for 5 years.

 

[EduPAz]

Hi,

Sorry, my mistake.

It will be like this:

If an employee has worked 6 years, put in Column D 2 days
If an employee has worked 7 or more years, put in Column D 3 days. The maximum number of xtra days is 3

 

[jtakw]

Thanks for clarifying.

You can use a Nested IF statement like D2,
Or, use a LOOKUP formula with your requirements "hard-coded" in the formula,
Or, use the LOOKUP formula referencing your requirements in a Table (H2:I5 in my sample), this is more flexible if the requirements might change, just change them in the Table:

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Hire Date	V Start Date	Xtra V Days				years	days
2	John Doe	1/15/2015		1	1	1		0	0
3	Jane Doe	3/2/2011		3	3	3		4	1
4	Jack Doe	12/1/2017		0	0	0		6	2
5	Jill Doe	12/30/2012		2	2	2		7	3
Sheet515

Cell Formulas
Range	Formula
D2	=IF(EDATE(B2,84)<=TODAY(),3,IF(EDATE(B2,72)<=TODAY(),2,IF(EDATE(B2,48)<=TODAY(),1,0)))
E2	=LOOKUP(YEARFRAC(B2,TODAY(),1),{0,4,6,7},{0,1,2,3})
F2	=LOOKUP(YEARFRAC(B2,TODAY(),1),H$2:H$5,I$2:I$5)