Extracting just the time portion

  • Thread starter Thread starter wabbleknee
  • Start date Start date
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
 
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)??
Click to expand...

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


Regards
Claus B.
 
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
Click to expand...

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


Regards
Claus B.
 
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.
Click to expand...

"=text(a1, "hh:mm")" may work also
 
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
Click to expand...

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


Regards
Claus B.
 
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
Click to expand...

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


Regards
Claus B.
 
Back
Top