Convert existing "time" format to ratio

[dougbert]

Hi all,

This is a "hand me down" worksheet. The author created a column they intended to display as ratios (e.g.; 1:3, 2:16, etc.) However, Excel "knows" that they must have meant these were really "time" format. So, the 1:3 "ratio" is stored in the cell as "1:03:00 AM". The 2:16 "ratio" is stored as "2:16:00 AM".

As you may know, reformatting that column to text won't change the fact that the stored "value" is really still a time. I've tried using Replace ":00 AM" with a null value, but that doesn't work. I tried the LEFT function and that converts it to a long decimal number. I've tried Custom formatting and didn't receive the desired text (1:3, 2:16, etc.).

I know how to make the ratio display correctly if I had created the worksheet, but need to convert someone else's misunderstanding of Excel into what was originally intended.

I searched the forums before submitting this thread and never found any suggestions that worked for my existing "data".

I'll provide sample data if my description isn't clearly understood.

Thanks for any bright ideas!!!

-dougbert

 

[Norie]

How about extracting the first part of the ratio with HOUR and the wnd with MINUTE?

Something like this perhaps.

=HOUR(A1)&":"&MINUTE(A1)

Now that should give you the ratio as text, so I don't know if it's any use for calculation - I'll need to bone up on my Excel ratios I think.

 

[Special-K99]

=HOUR(A1) will give you the first figure
=MINUTE(A1) will give you the second figure

=HOUR(A1)&":"&MINUTE(A1)

will give you ratio as you want it displayed

 

[c_m_s_jr]

If you want something not as simple because I forgot about HOUR and MINUTE:

=IF(LEFT(TEXT(A2,"hh:mm"),1)="0",MID(TEXT(A2,"hh:mm"),2,1),LEFT(TEXT(A2,"hh:mm"),2))&":"&IF(MID(TEXT(A2,"hh:mm:ss"),4,1)="0",MID(TEXT(A2,"hh:mm:ss"),5,1),MID(TEXT(A2,"hh:mm:ss"),4,2))

 

[dougbert]

Thanks to everyone for the bright ideas! I tried Norie's first and it did exactly what I needed! Thanks Norie!

Just like c_m_s_jr, I had also forgotten about the HOUR and MINUTE functions for manipulating TIME!