Ages in form yy/mm manipulation error

[Novice_user]

Hi All,

I am very new to excel so bear with me please...!

So, I have a set of numbers in two columns

[TABLE="class: cms_table, width: 256"]
<tbody>[TR]
[TD="class: cms_table_xl66, width: 64"][/TD]
[TD="class: cms_table_xl66, width: 64"]A[/TD]
[TD="class: cms_table_xl66, width: 64"]B[/TD]
[TD="class: cms_table_xl66, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]1[/TD]
[TD="class: cms_table_xl69"]a[/TD]
[TD="class: cms_table_xl65, width: 64"]13/3[/TD]
[TD="class: cms_table_xl69"]14/9[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]2[/TD]
[TD="class: cms_table_xl69"]b[/TD]
[TD="class: cms_table_xl65, width: 64"]15/6[/TD]
[TD="class: cms_table_xl69"]16/3[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]3[/TD]
[TD="class: cms_table_xl69"]c[/TD]
[TD="class: cms_table_xl65, width: 64"]10/3[/TD]
[TD="class: cms_table_xl69"]12/3[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]4[/TD]
[TD="class: cms_table_xl69"]d[/TD]
[TD="class: cms_table_xl65, width: 64"]14/6[/TD]
[TD="class: cms_table_xl69"]14/9[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]5[/TD]
[TD="class: cms_table_xl69"]e[/TD]
[TD="class: cms_table_xl65, width: 64"]8/10[/TD]
[TD="class: cms_table_xl69"]6/5[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]6[/TD]
[TD="class: cms_table_xl69"]f[/TD]
[TD="class: cms_table_xl65, width: 64"]10/11[/TD]
[TD="class: cms_table_xl69"]12/3[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]7[/TD]
[TD="class: cms_table_xl69"]g[/TD]
[TD="class: cms_table_xl68, width: 64"]12/0[/TD]
[TD="class: cms_table_xl69"]8/2[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]8[/TD]
[TD="class: cms_table_xl69"]h[/TD]
[TD="class: cms_table_xl65, width: 64"]12/9[/TD]
[TD="class: cms_table_xl69"]14/0[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]9[/TD]
[TD="class: cms_table_xl69"]i[/TD]
[TD="class: cms_table_xl65, width: 64"]9/7[/TD]
[TD="class: cms_table_xl69"]10/7[/TD]
[/TR]
</tbody>[/TABLE]


In column D I want to produce the following results - if the numbers in column C are greater than B then display the number in C, otherwise display column B

I tried using the IF formula =IF(c1>b1,c1,b1)

which was great & worked but when it got to name g the result returned was 8/2 which isn't right.

I looked further down and the results remain inconsistent throughout.

Help please!!

 

[mole999]

Re: Ages in form yy/mm manipulation error - HELP PLEASE

if excel is seeing it as day / month then the zeros won't work, if its seeing as a formula then 12 / 0 which probably will throw a divide by zero error

 

[jondavis1987]

Re: Ages in form yy/mm manipulation error - HELP PLEASE

So, I'm a little confused. I understand that you have it in a year/month format. My guess is 12/0 is supposed to be 2012 January. Why the 0 instead of a 1 for january

 

[jondavis1987]

Re: Ages in form yy/mm manipulation error - HELP PLEASE

So I've played around with it a bit. Your formula is fine. It's the formatting that needs tweeked.

Custom format the cells to yy/m

enter 1 instead of 0 for January

when entering the data type it as 3/1/2013 and it will automatically change to 13/3

doing it this way automatically makes the formula work

[TABLE="width: 192"]


<colgroup><col width="64" style="width: 48pt;" span="3">
<tbody>[TR]

[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]13/3[/TD]

[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]14/9[/TD]

[TD="class: xl66, width: 64, bgcolor: transparent"]14/9[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]15/6[/TD]

[TD="class: xl65, bgcolor: transparent, align: right"]16/3[/TD]

[TD="class: xl66, bgcolor: transparent"]16/3[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]10/3[/TD]

[TD="class: xl65, bgcolor: transparent, align: right"]12/3[/TD]

[TD="class: xl66, bgcolor: transparent"]12/3[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]14/6[/TD]

[TD="class: xl65, bgcolor: transparent, align: right"]14/9[/TD]

[TD="class: xl66, bgcolor: transparent"]14/9[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]08/10[/TD]

[TD="class: xl65, bgcolor: transparent, align: right"]06/5[/TD]

[TD="class: xl66, bgcolor: transparent"]08/10[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]10/11[/TD]

[TD="class: xl65, bgcolor: transparent, align: right"]12/3[/TD]

[TD="class: xl66, bgcolor: transparent"]12/3[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]12/1[/TD]

[TD="class: xl65, bgcolor: transparent, align: right"]08/2[/TD]

[TD="class: xl66, bgcolor: transparent"]12/1[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]12/9[/TD]

[TD="class: xl65, bgcolor: transparent, align: right"]14/1[/TD]

[TD="class: xl66, bgcolor: transparent"]14/1[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]16/9[/TD]

[TD="class: xl65, bgcolor: transparent, align: right"]10/7[/TD]

[TD="class: xl66, bgcolor: transparent"]16/9[/TD]

[/TR]


</tbody>[/TABLE]

 

[jondavis1987]

Re: Ages in form yy/mm manipulation error - HELP PLEASE

I realize this isn't exactly what you wanted because you would have to have at least 1 month in there instead of 0. that's the closest i could come up with. Might not be as elegant of a solution but you could change it to a decimal format. instead of 13 years and three months displayed as 13/3 it could be 13.25 which would still allow your current formula to work.

The other option would be to make it a pure fraction. so it would be 13 3/12

 

[jondavis1987]

Re: Ages in form yy/mm manipulation error - HELP PLEASE

Custom format it to # ??/12

If you do that you will have the years and months old. your formula still works. This way it doesn't try to change 13 3/12 to 13 3/4 and you still get all the relevant data displayed in an easy to read format

 

[Novice_user]

Re: Ages in form yy/mm manipulation error - HELP PLEASE

Hi thanks for all your help.

The yy/mm thing is for ages eg. 12 yrs and 0 months comes written as 12/0 , 8 years and 2 months comes written as 8/2

I am not inputting the data that is how it comes to me. But I have to then produce a spreadsheet with the result of my formula.

Do you think I need to change the original data that comes in before I can apply the formula?

Thanks for the help, very much appreciated.

 

[Novice_user]

Re: Ages in form yy/mm manipulation error - HELP PLEASE

Thanks for the help.. If you have any further ideas please help as I feel I'm drowning in something that I thought would be simple to do!

 

[mole999]

Re: Ages in form yy/mm manipulation error - HELP PLEASE

I had no idea you were doing age as opposed to finding the highest date. If you can get a date of birth then it will always update as a calculation

 

[Novice_user]

Re: Ages in form yy/mm manipulation error - HELP PLEASE

No d.o.b, all I have is the files with the data as is. I just need to produce my results with the data given. I have no control over how the data comes to me. It will always be in the form above.

All help gratefully received.