Microsoft Excel - Annual Leave Formula

Hi

Would anyone be able to help me with a formula to work out an employee's annual leave entitlement which is dependant on their length of service?

The longer somebody works for my company, the more annual leave they are entitled to.  1 years service = 23 days holiday, 2 years service = 28 days holiday, 5 years service = 29 days holiday, 10 years service = 31 days holiday.

A3: Start Date

C3: End Date

E3: =DATEDIF(A3,C3,"y") & " years " & DATEDIF(A3,C3,"ym") & " months"

G3: The formula I am looking for 

T3: 23

T4: 28

T5: 29

T6: 31

I seem to have the bread for either side of the sandwich, now I just need the meat in the middle!  Thank you in advance for your kind help and assistance.  Cheers, Wayne 

as per your description i assume that

E3: =DATEDIF(A3,C3,"y") & " years " & DATEDIF(A3,C3,"ym") & " months", E3 contains the actual working time period.

Ex: 1 years 3 months...

But when you want to calculate the leaves you need to split the above string to 1 year = 12 month and addition 3 months = 15 months and based on this 15 months you need to assign the number of holidays.

So splitting the above string will be very difficult to implement, so what i suggest make a hidden cell where it will calculate the number of months for his/her working service.

Ex: consider E10 has hidden cell:

Write the following formulae in E10:

E10: =SUM(DATEDIF(A3,C3,"y")* 12, DATEDIF(A3,C3,"ym"))

DATEDIF(A3,C3,"y")* 12 -- convert years into months

DATEDIF(A3,C3,"ym") -- number of months and sum the both of them :)

now you validate the number of holidays based on E10 cell value like this:

=IF(E10<12,"23",IF(AND(E10>12,E10<=24),"28",IF(AND(E10>24,E10<=60),"29",IF(AND(E10>60,E10<=120),"31",""))))

Thanks,

Venkata

Thank you very much - I had to edit the final formula a bit to get it to do exactly what I want, but that was simply my poor explanation to start with.

Everything works perfectly and I have now learnt a really good lesson.

Many thanks,

Wayne

I have verified only the IF condition formulae and all the other i have written instantly, cool that you want make it worked!

Thanks,