another time date issue

[irvpost]

i have several million records that need to be modified.
when i try to concatenate it always converts time to decimal if orignal D1 or E1 has a date but works if time only.

Original import from text-
Columns C= init date/time, D=Connect date/time, E=Disconnect time

column C1= date.time ( 11/24/11.23:59:18 )

column D1 = ( if same day as column C1 ) hh:mm:ss if different day date.time
( 23:58:42 ) or (11/25/11.00:00:04)

Column E1 = ( if same day as column D1 ) hh:mm:ss if different day date.time
( 23:58:42 ) or (11/25/11.00:00:04)

4 possible entries
10/20/11.23:59:56 10/21/11.00:00:11 00:00:40
10/21/11.00:00:06 - 00:00:42
10/21/11.00:00:15 00:00:30 00:00:44
10/20/11.23:54:32 23:54:34 10/21/11.00:00:44

I have inserted a blank column before both column D and E. I have used the formula in the new column D with success-
D1=IF(F3="-","",IF(ISERROR(SEARCH("*/*",F3,1)),CONCATENATE(LEFT(D3,(9)),F3),F3))

F1=IF(G2="-",IF(ISERROR(SEARCH("*/*",H2,1)),CONCATENATE(LEFT(D2,(9)),H2),H2),IF(ISERROR(SEARCH("*/*",H2,1)),CONCATENATE(LEFT(D2,(9)),H2),H2)).

i have tried other tricks her using +left(C1,9)+D1 no joy,
tried different formating dd/mm/yy.hh:mm:ss
this is for import to SQL later and i am not very familiar with all this.

thanks in advance!

 

[irvpost]

got it-
IF(D2="-","",IF(ISERROR(SEARCH("*/*",D2,1)),LEFT(D2,8),LEFT(D2,8)))+IF(D2="-","",IF(ISERROR(SEARCH("*/*",D2,1)),LEFT(C2,8),LEFT(D2,8)))