How to split date and time from single cell to two cells

To all GURUJIS s

Sir I need to change format of date and time that I get in a single cell - YYYYMMDD.HHMMSS in this format from my share broker say in cell "A" now I need to change this into the format DD/MM/YY in one cell and HH:MM:SS in another cell. I have very hard but not able to change the format, me being a zero in excel need help - sir kindly help me out

Thanks to all

Welcome to the Forum taprico. Please adhere to Forum Rule No. 1.

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

Also if you could attach a sample file with data, I'm sure you'll get a quicker response and more solutions to your request.

To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

It's OK. To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. Please also attach a sample, so we can visualize your requirements.

I get data from my share broker where in the date and time are in a single cell in the format YYYYMMDD.HHMMSS. I need to have it in two different cell one having date and the other having time - date in the format DD/MM/YYYY and time HH:MM:SS. I tried using the delimiting option but when the time is separated the zeros in front disappear and when I tried customizing with six digit numbers then the zero at the back disappears. Tried quite a few other tricks but failed with the time thing. Attaching a file with the data hope I manage to make everyone understand.


Thanks to all

Hi Taprico,

Try this
Suppose your Date & time is in cell A1 = 10/08/2013 19:48

In B1 type =Int(A1) and format the cell as Custom dd/mm/yyyy you will get 10/08/2013
and
In C1 type =Mod(A1,1) and format the cell as Custom hh:mm you will get time 19:48

Dear Kmahesh

I have already used this but when I use =Int(A1) I get a part of the data that is date in A1 in the active cell but when I change the cell format to date the data changes to a lots of hashes.
And for =Mod(A1,1) it displays like a time but its quite different.

Hope you understand what I mean.

A1 = 20130820.093826 --------- in BI (=Int(A1)) = 20130820 = #######.... (when the B1 Cell is formated to date)
A1 = 20130820.093826 --------- in C1 (=Mod(A1,1)) = 2:15:07

Where am I going wrong

Thanks

Use this for code:
A1 I used for teh source data.
B1 Resulting Date
C1 Resulting Time


A1

Formula:

Please Login or Register  to view this content.

B1

Formula:

Please Login or Register  to view this content.

C1

Formula:

Please Login or Register  to view this content.

Let me know if that helps you out at all.

Thanks David and FDibbins to both of you. David's formula seems to be working. It does change the data as I want. Now I will have to check if the data goes into Amibroker or not.

Will try that and report back

Thanks to all