TIme

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I hope this is an easy question, even though I can't figure it out. :-)

If I have time in the format of:

24:13:03 HH:MM:SS

and I need it in minutes only.

How can I convert that? I have Excel 2003.
 
Then your values are text.
For future posts, please refrain from multiposting,
post to one NG or crosspost..
 
Hi
this sound like the time value is not really a time value but a text
entry.
Try the function
=ISNUMBER(A1)
does this return FALSE. If yes you don't have a time but a text value.
Look for formats or invisible characters
 
Your correct. It is false. I also mis-stated. The format that the time is
in is actually 000.00. Then only the occasional has 000.00.00 and has
seconds in it.

I can't really just format the cells in time as I don't think the result
would be consistant.

Thoughts?
 
Hi
you may explain what your current text format represents. what is the
first part what the latter one.
Also you may consider using a real time format. Why should your results
not be consistant?
 
As I look at it, maybe I am not too bad off. It is a listing of time spent
on a cell phone. It is roughly 26000 rows long.

The format goes from 001.56 to represent 1 hour and 56 minutes. Then they
go to 45.32.00 to reperesent 45 hours and 32 minutes, zero seconds.

If I just format all the cells to time hh:mm:ss and then use the =Round do
you think that would be consistant?
 
Hi
formating won't work. (Try it and you'll notice the values won't
change). I assume that these values are stored as text. Try the
following formula in a helper column (e.g. B1)
=IF(ISNUMBER(FIND(".",A1,FIND(".",A1)+1)),TIME(--LEFT(A1,FIND(".",A1)-1
),--MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A15)-1),
--MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,10)),TIME((--LEFT(A1,FIND(".",A1
)-1),--MID(A1,FIND(".",A1)+1,10),0))

and copy this formula down for all rows. This should give you a correct
time value (format the cells as hh:mm:ss). After this copy this helper
column and insert it again as 'Edit - Paste Special - Values'
 
Back
Top