Text to Columns issue with Date & Time

I exported a report from our problem tracking system into Excel, and th
date and time show up in one cell together with a space between.  Ex
10/25/04  14:56.
When I try to split them into two columns using the 'Text to Columns
feature, (space delimited) it changes it to three columns that loo
like this: 
A) 10/25/04  0:00    B) 1/0/00  2:56    C)  PM

I don't want the 0:00 in column A, or the 1/0/00 in column B, and m
preference is that it would leave the time in a 24 hour format
although that's not critical.  Ideal would be  A) 10/25/04     B
14:56.

I tried formatting the column as 'Text' before I did text to columns
but it changed the data to 38285.62267.  I also tried choosing 'Custom
from the Number tab in formatting and set it to a type of 'm/d/yyy
h:mm', but I got the same result when I did text to columns.

Any ideas on how to make this work

--
C Andrew
-----------------------------------------------------------------------
C Andrews's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1625
View this thread: http://www.excelforum.com/showthread.php?threadid=27665

0
11/9/2004 4:13:30 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
668 Views

Similar Articles

[PageSpeed] 7

Hi

as excel stores date & time as serial numbers (e.g. the 38285.62267 that you 
saw when you formatted it to text) you can't use data / text to columns to 
split it up (AFAIK)
if you just want to display the date in one column and the time in another 
you can copy to date across to these two cells and format the first only to 
show the date and the second only to show the time (format / cells - play 
with the settings under date) ... this will not change the value from 
38285.62267 but "hide" the bit you don't want.

If, for some reason, you need to change the values then you'll need to use 
the following formula (with your data in A1)
=TEXT(INT(A1),"mm/dd/yy")
=TEXT(MOD(A1,1),"hh:mm")
(note this changes them to text, you can leave the text function off, and 
then use the format option to hide the bit you don't want)

Hope this helps
Cheers
JulieD


"C Andrews" <C.Andrews.1fgp3b@excelforum-nospam.com> wrote in message 
news:C.Andrews.1fgp3b@excelforum-nospam.com...
>
> I exported a report from our problem tracking system into Excel, and the
> date and time show up in one cell together with a space between.  Ex.
> 10/25/04  14:56.
> When I try to split them into two columns using the 'Text to Columns'
> feature, (space delimited) it changes it to three columns that look
> like this:
> A) 10/25/04  0:00    B) 1/0/00  2:56    C)  PM
>
> I don't want the 0:00 in column A, or the 1/0/00 in column B, and my
> preference is that it would leave the time in a 24 hour format,
> although that's not critical.  Ideal would be  A) 10/25/04     B)
> 14:56.
>
> I tried formatting the column as 'Text' before I did text to columns,
> but it changed the data to 38285.62267.  I also tried choosing 'Custom'
> from the Number tab in formatting and set it to a type of 'm/d/yyyy
> h:mm', but I got the same result when I did text to columns.
>
> Any ideas on how to make this work?
>
>
> -- 
> C Andrews
> ------------------------------------------------------------------------
> C Andrews's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=16258
> View this thread: http://www.excelforum.com/showthread.php?threadid=276656
> 


0
JulieD1 (2295)
11/9/2004 4:26:23 PM
hi,
try text to columns fixed width. this way you can split 
the datatime into 2 columns, date and time.
you may have to reformat both columns.
regards

>-----Original Message-----
>
>I exported a report from our problem tracking system into 
Excel, and the
>date and time show up in one cell together with a space 
between.  Ex.
>10/25/04  14:56.
>When I try to split them into two columns using the 'Text 
to Columns'
>feature, (space delimited) it changes it to three columns 
that look
>like this: 
>A) 10/25/04  0:00    B) 1/0/00  2:56    C)  PM
>
>I don't want the 0:00 in column A, or the 1/0/00 in 
column B, and my
>preference is that it would leave the time in a 24 hour 
format,
>although that's not critical.  Ideal would be  A) 
10/25/04     B)
>14:56.
>
>I tried formatting the column as 'Text' before I did text 
to columns,
>but it changed the data to 38285.62267.  I also tried 
choosing 'Custom'
>from the Number tab in formatting and set it to a type 
of 'm/d/yyyy
>h:mm', but I got the same result when I did text to 
columns.
>
>Any ideas on how to make this work?
>
>
>-- 
>C Andrews
>----------------------------------------------------------
--------------
>C Andrews's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=16258
>View this thread: 
http://www.excelforum.com/showthread.php?threadid=276656
>
>.
>
0
anonymous (74722)
11/9/2004 4:29:37 PM
Try this:

