Excel 2010: round up

[FiDonk]

Hello

I am looking for a formula to round up a date of birth to the relevant age e.g. if the person is 37 and 7 months the round up and age column total would automatically calculate 38. If the person was 37 and 6 months, the calculation would be 37.

I have three columns, DOB, Age and then Age rounded up.

Does anyone have any ideas of how I may do this please? I am at basic level with excel use.

Many thanks in anticipation.

 

[Joe4]

Welcome to the Board!

Is your Age value a formula or hard-coded value?
If a formula, what does the formula look like?

Here is a link to an old thread that shows how to round age to the nearest whole number.
https://www.mrexcel.com/forum/excel-questions/486582-rounding-age-calculation.html

 

[FiDonk]

hello the age value is a formula e.g.

=DATEDIF(A3,$B$2,"Y")&" Years, "&DATEDIF(A3,$B$2,"YM")&" Months"

this provides me with the Age as At and now looking for a separate column to round up the age if >6 months

Many thanks for replying to my post.

 

[Joe4]

Did you try the formulas in that link, that show you how you can round the age to the nearest whole number?

 

[FiDonk]

to be honest, it took me ages to get this formula together and I did it sometime ago, I am not sure how to add the round up to the nearest whole number?

Thanks

 

[Joe4]

Notice that the formula the person posted in their first post is remarkably similar to yours.
So you should just be able to use the formulas in the other two posts, just substituting in your cell references for theirs.

It looks like what they used "C3" for, you used "A3", and what they used "K3" for, you used "$B$2".
So in their formulas, simply put in A3 wherever you see C3, and put in $B$2 wherever you see K3.

 

[FiDonk]

I will give that a go, thank you

 

[FiDonk]

Hi

When I did this, I get the following results...

[TABLE="width: 366"]
<tbody>[TR]
[TD]12/01/1955[/TD]
[TD]63 Years, 3 Months[/TD]
[TD]64[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]
[TABLE="width: 366"]
<tbody>[TR]
[TD]
When it should read 63?? if 6 months or less and 64 if more than 6 months into the year?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[shg]

Maybe ...

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

DoB​

[/td][td="bgcolor:#F3F3F3"]

As Of​

[/td][td="bgcolor:#F3F3F3"]

~ Age​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

15 Jan 1955​

[/td][td]

15 Jul 2017​

[/td][td="bgcolor:#E5E5E5"]

63​

[/td][td]C2: =DATEDIF(A2,B2, "y") + (DATEDIF(A2,B2, "ym") >= 6)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

16 Jan 1955​

[/td][td]

15 Jul 2017​

[/td][td="bgcolor:#E5E5E5"]

62​

[/td][td][/td][/tr]
[/table]

 

[FiDonk]

okies

Many thanks for this. I have tried this, feel very close to the result.....but...

when I type this in the cell and press ENTER, the formula remains in the cell and the not the result, I have tried switching cells between general and formula but this didn't make any difference..

It looks like this

- Hope you can see this image??

If not, my cells are A3 and B3 and I typed this:

C3: = DATEDIF(A3,C3, "y") + (DATEDIF(A3,B3, "ym") > = 6)

Thanks