Date Time formula problem

irvpost

New Member
Joined
Jul 28, 2011
Messages
5
3 million lines that are formatted the same in text. I need to get them into a DB and i am trying to use excel. I have a single column that has 3 different date time formats. Format 1 is Date.Time = (01/02/11.13:52:12). Format 2 is Time = (16:31:17). Format 3 is just a dash (-). i need to get them into 2 columns, one with date and one with time. If there is no date i would like a dash (-) if there is no time i would like a dash (-).
I have tried or'd iserror with if statement left and right and it always converts the time into LI.
I need help with column D and E. I would like to split each column into date and time.

here is a small sample:Space Delimited
626483 626357 05/01/11.23:59:46 23:59:55 05/02/11.00:00:01 611 6013 - - N - U TER - A T -
646467 626446 05/01/11.23:59:39 05/02/11.00:00:01 00:00:02 611 6012 - - N - - TER - A T -
626465 626254 05/01/11.23:59:50 05/02/11.00:00:00 00:00:08 611 6014 - - N - - TER - RW T -
626343 626256 05/01/11.23:59:34 23:59:49 05/02/11.00:00:15 611 6011 - - N - - TER - A T -
714817 626470 05/02/11.00:00:15 - 00:00:15 611 6008 - - 003 - - TER - A F -
626446 866801 05/02/11.00:00:13 - 00:00:18 - - - - N - - INT - A F -
866801 866801 05/02/11.00:00:14 - 00:00:18 - - 601 39 N - - ORG TF A F 5102
626303 626358 05/01/11.23:59:32 23:59:42 05/02/11.00:00:19 - - 101 0 N - - ORG LO A T -
626446 - 05/0.00:00:18 - 00:00:20 - - - - N - - INT - A F -
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Watch MrExcel Video

Forum statistics

Threads
1,130,046
Messages
5,639,761
Members
417,109
Latest member
996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top