Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Splitting Column Data into 2 fields
Hi All,
I have a column that recieves date and time data in the following format: 01/03/2006 16:00:00 (mm/dd/yyyy hh:mm:ss) It is causing me some troubles, namely in calculating elapsed time between 2 data points because Excel doesn't seem to like the format the data comes in and recognizes it as text instead of a date. Is there a way to split this into 2 columns, one for date the other for time, (Sort of a reverse concatate?) and is this my best course of action or is there a more efficient solution to this? Thanks kindly! Kevin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Splitting Column Data into 2 fields
You could convert the text to a value using the text function as
follows: =TEXT(A2,"mm/dd/yyyy hh:mm:ss") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Splitting Column Data into 2 fields
Thanks Edessary, is it possible to make this conditional, I assume through an
IF statement, so that if the reference cell is not Null, the Text is converted to a value? I ask because the Imported Date/Time stamp comming over as a Text field is of variable length, and I'd like to make the converted column expand or collapse with the number of reference cells imported into the column. I'm not sure that made sense or not... Thanks! Kevin " wrote: You could convert the text to a value using the text function as follows: =TEXT(A2,"mm/dd/yyyy hh:mm:ss") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Splitting Column Data into 2 fields
Hi!
Try this: Try it on a single cell first to see if it will work! Select one of the faux date cells Goto DataText to ColumnsNextNext Select Date and MDY format from the drop down Finish Biff "Kevin" wrote in message ... Hi All, I have a column that recieves date and time data in the following format: 01/03/2006 16:00:00 (mm/dd/yyyy hh:mm:ss) It is causing me some troubles, namely in calculating elapsed time between 2 data points because Excel doesn't seem to like the format the data comes in and recognizes it as text instead of a date. Is there a way to split this into 2 columns, one for date the other for time, (Sort of a reverse concatate?) and is this my best course of action or is there a more efficient solution to this? Thanks kindly! Kevin |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Splitting Column Data into 2 fields
If I understand you correctly try the following:
=IF(A2="","",TEXT(A2,"mm/dd/yyyy hh:mm:ss")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |