Calculating net qualifying service period

[baidya91]

In a service, below 3 months of service will not be counted. Three or more than three months of service will be counted as next 6 months qualifying service.How will i calculate it in excel?

For example: 15 years 2 (<3) months to be calculated as 15 years.
15 years 3/4/6/7/8 (<=3,>9) months to be calculates as 15 years 6 months
15 years 9/10/11/12 (<=9) months to be calculated as 20 years.

 

[etaf]

how is the data entered
seperate cells for years and months
or a date?
also yiu go from 15 years 9/10/11/12 to 20 years
is it 5 year leaps
so 5 years 9/10/11/12 = 10years

 

[baidya91]

One cell is to be used for entry: Suppose the cell is G5, where G5=14 years 2 months 20 days and the result will be 14 years in cell H5. No, 15 years 9/10/11/12 months will be turned into 16 years which I provide wrong earlier.

 

[etaf]

G5=14 years 2 months 20 days

is that how entered
what if zero days or months
hows that entered

 

[baidya91]

is that how entered
what if zero days or months
hows that entered

In that case G5= 14 years 0 month 0 day.

 

[baidya91]

In that case G5= 14 years 0 month 0 day.

Please help.

 

[jtakw]

This is a duplicate to this post: https://www.mrexcel.com/forum/excel...ding-off-decimals-post5197343.html?highlight=

 

[jtakw]

I've posted a formula solution at your Original thread above.

 

[Fluff]

@baidya91
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).