In *addition* to checking <space> as the delimiter, also check "other",
And in the box enter
<Alt>0160
using the numbers from the num keypad, *not* the numbers under the function
keys.
You will *not* see anything in the box, since this is the code for a
"non-breaking" space.

Now look at the "Data Preview" window (which is WYSIWYG), and see what's
displayed there.
-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"C Andrews" <C.Andrews.1fgp3b@excelforum-nospam.com> wrote in message
news:C.Andrews.1fgp3b@excelforum-nospam.com...

I exported a report from our problem tracking system into Excel, and the
date and time show up in one cell together with a space between.  Ex.
10/25/04  14:56.
When I try to split them into two columns using the 'Text to Columns'
feature, (space delimited) it changes it to three columns that look
like this:
A) 10/25/04  0:00    B) 1/0/00  2:56    C)  PM

I don't want the 0:00 in column A, or the 1/0/00 in column B, and my
preference is that it would leave the time in a 24 hour format,
although that's not critical.  Ideal would be  A) 10/25/04     B)
14:56.

I tried formatting the column as 'Text' before I did text to columns,
but it changed the data to 38285.62267.  I also tried choosing 'Custom'
from the Number tab in formatting and set it to a type of 'm/d/yyyy
h:mm', but I got the same result when I did text to columns.

Any ideas on how to make this work?


-- 
C Andrews
------------------------------------------------------------------------
C Andrews's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=16258
View this thread: http://www.excelforum.com/showthread.php?threadid=276656


0
ragdyer1 (4060)
11/9/2004 4:39:02 PM
Reply:

Similar Artilces:

How do you change the rows to columns and columns to rows.
Ok, I am stumped on how to do this? I have a spreadsheed that has Dates in the A column and Data in the row. I want to make is so, the data is in the A column and Dates ru accross in the 1 column. How can I do this? Sound easy, but I am stumped??? :eek -- cc4digita ----------------------------------------------------------------------- cc4digital's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2656 View this thread: http://www.excelforum.com/showthread.php?threadid=39843 Highlight your data and copy it into the clipboard. Move to an open spot in the sprea...

How do I have Column Headers Repeat Autmoatically on Every Page?
I just want to know how to have header automatically repeat on every page, without having to insert row, copy and paste.... Hi file - Pagesetup - Sheets and define the repeating rows "kristiatscaor" wrote: > I just want to know how to have header automatically repeat on every page, > without having to insert row, copy and paste.... ...

How do I add vertical lines to separate columns in Outlook 2003 #2
In Outlook 2000, all columns were separated by a gray vertical line. I can't seem to set this up in Outlook 2003. Any help would be appreciated ARe you using Word as the message editor? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, mschwartz asked: | In Outlook 2000, all columns were separated by a gray | vertical line. I can't seem to set this up in O...

Add SafePay footer record for date and account number
Union Bank of California has a Positive Pay format that requests a footer record for each day and account number. So if you transmit checks issued on two dates for a single account, the SafePay file would have two footer records--one for each date. Currently, I am only able to create a footer by account, totalling all checks issued for that account (regardless of date), and attributing that total to the Issue Date in the footer record. Union Bank reads the issue date on the footer, and sees that the checks issued on that date do not match the footer total, causing them to consider th...

Percentage Column Charts
HI!! I think I am thinking too much about this and it is easier than it seems, but I cant figure it out! hopefully someone can help. So I need to create a chart with 2 column...plan vs. actual. But I need each of these bar columns to be separted by percentages of the categories that make them up. EX: I need to make a comparison on how big of a percentage the Labor part of the Budget was when they planned and how it differs from the actual execution. Thanks for your help! Stacked Column Graph Set up as labor code ACT PLAN a 1 1 b 3 2 c 5 3 d ...

excel margin issues on landscape
When I print a spreadsheet I cant get it to print to the full page - it prints smaller unlike older excel program. Also when i set the margins for a spreadsheet the left hand margin wont move over to the edge of page like right hand side? In Page Setup: If you are using the Scaling option to print to a certain number of pages wide by pages tall and/or you are using the columns to repeat at left, try: - clearing the number of pages tall value (so that it is blank), and/or - if you are printing to one page wide, remove the columns to repeat at left Simon "Peter MB" wrote: >...

how to select multiple text boxes in excel for formatting
I am trying to select multiple text boxes for formatting the font but seem unable to select all of them other than to click on each one individually. Is there an easy way to select all of the text boxes at once? To select multiple objects on the sheet -- Click on one object Hold the Ctrl key, and click on additional objects To select all the objects on the sheet -- Choose Edit>Go To, click Special Select Objects, click OK Or, to work with specific objects, you can add the 'Select Multiple Objects' tool to one of your toolbars: Choose Tools>Customize Select the Commands tab...

