working with entries in XX:XX format

  • Thread starter Thread starter Microsoft
  • Start date Start date
M

Microsoft

I have entries that are pasted from HTML they appear as XX:XX ie; 31:24.
They are NOT
TIMES! I can not alter the data before it gets to excell ,and excell wants
to make them times of day that are incomprehensible!

If i reformat the cells after the text is there, the data gets converted to
microsofft geekspeak time.

I format the cell for text , then even after repasting, it still sees it as
a
time of day. WHY IS THERE NO FIX FOR THIS LIMITATION!!!!!!!!

SOMETIMES if I paste as Unicode it works, but not consistently


PLEASE I MUST HAVE THIS WORKING . MY last post appears to have disappeared
off this board!

I am replacing the ":" with a "." and using a roundup function, but the data
comes
with a ":" seperator, and can not be changed before pastiing

Thanks in advance.,

Mark
 
Convert it. IN B1 put
=TIME(LEFT(A1,FIND(":",A1)-1),RIGHT(A1,2),0)
make sure B1 et al are formatted as [h]:mm:ss for >24 hours
 
You can try this formula
assumed your 31:24 is in A2
and this formula will give you 31.24 (format the cell as number)
=INT(A2*24)+(MOD(A2*24,1))*0.6
HTH
Cecil
 
Back
Top