remove the date, leave the time

  • Thread starter Thread starter Matrix
  • Start date Start date
M

Matrix

Hi all. I have a problem. I have a data set that must be copied and pasted
into Excel and one of the columns contains a date and time in this format:

3/31/2010 4:18:00 PM (ET)

Now I can do a simple search and replace to get rid of the (ET) part, but
what I want to do is this. If the data is in cell ‘A1’, I want to have cell
C1 show JUST the time in a 24hr format. Is there a way to strip the date data
out and convert the time to 24hr format? Thanx!
 
Lets assume that the data is in text format (because of the ET)

In B1:
=LEFT(TRIM(A1),LEN(TRIM(A1))-5)
to display:
3/31/2010 4:18:00 PM
this removes any extra spaces and the (ET)

In C1:
=TIMEVALUE(MID(B1,FIND(" ",B1)+1,256))
and format as time 13:30:55
to display:
16:18:00
The formula discards the date and converts to a true time.
 
That worked great! I can use that, but just wondering if there was a way to
do that without having to use the formula in B1 then converting again. In
other words, is it possible to go from A1 to C1 without needing the formula
in B1 or combining the formulas into one formula? Thanx again!
 
Awesome! That worked great too! I love having several solutions to problems.
Thanks all!!!
 
You can use a tiny trick! Using Find/Replace, edit C1. Just replace "B1"
with the formula contained in B1. This will create a large formula that you
can copy downwards.

Personally, I like short formulas and don't mind using "helper columns".
However we don't always get that option!
 
T. wrote on Sat, 1 May 2010 11:00:53 -0400:
=--SUBSTITUTE(MID(A1,FIND(" ",A1)+1,20)," (ET)","")
Click to expand...
Format as hh:mm
Click to expand...
As far as I can tell, after you remove the "(ET)", a simple Format
cells> Time will work.

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not
 
Yes, but the true underlying value of the cell will still contain the date.

With the date included the cell value is 40268.6791666667.

With just the time the cell value is 0.6791666667.

So, going by the OP's subject line: remove the date, leave the time
 
T. wrote on Sat, 1 May 2010 13:43:30 -0400:
With the date included the cell value is 40268.6791666667.
Click to expand...
With just the time the cell value is 0.6791666667.
Click to expand...
So, going by the OP's subject line: remove the date, leave the
time
Click to expand...

I quite agree but I guess it depends on what you want, display of time
or the actual hours and minutes.


--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not
 
Back
Top