separate columns
Hi, I am having a little problem and I was wondering if any of you coul help me with it. I have two columns, A (Definitions) and B is empty. Column A has several words in a single cell, I was wondering if ther is a way to put only the first word from cell A1, for example, in cel B1. Thanks. Regards, Marco -- Message posted from http://www.ExcelForum.com Hi Marcos! Try: =LEFT(A1,FIND(" ",A1)-1) -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "marksuza >" <<marksuza.16lqli@excelforum-nospam.com> wrote in message news:ma...

How to make a column of formulas all ROUND
I created a spreadsheet in which I have a column of formulas. Most of these fomulas are simply pulling a single number off another sheet. I want to make all the formulas ROUND versions of the existing formula without having to go into each cell and making the change. They are not in order to which I can just make the first fomula a ROUND fomula and copy down. So, is there a way to select a range of cells and make the existing fomulas all ROUND versions? Thanks. Would this help? Sub RoundAdd() Dim mystr As String Dim cel As Range For Each cel In Selection If...

XY chart labeller Issue: (Magic quadrant) But with a twist.
Hiya Folks I have the same problem as has already been discussed on the forum (see below@ end of my email), but in my case there is an additional twist. The orginal issue was solved by using Rob Bovey's XY Chart Labeler to add labels. The free download is at his web site: http://www.appspro.com/Utilities/ChartLabeler.htm But in my case I have lots of XY points (155 to be precise). The problem is that 155 visible labels make the chart look very messy. Is there anyway to make the XY labels invisible until you hover over them with a mouse/pointer? Any help would be greatl...

Secondary axes issues
I have the following sample data.... COL-1 COL-2 Month Cumulative Revenue (Without help) Jan-09 2.20 Feb-09 5.36 Mar-09 10.28 COL-3 COL-4 Dates Cumulative Revenue (With Help) Mar-25 0.20 Mar-28 1.50 Mar-31 2.50 Apr-2 3.25 Apr-5 4.65 I want to show ONE CHART where the revenue trend with help has been better than the months without help I tried using COL-1, COL...

