Extracting just the time portion

W

wabbleknee

I have not had much problem with this type in the past, but this one seems
to want to be difficult. I get an excel sheet mailed to me that shows an
event time. i.e. "9/24/2013 4:10:00 PM". As received, the custom format
now is "mm/dd/yyyy hh:mm:ss AM/PM"
What I want to extract is only time to be shown as 16:10. What seemed to
work in the past is to do a "text to columns" and then to make one column =
4:10:00 PM. Then format as 13:30, but getting the 4:10:00 PM in that
format requires a lot of extra steps beforehand.
Would appreciate a more simple conversion if possible. (A text to columns
attempt makes 4:10:00 PM show up as 4:10:00 AM in first cell, PM shows up in
the next cell)?? Tx
 
C

Claus Busch

Hi,

Am Wed, 25 Sep 2013 14:26:57 -0400 schrieb wabbleknee:
I have not had much problem with this type in the past, but this one seems
to want to be difficult. I get an excel sheet mailed to me that shows an
event time. i.e. "9/24/2013 4:10:00 PM". As received, the custom format
now is "mm/dd/yyyy hh:mm:ss AM/PM"
What I want to extract is only time to be shown as 16:10. What seemed to
work in the past is to do a "text to columns" and then to make one column =
4:10:00 PM. Then format as 13:30, but getting the 4:10:00 PM in that
format requires a lot of extra steps beforehand.
Would appreciate a more simple conversion if possible. (A text to columns
attempt makes 4:10:00 PM show up as 4:10:00 AM in first cell, PM shows up in
the next cell)??

your values in column A
Then in B1:
=MOD(A1,1)
and format h:mm


Regards
Claus B.
 
C

Claus Busch

Hi again,

Am Wed, 25 Sep 2013 20:37:40 +0200 schrieb Claus Busch:
I get an excel sheet mailed to me that shows an
event time. i.e. "9/24/2013 4:10:00 PM". As received, the custom format

or first format your column custom as mm/dd/yyyy h:mm:ss and then run
TextToColumns


Regards
Claus B.
 
S

shanermuls

Claus said:
Hi again,

Am Wed, 25 Sep 2013 20:37:40 +0200 schrieb Claus Busch:
-

or first format your column custom as mm/dd/yyyy h:mm:ss and then run
TextToColumns


Regards
Claus B.

"=text(a1, "hh:mm")" may work als
 
W

wabbleknee

That seems to work....Tx

"shanermuls" wrote in message
Hi again,

Am Wed, 25 Sep 2013 20:37:40 +0200 schrieb Claus Busch:
-

or first format your column custom as mm/dd/yyyy h:mm:ss and then run
TextToColumns


Regards
Claus B.

"=text(a1, "hh:mm")" may work also
 
W

wabbleknee

Tx Claus. The first one works great for me, your 2nd suggestion shows
2:28 PM when the input was 14:28 Tx

"Claus Busch" wrote in message
Hi again,

Am Wed, 25 Sep 2013 20:37:40 +0200 schrieb Claus Busch:
I get an excel sheet mailed to me that shows an
event time. i.e. "9/24/2013 4:10:00 PM". As received, the custom
format

or first format your column custom as mm/dd/yyyy h:mm:ss and then run
TextToColumns


Regards
Claus B.
 
C

Claus Busch

Hi,

Am Mon, 30 Sep 2013 20:36:11 -0400 schrieb wabbleknee:
Tx Claus. The first one works great for me, your 2nd suggestion shows
2:28 PM when the input was 14:28 Tx

what you see is the format. The value is correct and the format is easy
to change


Regards
Claus B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top