Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert date/time to text
An automatically generated report has a column of dates/times in the format:
mm/dd/yyyy hh:mm:ss AM We want to be able to sort this column ONLY by the times. We don't care about the date. When we change the format of the cells to contain only the time, the sort sorts by the entire date/time (because we never really changed the contents of the cells). So, basically what I want to do is 'strip out' the date information, leaving only the time information. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert date/time to text
Hilight the column
Click on Data Text to columns Clicked next Tick "Space" Click next Click Finish Sort as required x "Pam" wrote: An automatically generated report has a column of dates/times in the format: mm/dd/yyyy hh:mm:ss AM We want to be able to sort this column ONLY by the times. We don't care about the date. When we change the format of the cells to contain only the time, the sort sorts by the entire date/time (because we never really changed the contents of the cells). So, basically what I want to do is 'strip out' the date information, leaving only the time information. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert date/time to text
Hi
You could use a helper column with =MOD(A2,1) in it and sort on that. Hope this helps. Andy. "Pam" wrote in message ... An automatically generated report has a column of dates/times in the format: mm/dd/yyyy hh:mm:ss AM We want to be able to sort this column ONLY by the times. We don't care about the date. When we change the format of the cells to contain only the time, the sort sorts by the entire date/time (because we never really changed the contents of the cells). So, basically what I want to do is 'strip out' the date information, leaving only the time information. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert date/time to text
I had great luck with separating the date/time using Text/Columns. (Thanks!)
But now my problem is that the time is 1:00:00 and to get the sort to work correctly I would need it to display 01:00:00. I need the leading "0", so all of the text I am sorting has the same amount of characters. Any ideas on that?? "Sarah_Lecturer" wrote: Hilight the column Click on Data Text to columns Clicked next Tick "Space" Click next Click Finish Sort as required x "Pam" wrote: An automatically generated report has a column of dates/times in the format: mm/dd/yyyy hh:mm:ss AM We want to be able to sort this column ONLY by the times. We don't care about the date. When we change the format of the cells to contain only the time, the sort sorts by the entire date/time (because we never really changed the contents of the cells). So, basically what I want to do is 'strip out' the date information, leaving only the time information. Any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert date/time to text
"Pam" wrote in message
... An automatically generated report has a column of dates/times in the format: mm/dd/yyyy hh:mm:ss AM We want to be able to sort this column ONLY by the times. We don't care about the date. When we change the format of the cells to contain only the time, the sort sorts by the entire date/time (because we never really changed the contents of the cells). So, basically what I want to do is 'strip out' the date information, leaving only the time information. Any ideas? =MOD(A1,1) -- David Biddulph |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert date/time to text
"Pam" wrote in message
... "Sarah_Lecturer" wrote: "Pam" wrote: An automatically generated report has a column of dates/times in the format: mm/dd/yyyy hh:mm:ss AM We want to be able to sort this column ONLY by the times. We don't care about the date. When we change the format of the cells to contain only the time, the sort sorts by the entire date/time (because we never really changed the contents of the cells). So, basically what I want to do is 'strip out' the date information, leaving only the time information. Any ideas? Hilight the column Click on Data Text to columns Clicked next Tick "Space" Click next Click Finish Sort as required x I had great luck with separating the date/time using Text/Columns. (Thanks!) But now my problem is that the time is 1:00:00 and to get the sort to work correctly I would need it to display 01:00:00. I need the leading "0", so all of the text I am sorting has the same amount of characters. Any ideas on that?? Custom format hh:mm:ss -- David Biddulph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert excel file into ASCII text file with alignment? | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Convert excel file to flat text file | Excel Discussion (Misc queries) | |||
How to convert columns of data to one column of text | Excel Worksheet Functions | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) |