Spliting date and time when in one field.

[luccian34]

Thanks for the help. Here is what I have. I am getting data in an Excel worksheet that has a date and time in one cell. example 1/1/2006 8:00am I need to split them into two cells. How do I do this. I know this is probably a simple fix but I have not been able to figure it out. Thanks

 

[PRPhx]

How many columns and rows are you talking about? The easiest way is to copy each column and past it next to the orginal column. Then format the orginal column as a DATE and the copied column as TIME.


There is also the Text to column function (found under the Data menu). You can split columns this way. BUT, depending on how your date/time is formatted, it may/may not work. If your Date is obtained with the now function, it could be very difficult to do. Also using the Text to column function will probably show some unwanted numbers in the Date column and you will have to shorten the width of the column to hind those unwanted numbers (they would be something like 00 to show time).

IOW, it's not something that is all that easy to do and may take some thought and trial and error.

 

[Lilliabeth]

copying it over and format one as date and the other as time will work well, as PRPhx said.

 

[luccian34]

Well I will have 20,000 rows to format. The unformatted data looks like 38822.06323 when formated is 4/15/06 1:31
in one cell. I need to have the date in one cell and the time in another.

Thanks for the quick reply.

 

[xlbo]

yup

format 1 column as dd/mm/yyy and the other as hh:mm:ss

38822 is the date
0.6323 is the time

Have a look at the FAQs section for an explanation of how dates & times are stored in excel

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

If you don't want date info in the time column or time info in the date column, then you can do the following:
Say your data is in column A.
In column B, enter [COLOR=blue white]=int(A1)[/color]
[tab]This will give you ONLY the date information.
In column C, enter [COLOR=blue white]=A1-int(a1)[/color]
[tab]This will give you ONLY the time information.

Format the new columns as suggested above.

For more info on how Excel deals with dates and times, see faq68-5827, Why do Dates and Times seem to be so much trouble?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[luccian34]

Awesome! Thanks a ton.