Re: TIme

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 22:19:21 +0200

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' 

--
Regards
Frank Kabel
Frankfurt, Germany
Here@There.com wrote:
> 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" <frank.kabel@freenet.de> wrote in message
> news:O8YCF$wJEHA.3104@TK2MSFTNGP10.phx.gbl...
>> 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?
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>>
>> Here@There.com wrote:
>>> 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" <frank.kabel@freenet.de> wrote in message
>>> news:OTU$zywJEHA.952@TK2MSFTNGP12.phx.gbl...
>>>> 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
>>>>
>>>> --
>>>> Regards
>>>> Frank Kabel
>>>> Frankfurt, Germany
>>>>
>>>>
>>>> Here@There.com wrote:
>>>>> It just came up with the ole #VALUE.  I typed it in exactly.
>>>>>
>>>>>
>>>>> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
>>>>> news:%23K5IpswJEHA.3040@TK2MSFTNGP09.phx.gbl...
>>>>>> Hi
>>>>>> use
>>>>>> =MINUTE(A1)
>>>>>> if A1 stores your time
>>>>>>
>>>>>> --
>>>>>> Regards
>>>>>> Frank Kabel
>>>>>> Frankfurt, Germany
>>>>>>
>>>>>>
>>>>>> Here@There.com wrote:
>>>>>>> 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.