Need help correcting conditional formula

[nadimtmbd]

Existing Calculation:
* Service Year goes up when service lenght exceed 6th month of a certain year.
for example,
if service length is, say, 5 year 5 months 29 days; then service year is still 5 [Row 2]
but if service length is, 5 year 6 months 1 day; then service year goes up to 6 [Row 3]
* When Service year is equal to or greater than 10, number of Gratuity is 1.5 times of service year
For example,
when service year=9; No. of gratuity=9 [Row 4]
but, when service year=10; No. of gratuity=15 [Row 5]

Problem
* Service Year goes up when service lenght is 0 (zero) month of a certain year
for example, When service length is 6 year 0 month 0 days; then service year turns into 7. [Row 6] which should remain 6.

Required Calculation:
Service year calculation need an exception only for the '9th year'
Service year '9' will turn into '10' only if service length exceeds 12th month of 9th year. that is, service year 9 will go up to 10 after serving full 10 year. not after 9th year 6th month.

[samba_ravi]

try below formula in AD2, copy and paste towards down
=DATEDIF($F2,$Z2+1,"Y")+IF(AND(DATEDIF($F2,$Z2+1,"YM")>=6,DATEDIF($F2,$Z2+1,"Y")<>9),1,0)

[bebo021999]

Sevice year, AD2
=IF(AB2=9,9,AB2+IF(AC2>=6,1,0))

AE2
=$AD2*IF($AD2>=10,1.5,1)