how to format date and time this way?

[cat3appr]

hi all,

I need to convert a date&time combined in one cell into 2 parts, combined in 2 cells, i've uploaded an .xls with all explained... not sure how to do this, please let me know

many thanks and marry christmas!
Alex

[Jonmo1]

Try

Date
=TEXT(SUBSTITUTE(LEFT(A2,10),":","/"),"dd.mm.yyyy")
You my have to reverse the dd.mm to mm.dd depending on your regional settings, and if the original string's date is Dec 11th or Nov 12th.

And the time
=SUBSTITUTE(MID(A2,12,8),":","")

[Sam Capricci]

this formula in H2 should take care of your date... =MID(A2,9,3)&MID(A2,6,3)&LEFT(A2,4)
still working on your time.

[Tony Valko]

Your time conversions don't make sense (to me).

19:08:52.19 = 190845 - - - shouldn't that be 190852
19:08:52.42 = 190841 - - - shouldn't that be 190852
19:08:51.86 = 190835 - - - shouldn't that be 190851

[Sam Capricci]

this is close on your time but I can't tell where your last two values for the time are coming from...
=MID(A2,12,2)&MID(A2,15,2)&RIGHT(A2,2)

[cat3appr]

thank you guys, but it gets already confused... as in the last formula there is an A2 while in the spreadsheet the date starts in A3, that's why i uploaded the file, could you be so kind to actually apply the working formula in the excel spreadsheet i've attached and attach it back?

thank you!

[cat3appr]

thank you guys, but it gets already confused... it doesn't work...as in the last formula there is an A2 while in the spreadsheet the date starts in A3, that's why i uploaded the file, could you be so kind to actually apply the working formula in the excel spreadsheet i've attached and attach it back?

thank you!

[Sam Capricci]

here is your sheet back. clearly the sheet I downloaded has the data beginning in A2, hence the formula.

[Jonmo1]

Here you go.

Again, you may need to reverse the dd.mm to mm.dd depending on your regional settings, and if that date is Dec 11th or Nov 12th..
Also repeating Tony's observation, that I don't see how you got 190845 from 19:08:52.19

excel question.xlsx

[Sam Capricci]

Wow, getting old, (just over 2 wks away from 60) but I just noticed that in his original attachment he changed the : to . in his desired output. Now I see why Jonmo gave you the substitute formula.


So if you adjust my first (date) mid formula to add this, it seems to work out for you.
=SUBSTITUTE(MID(A2,9,3)&MID(A2,6,3)&LEFT(A2,4),":",".")

[AlKey]

These formulas will preserve the numerical values of Date and Time

For Date

=SUBSTITUTE(LEFT(A2,10),":","-")+0

Format Custom, d.m.yyyy

For Time

=MID(A2,12,8)+0

Format Custom, hhmmss

Row\Col	H	I
1	Date	Time
2	12.11.2014	190852
3	12.11.2014	190852
4	12.11.2014	190851
5	12.11.2014	190851
6	12.11.2014	190851
7	12.11.2014	190851