software

xl2003, win xp

In cell I1 I have =IF(ISERROR(VALUE(LEFT(J1,4))),quot;quot;,(VALUE(LEFT(J1,4 ))))

In J1 I have a web link 12:22I want to disply just the time in I1 but is shows as 12:14 instead of 12:22
In the cells below some of the links are like 2:10 F4

and the I cell shows the correct time 2:10

But when the time is shown as AM as in 11:30am

the time shown is 11:03.

Is there any way to fix this so the correct time is shown. I do not mind
the format as long as it is sortable. I am trying to get a list of times in
ascending order.
Any help would be greatly appreciated.
Ta,
Martin

why not just format the cell as time.

I1=J12
and format/cell/number formats. then no manipulation is required

regards--
tony h
------------------------------------------------------------------------
tony h's Profile: www.excelforum.com/member.php...oamp;userid=21074
View this thread: www.excelforum.com/showthread...hreadid=517522Hi Tony,
Some of the cells have other letters in them ie, 2:00 F4, or 11:30 AM and
this mucks thing up when I go to sort them.
Ta,
Martin

quot;tony hquot; gt; wrote in
message ...
gt;
gt; why not just format the cell as time.
gt;
gt; I1=J12
gt; and format/cell/number formats. then no manipulation is required
gt;
gt; regards
gt;
gt;
gt; --
gt; tony h
gt; ------------------------------------------------------------------------
gt; tony h's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21074
gt; View this thread: www.excelforum.com/showthread...hreadid=517522
gt;
You are only taking the leftmost 4 characters instead of 5 - in your
11:30am example, your LEFT function would return 11:3, so the VALUE
function would turn this into 11:03. Try changing the parameter in your
LEFT function to 5 instead of 4. You might also think about appending
quot;:00quot; to your LEFT function inside the VALUE function, so that it
converts properly to hours:mins:sec.

Hope this helps.

PeteHi Pete,
You are right about the 4 instead of 5. And I will try the left function.
Ta,
Martin

quot;Pete_UKquot; gt; wrote in message ups.com...
gt; You are only taking the leftmost 4 characters instead of 5 - in your
gt; 11:30am example, your LEFT function would return 11:3, so the VALUE
gt; function would turn this into 11:03. Try changing the parameter in your
gt; LEFT function to 5 instead of 4. You might also think about appending
gt; quot;:00quot; to your LEFT function inside the VALUE function, so that it
gt; converts properly to hours:mins:sec.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;