Rounding off decimals

[baidya91]

How to round off 19 years 2 months to 19 years, 19 years 3 months 20 days to 19 years 3 months, 19 years 6 months 6 days to 19 years 6 moths and 19 years 9 days 20 days to 20 years in excel?

 

[Rick Rothstein]

How to round off 19 years 2 months to 19 years, 19 years 3 months 20 days to 19 years 3 months, 19 years 6 months 6 days to 19 years 6 moths and 19 years 9 days 20 days to 20 years in excel?

I presume what I highlighted in red above should have been 19 years 9 months 20 days, correct? I don't understand the logic of rounding it up to 20 years as opposed to down (like you did in your other examples) to 19 years 9 months.

 

[baidya91]

Yes, the last one is 19 years and 9 months. The logic is to find out the qualifying period of a service where 19 years and less than 3 years is counted as 19 years, 19 years and more or equal 3 months counted as 19 years 3 months, 19 years and more or equal 6 months counted as 19 years 6 months and 19 years and more or equal 9 months counted as 20 years. please help.

 

[Rick Rothstein]

Yes, the last one is 19 years and 9 months. The logic is to find out the qualifying period of a service where 19 years and less than 3 years is counted as 19 years, 19 years and more or equal 3 months counted as 19 years 3 months, 19 years and more or equal 6 months counted as 19 years 6 months and 19 years and more or equal 9 months counted as 20 years. please help.

I presume what I highlighted in red should have been the word "months", not "years". Here is what I understand from your post...

19 years to just under 19 years 3 months becomes 19 years.

19 years 3 months to just under 19 years 6 months becomes 19 years 3 months.

19 years 6 months to just under 19 years 9 months becomes 19 years 6 months.

19 years 9 months to 20 years becomes 20 years.

From that I conclude it is impossible for 19 years 9 months to ever be the answer... is that correct?

 

[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?

 

[Rick Rothstein]

[FONT=&]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?[/FONT]

Until I understand how your values are arrived at, I cannot create a solution for you. Unfortunately, I am still not sure of how your output is calculated. Is what I wrote in message #4 correct? If not, please show me ranges and results for those ranges like I did in Message #4 .

 

[baidya91]

Suppose a duration is of the range 19 years to 19 years 3 (>3) months. it will be counted as 19 years.
Similarly a duration is of the range 19 years 3 (<=3) months to 19 years 6 months. It will be calculated as 19 years 6 months.
19 years 6 (<=6) months to 19 years 9 months to be counted as 19 years 9 months
And 19 years 9 (<=9) months to 20 years to be treated as 20 years.

 

[jtakw]

Posted again here: https://www.mrexcel.com/forum/excel...et-qualifying-service-period.html#post5197444

 

[baidya91]

Until I understand how your values are arrived at, I cannot create a solution for you. Unfortunately, I am still not sure of how your output is calculated. Is what I wrote in message #4 correct? If not, please show me ranges and results for those ranges like I did in Message #4 .

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 16 years.

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

 

[jtakw]

Hi,

Depending on how something like my Row 2 and Row 3 samples are inputted (i.e. no month, 0 month), the formula may be shortened a bit.
Also, if the number of years can be 1 or 0, I'll modify the formula to suit:

Book1
	A	B
1	19 years	19 Years
2	19 years 15 days	19 Years
3	19 years 0 months 15 days	19 Years
4	19 years 2 months	19 Years
5	19 years 3 months 20 days	19 Years 3 months
6	19 years 6 months 6 days	19 Years 6 months
7	19 years 9 months 20 days	20 Years
8	19 years 11 months 20 days	20 Years
Sheet422

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(" ",A1)-1)+IF(ISNUMBER(SEARCH("month",A1)),IF(MID(A1&" 0",SEARCH("years",A1)+6,2)+0>=9,1,0),0)&" Years"&IFERROR(IF(ISNUMBER(SEARCH("month",A1)),SUBSTITUTE(" "&CHOOSE(MID(A1&" 1",SEARCH("years",A1)+6,2)+0,,,3,3,3,6,6,6,,,,)&" months"," months",""),""),"")