Lookup based on ranges

[Mrs. T]

need to figure out a way to take a date and determine how much of an accrual should be added.

In other words, I have their hire date and once they hit 6 months of employment, they receive a certain accrual amount. They accrue this amount until they hit 1 year which is a different accrual amount. The next step is at year 2 and then again at year 5.

How do I create a formula to look at the YOS and determine the highest accrual that they should receive without making a long list of dates?

This is the schedule:
1.00 - 1.99 = 3.08
2.00 - 4.99 = 3.08
5.00 - 9.99 = 4.16

An example would be: Date of hire 06.12.04 should accrue at 3.08

 

[dave3009]

Hi There

Not sure if I got you right but have a look at the 2 options below. Opt 1 references a table which is easy to edit should your 'accrue' change in the future. Opt2 looks at an array in built into the function.

Book2
	A	B	C	D	E
10		StartDate	Opt1	Opt2
11	Bob	01/01/2007	1.54	1.54
12	Dan	03/06/1999	4.16	4.16
13	Ian	01/03/1984	8.32	8.32
14
15
16		Service	Accrual
17		0.5	1.54
18		1	3.08
19		2	3.08
20		5	4.16
21		10	8.32
Sheet1

HTH


Dave

 

[barry houdini]

Your accrual amounts for the second two categories are the same, is that correct? I'll assume that 1 - 1.99 should be 2.22.

With hire date in A1

=LOOKUP(DATEDIF(A1,TODAY(),"y"),{0,1,2,5},{0,2.22,3.08,4.16})

The above gives zero up to 1 year but you said there was an accrual amount for 6 months? If so you could use a revised formula like this, where accrual amount for 6 months to 1 year is 1.5

=LOOKUP(DATEDIF(A1,TODAY(),"m"),{0,6,12,24,60},{0,1.5,2.22,3.08,4.16})

Note: both formulas will still give 4.16 for 10 years and beyond

 

[Mrs. T]

Dave3009 & Barry houdini, thank you for both of your input.

I will be using both methods in the spreadsheets that I am going to tackle on this project...

I really appreciate your help!