Rounding off decimals

B

baidya91

Board Regular
Joined
Jun 1, 2016
Messages
147
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?
 
Last edited:
Sort by date Sort by votes
Noticed a small bit that's not needed in the formula, updated below:


Book1
AB
119 years19 Years
219 years 15 days19 Years
319 years 0 months 15 days19 Years
419 years 2 months19 Years
519 years 3 months 20 days19 Years 3 months
619 years 6 months 6 days19 Years 6 months
719 years 9 months 20 days20 Years
819 years 11 months 20 days20 Years
Sheet422
Cell Formulas
RangeFormula
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,SEARCH("years",A1)+6,2)+0,,,3,3,3,6,6,6,,,,)&" months"," months",""),""),"")
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
jtakw said:
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:

AB
19 years19 Years
19 years 15 days19 Years
19 years 0 months 15 days19 Years
19 years 2 months19 Years
19 years 3 months 20 days19 Years 3 months
19 years 6 months 6 days19 Years 6 months
19 years 9 months 20 days20 Years
19 years 11 months 20 days20 Years

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

</tbody>
Sheet422​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=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",""),""),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
IF A1=19 years 7 months 1days, B1=19 years 6, months is not appearing.
 
Upvote 0
Works for me....


Book1
AB
119 years19 Years
219 years 15 days19 Years
319 years 0 months 15 days19 Years
419 years 2 months19 Years
519 years 3 months 20 days19 Years 3 months
619 years 6 months 6 days19 Years 6 months
719 years 7 months 1days19 Years 6 months
819 years 9 months 20 days20 Years
919 years 11 months 20 days20 Years
Sheet422
Cell Formulas
RangeFormula
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,SEARCH("years",A1)+6,2)+0,,,3,3,3,6,6,6,,,,)&" months"," months",""),""),"")


EDIT: Very Important, for the formula to work properly, make Certain there are 2 spaces in front of the 2nd months within the quotes as highlighted below:

=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,SEARCH("years",A1)+6,2)+0,,,3,3,3,6,6,6,,,,)&" months"," months",""),""),"")

2 (Two) spaces in front of the Red months.
 
Last edited:
Upvote 0
jtakw said:
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:

AB
19 years19 Years
19 years 15 days19 Years
19 years 0 months 15 days19 Years
19 years 2 months19 Years
19 years 3 months 20 days19 Years 3 months
19 years 6 months 6 days19 Years 6 months
19 years 9 months 20 days20 Years
19 years 11 months 20 days20 Years

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

</tbody>
Sheet422​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=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",""),""),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
months is not appearing in the result.
 
Upvote 0
jtakw said:
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:

AB
19 years19 Years
19 years 15 days19 Years
19 years 0 months 15 days19 Years
19 years 2 months19 Years
19 years 3 months 20 days19 Years 3 months
19 years 6 months 6 days19 Years 6 months
19 years 9 months 20 days20 Years
19 years 11 months 20 days20 Years

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

</tbody>
Sheet422​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=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",""),""),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Sorry to say that 19 years 3 months or 19 years 3 months 20 days will be converted into 19 years 6 months. A period equal to 3 or more than 3 months will be counted as next 6 months. Please see to that.
 
Upvote 0
Please clarify, so now you're saying if it's 3 months to 8 months, it should be 6 months ?
 
Upvote 0
Just needed a small mod to the formula.

PLEASE, remember, there are 2 spaces in front of the Last " months" within the quote marks:


Book1
AB
119 years19 Years
219 years 15 days19 Years
319 years 0 months 15 days19 Years
419 years 2 months19 Years
519 years 3 months19 Years 6 months
619 years 3 months 20 days19 Years 6 months
719 years 6 months 6 days19 Years 6 months
819 years 7 months 1days19 Years 6 months
919 years 9 months 20 days20 Years
1019 years 11 months 20 days20 Years
Sheet422
Cell Formulas
RangeFormula
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,SEARCH("years",A1)+6,2)+0,,,6,6,6,6,6,6,,,,)&" months"," months",""),""),"")


=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,SEARCH("years",A1)+6,2)+0,,,6,6,6,6,6,6,,,,)&" months"," months",""),""),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Current financial year and Pior financial year
Replies
3
Views
94
Sarah7828
S
D
  • Question Question
20 team round robin golf league schedule - 3 courses
Replies
0
Views
372
dep1969
D
M
  • Question Question
Calculate Date + 2 years starting 1st Jan
Replies
5
Views
75
FormR
FormR
B
  • Question Question
Chart help - rainfall data
Replies
2
Views
261
JV0710
J
S
  • Solved
Conditional Formatting for when the month/year date for the same ID on two worksheets doesn't match
Replies
7
Views
125
etaf
etaf

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top