Excel text to columns converting PM to AM
Closed
vjk
-
Oct 2, 2011 at 12:50 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 3, 2011 at 10:18 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 3, 2011 at 10:18 PM
Hello,
I have a spreadsheet that has 9/11/2011 1:33:03 PM in the display bar, 09/11/11 13:33:03 PM in the cell. When I perform text to columns to separate the time from the date, the 13:33:03 PM converts to 1:33:03 AM in the destination cell no matter how I pre-format the destination cell.
I would like the destination cell to read 13:33:03 PM.
Anyone know how to fix this?
Thank you.
I have a spreadsheet that has 9/11/2011 1:33:03 PM in the display bar, 09/11/11 13:33:03 PM in the cell. When I perform text to columns to separate the time from the date, the 13:33:03 PM converts to 1:33:03 AM in the destination cell no matter how I pre-format the destination cell.
I would like the destination cell to read 13:33:03 PM.
Anyone know how to fix this?
Thank you.
Related:
- Excel text to columns converting PM to AM
- Convert number to words in excel - Guide
- Excel mod apk for pc - Download - Spreadsheets
- Convert m3u to mp3 - Guide
- How to take screenshot in excel - Guide
- How to change date format in excel - Guide
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Oct 3, 2011 at 06:00 AM
Oct 3, 2011 at 06:00 AM
not clear what you want. there is nothing like "13:03:03 PM" there is either
13:03:03
or
01:03:03 PM
why do you want text to columns.
what exactly do you want suppose A1 has the above entry
13:03:03
or
01:03:03 PM
why do you want text to columns.
what exactly do you want suppose A1 has the above entry
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Oct 3, 2011 at 10:18 PM
Oct 3, 2011 at 10:18 PM
it all depends upon for what purpose you want this.
one simple way to only see (not to manipulate)
copy A1 to B1
CUSTOM format A1 as m/d/yyyy
time format B1 as time something like 13:30:55
suppose you want to manipulate
in A2 type this formula
=INT(A1)
in B2 type this formula
=A1-A2
format B2 as time 13:30:55(something like this in your version of excel
is this what you want
one simple way to only see (not to manipulate)
copy A1 to B1
CUSTOM format A1 as m/d/yyyy
time format B1 as time something like 13:30:55
suppose you want to manipulate
in A2 type this formula
=INT(A1)
in B2 type this formula
=A1-A2
format B2 as time 13:30:55(something like this in your version of excel
is this what you want
Oct 3, 2011 at 09:51 AM
What I want to end up with is the time separate from the date without having to change it to text format. When I do text to columns, the result is that 13:03:03 ends ups as 01:03:03 AM in the destination cell. The format in A1 (original cell) is custom [$-10409] mm/dd/yy hh:mm:ss.
I've tried formatting the destination cell to various time formats, but can't get anything but 01:03:03 AM unless I change it to text in the text to columns dialog. A VBA solution would be acceptable, just don't know what that would be?
Thank you.