Please help: Extract some information from a cell
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
|