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.

--
Gary''s Student - gsnu201001


"Matrix" wrote:

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!

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!

"Gary''s Student" wrote:

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.

--
Gary''s Student - gsnu201001


"Matrix" wrote:

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!

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!
--
Gary''s Student - gsnu201001


"Matrix" wrote:

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!

"Gary''s Student" wrote:

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.

--
Gary''s Student - gsnu201001


"Matrix" wrote:

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!

Try this...

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

Format as hh:mm

--
Biff
Microsoft Excel MVP


"Matrix" wrote in message
...
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!

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

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

Format as hh:mm

--
Biff
Microsoft Excel MVP

"Matrix" wrote in message
...
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!

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

--
Biff
Microsoft Excel MVP


"James Silverton" wrote in message
...
T. wrote on Sat, 1 May 2010 11:00:53 -0400:

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

Format as hh:mm

--
Biff
Microsoft Excel MVP

"Matrix" wrote in message
...
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!

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

--
Biff
Microsoft Excel MVP

"James Silverton" wrote in message
As far as I can tell, after you remove the "(ET)", a simple
Format cells Time will work.


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