Named Ranges -- Link Issue?
I am working on a very large Excel workbook (231 worksheets, all inter-linked to one another). Each worksheet contains a number of named ranges (for the entire workbook, there are 6,117 named ranges). This appears to be causing a problem such that if I modify a cell on Worksheet1, and Worksheet2 is dependent on the value, Worksheet2 does NOT change UNLESS I rename Worksheet1. (I can rename it to _Worksheet1 and then back to Worksheet1, but again, unless I actually *change* the name, the link is not updated). Calculation is set to automatic (but even if I force calculation, be it on ...

Excel 2003 Print Issue
I have created a spreadsheet to help with a university engineering assignment and I have added a worksheet that is basically an automatically generated report of all the calculations. I have set the Print Area up in such a way so that the results are printed out in well defined pages (e.g. page 1: title page, page 2: summary of input variables, page 3: summary of calculation results etc). The report is arranged vertically in the worksheet, so the pages are 'stacked' on top of each other. It prints out fine in Excel 2000 and 2002 but I recently upgraded to Excel 2003 and now find tha...

Formula for Date
I'm new to formulas and just want to display the current date in my outlook form (e.g., December 18, 2004). What I've done is created a combination text field where I have the following fields: [Email Opening Date] [Full Name] [Job Title] [Company] [Business Address] Dear [Full Name]: When I send a new message, I then copy the values into my email instead of copying the data (name, title, address, salutation) one field at a time. This allows me to personalize the email. The problem is that I do not know what to do to with formulas to show the current date as I note above. Thank...

Creat a time book
I'm building a semi automated time book in Access. what i want is to be able to give access a two week period prefferably by specifying the beginning and end dates and have access add an entry to a table i'm going to call the 'Time Book' for each person in a personnell table for each day. the best i have been able to come up with is to pack a Macro with 14 queries, each adds one more day to a specified starting point. one of the problems i'm running into is that some of the shifts run over night and Access doesn't calculate the shift end correctly. I wo...

Category totals over time...
Good morning... Is there a way within Money 2007 to graph the income or expense in a given category over time? For example, I might want to graph the monthly sums of my Dining Out expense over the past few years. -Ed In microsoft.public.money, Ed Markovich wrote: > >Is there a way within Money 2007 to graph the income or expense in a >given category over time? > >For example, I might want to graph the monthly sums of my Dining Out >expense over the past few years. You could start with the Income and Spending Over Time report. Customize to select just the category you w...

Smartlist MDA lookup is missing the GL Posting Date.
There is two different dates in the MDA tables, one is for Posting Date and the other is the Transaction date. The DTA10100 table has a TRXDATE field, which is actually the GL Posting Date, and the DTA10200 table contains the same field name, TRXDATE, which is the Transaction Date from the sub-module. In Smart List, it only allows us to look at the Transaction Date from the DTA10200 and the customers want to look up the MDA records by the GL Posting Date, which Smart List doesn't allow. We need to add the Date from the DTA10100 table as the GL Posting Date. ---------------- This p...

Format Cells Date (or any change) not working on imported data
Hello, I've just spent ages researching this and not come up with what I need to be able to do. I have a worksheet for some simple data that has been imported, a date, text and number column (as they display graphically to the end user). All are a "general" format when using Format > Cells. The issue I have is that the date information is in an American date format and I would like to change them into a UK date format. Format > Cells and selecting any option (including custom and special) makes no changes to the imported data. I have seen the work arounds whereby you sp...

On-Premise - Outlook CRM Client 4
We're having a multitude of issues with our CRM for Outlook Client. These issues have just started accross multiple users since CRM was installed last month. 1)Many machines running Outlook 2003 / 2007; current patches with the Microsoft CRM4 (RU5)Outlook client installed. When creating or replying to a HTML, or RTF, e-mail sometimes even though the cursor is in the body text area, the formating toolbar will be greyed out (as if it is a plain text e-mail) unless the user right clicks in the toolbar area of the message which apart from bringing up the toolbar customization menu ...

Excel 2007 text flash (or blink)
This might have been asked before but if so I'm not finding it when I use a google groups search, mainly because of either none or too many answers returned depending on which keywords I use. Is it possible to make the text and/or the background in any individual cells to flash or blink ? In detail what I need to do is have a cell with a solid black background, text which is yellow, but blinking at a slow cadence, 1 sec on 1 sec off in such a way that it looks as if the text in the cell is flashing between off and on like a flashing light. -- Nick hi also have a look...

how can i start using excel for the first time?
i cant figure out how to get excel to work for me and im a first time user of it? i Cant get nothing to work on it? can anyone please help me here Paul Can you get Excel to start up? Can you get a blank workbook to open via File>New? For basics on Excel see.......... http://www.usd.edu/trio/tut/excel/index.html http://www.baycongroup.com/el0.htm Microsoft Training Courses. http://office.microsoft.com/en-us/training/CR061831141033.aspx Gord Dibben Excel MVP On Wed, 15 Dec 2004 15:39:02 -0800, "Paul Scheffer" <Paul Scheffer@discussions.microsoft.com> wrote: >i ...

Convert Date Field to Text
I have created 2 tables, a Day table and Month table. I have two fields in both, Day table has Day, Text; Month table has Month, Text. I have a query combining the two so the query has Day, Days_Text, Month, Months_Text. What i wanted to do on one of my forms is have a date field using the short date so i can have the numeric value of course, is if the day of the month format is 'dd' or 'mm' it would look into either the table or query and bring back the text value, same for the month. Example: day '11', would read 'Eleventh', and month '09' would r...

Removing empty cells in column groups
Hi All, I'm creating a report with row and column groups. But the columns groups are displaying values in sperate rows instead on the same row leaving empty cells. I need to remove those cells and get the column gropu values in a same row. Please refer to this image http://img526.imageshack.us/img526/7139/23561415.png Hi What dataset query do you have? I think SELECT MAX(CASE WHEN .... ) should solve the problem "Supun" <Supun@discussions.microsoft.com> wrote in message news:F842A7A9-7975-438E-B2E5-40C72166DF63@microsoft.com... > Hi All, > ...

Preprend Text Where Cell Not Empty
Good evening I have a spreadsheet used as a data source for a Word mail merge. The address data has "address1", "address2", etc. Address2 is used store apartment or unit numbers, but does not have a text prefix, just the bare number (Ex. "104"). I want to prepend a "#" before the apartment number (Ex. "# 104"). I generally get the concept that I want to the select the entire column to as a range, and increment down the column. However, if the cell is empty (as would be the case for a house), then we want to skip the cell w...

Why isn't the selected text replaced when I start typing?
When I select text and and start typing, the selected text is not replaced and remains as it was after what I typed. This also happens when I paste. This only happens with Microsoft Word. (I have Word 2003.) How can you stop this from happening? Tools | Options... and on the Edit tab ensure that 'Typing replaces selection' is selected. -- Cheers! Gordon Bentley-Mix "Phi96" <Phi96@discussions.microsoft.com> wrote in message news:4307E265-CDA1-41C4-9241-A18CF57BD2E3@microsoft.com... > When I select text and and start typing, the selected text is no...