Thread: Returning a Certain Result based on a specific data range

Hi

I have a problem with a calculation i am trying to do. I have to calculate holiday allocation for staff but i my company also offers length of service bonus days.

After 5 years you get 1 extra day, after 10 years you get 2, after 15 you get 3 and so on. In my sheet i have a formula based on their start date that tells me their length of service in the following format: 10 years, 2 months.

I have put an IF Statement in for each of the different milestone criteria but the result is not coming back so i know i am missing something. Is there something wrong with the formula itself or is there a better one that will allow me to put the start and end of the range i want it to look at? For example can i tell it to say if between 5 years and 9 years 11 months put 1, if between 10 years and 14 years 11 months put 2 etc in column H.

I have attached the sheet to show what i have done so far, any help would be much appreciated!!

Thanks

Mark

Perhaps simply

E2: =IF(SUM(D2),LOOKUP(DATEDIF($D2,NOW(),"y"),{0,5,10,15,20,25},{0,1,2,3,4,5}),"")
copied down

Given the increment of output is constant (0-5) you could use a basic MATCH without need for LOOKUP (and result_vector) but the above offers more flexibility should you ever choose to adjust the bonus days.

Why not use this in H1:

=FLOOR(FLOOR(DATEDIF(D2,NOW(),"y"),5)/5,1)

edit: and I think DonkeyOte means putting his formula in H1 as well.