remove the date, leave the time

[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!

 

[Gary''s Student]

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.

 

[broro183]

hi Matrix,

If your data cells are recognised as being dates when the " (ET)" par
is removed, you could try either of the following approaches which ar
slightly different & have different impacts if the cells are used i
subsequent formulae.

1) To remove the date portion of the value, type the below & then forma
the cells in 24hr format:
type =B5-INT(B5)

or
2) To keep the whole date value but just display the time part of it
type the below
=TEXT(B5,"HH:MM")

(where cell B5 contains your date)

hth
Rob


Hi all. I have a problem. I have a data set that must be copied an
pasted

into Excel and one of the columns contains a date and time in thi 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 t have cell
C1 show JUST the time in a 24hr format. Is there a way to strip the dat data
out and convert the time to 24hr format? Thanx!

 

[Matrix]

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!

 

[Matrix]

Awesome! That worked great too! I love having several solutions to problems.
Thanks all!!!

 

[Gary''s Student]

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. Valko]

Try this...

=--SUBSTITUTE(MID(A1,FIND(" ",A1)+1,20)," (ET)","")

Format as hh:mm

 

[James Silverton]

T. wrote on Sat, 1 May 2010 11:00:53 -0400:

=--SUBSTITUTE(MID(A1,FIND(" ",A1)+1,20)," (ET)","")

Format as hh:mm

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

 

[T. Valko]

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

 

[James Silverton]

T. wrote on Sat, 1 May 2010 13:43:30 -0400:

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

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