I have a cell that contains both a date and a time. I need to extract
only the time and put that into a new cell. Example: cell N1 reads
"01/19/06 12:53:32 PM". I only want to extract the time to put into
cell S1. What formula do I use to do this? THANK YOU!


--
Bobsus5
------------------------------------------------------------------------
Bobsus5's Profile: http://www.excelforum.com/member.php...o&userid=32210
View this thread: http://www.excelforum.com/showthread...hreadid=519590

I have a suggestion. I hope it may helps. Would you mind to try this ?
Put "=TIME(HOUR(N1),MINUTE(N1),)" in Cell S1.

"Bobsus5" wrote:


I have a cell that contains both a date and a time. I need to extract
only the time and put that into a new cell. Example: cell N1 reads
"01/19/06 12:53:32 PM". I only want to extract the time to put into
cell S1. What formula do I use to do this? THANK YOU!


--
Bobsus5
------------------------------------------------------------------------
Bobsus5's Profile: http://www.excelforum.com/member.php...o&userid=32210
View this thread: http://www.excelforum.com/showthread...hreadid=519590

On Mon, 6 Mar 2006 19:58:40 -0600, Bobsus5
wrote:


I have a cell that contains both a date and a time. I need to extract
only the time and put that into a new cell. Example: cell N1 reads
"01/19/06 12:53:32 PM". I only want to extract the time to put into
cell S1. What formula do I use to do this? THANK YOU!

You must understand that Excel stores dates and times as serial numbers with
1 = 1/1/1900. Times are stored as fractions of a day. So your string, if it
is a true Excel date/time, is really the value 38,736.5371759259

If the data is a true Excel time, and not a text string, then you can:

1. Just display the time by formatting the cell to show time.
2. The time portion of the value is the decimal portion, so you could use
=MOD(N1,1) to just have the time value, or decimal portion of the value. You
would format the cell as time, anyway.

If the value is a text string, then you could extract the portion after the
first <space with the formula:

=MID(N1,FIND(" ",N1)+1,255)


--ron