Spreadsheet: 715477d1611730090-need-help-correcting-conditional-formula-excel-help-forum-sheet
Sheet: Sheet1

 |  |  |  |  | DATE OF JOIN |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | LAST WORKINGDATE | SERVICE LENGTH | YEAR | MONTH | SERVICE YEAR | NO OF GRATUITY |  | 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.
 |  |  |  |  | 2015-01-26 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2020-07-24 00:00:00
 |  |  |  |  | 2015-01-26 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2020-07-26 00:00:00
 |  |  |  |  | 2011-01-26 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2020-07-24 00:00:00
 |  |  |  |  | 2011-01-26 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2020-07-26 00:00:00
 |  |  |  |  | 2015-01-26 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2021-01-25 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Existing Calculation:
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | * Service Year goes up when service lenght exceed 6th month of a certain year.
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | for example,
 |  |  |  |  | *blank hidden columns are kept intentionnally for confidential calculations |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 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 [NAME] [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.