Convert years:months to months

[Jen]

Hello- I am hoping to use Excel to convert data that inputted in a
years:months format to just months. For example, I want to translate
5:3 to 63 months. I'm running into one hiccup: Excel views the
inputted data as time and is translating it into 5:30 AM, making
calculations challenging Does anyone have any tips?

 

[Bernard Liengme]

=HOUR(A1)*12+MINUTE(A1)/10
you may need to format the cell a General as Excel can be overhelpful and
use a time format
best wishes

 

[Rick Rothstein]

Give this a try...

=12*LEFT(A1,FIND(":",A1)-1)+MID(A1,FIND(":",A1)+1,9)

 

[Rick Rothstein]

You accidentally included a "divide by 10" in your formula... removing it
makes your formula work correctly.

 

[Harald Staff]

OP says 5:3 is converted to 5:30, not 5:03.
Not so here, but what do I know.

Best wishes Harald

 

[Jen]

Thanks for all the replies. Your formula worked for the most part,
Bernard (=HOUR(A1)*12+MINUTE(A1)/10) except that, for example, it
generated 60.3 instead of 63 when computing 5:03 in months. I think I
may have led you astray b/c I accidentally mistyped that Excel
translates 5:3 to 5:30-- I meant to say it translates 5:03 to 5:03
AM. Would you be able to send me a modified formula that would
account for this? thanks so very much. Jen

 

[Jen]

Nevermind! I figured it out on my own. Deleting the /10 worked once
I switched the cell format back to general (for some reason Excel
changed it back to Time).

thank you again, everyone.

 

[Bernard Liengme]

I assumed OP had 5:30 not 5:03 !
cheers
Bernard

You accidentally included a "divide by 10" in your formula... removing it
makes your formula work correctly.

 

[Rick Rothstein]

Yes, I see that now.

--
Rick (MVP - Excel)

I assumed OP had 5:30 not 5:03 !
cheers
Bernard