TIme

[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.

 

[Guest]

It just came up with the ole #VALUE. I typed it in exactly.

 

[Frank Kabel]

Hi
use
=MINUTE(A1)
if A1 stores your time

 

[Peo Sjoblom]

Then your values are text.
For future posts, please refrain from multiposting,
post to one NG or crosspost..

 

[Frank Kabel]

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

 

[Guest]

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?

 

[Frank Kabel]

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?

 

[Guest]

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?

 

[Frank Kabel]

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'