Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting just the time portion
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting just the time portion
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting just the time portion
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 now is "mm/dd/yyyy hh:mm:ss AM/PM" or first format your column custom as mm/dd/yyyy h:mm:ss and then run TextToColumns Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
|
|||
|
|||
Quote:
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting just the time portion
That seems to work....Tx
"shanermuls" wrote in message ... Claus Busch;1614046 Wrote: 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 - now is "mm/dd/yyyy hh:mm:ss AM/PM"-- or first format your column custom as mm/dd/yyyy h:mm:ss and then run TextToColumns Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 "=text(a1, "hh:mm")" may work also -- shanermuls |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting just the time portion
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 now is "mm/dd/yyyy hh:mm:ss AM/PM" or first format your column custom as mm/dd/yyyy h:mm:ss and then run TextToColumns Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting just the time portion
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove time portion from date on Excel 2007 chart | Charts and Charting in Excel | |||
MS Query and Time Portion of Timestamp | Excel Discussion (Misc queries) | |||
Extracting portion of filename | Excel Programming | |||
Buttons in lower portion of workbook appear in upper portion | Excel Programming | |||
Extracting A Portion Of A String | Excel Programming |