text date & time conversion help

[karkas]

I have a report where the cells are in text in this format "2018021409012900" it is YYYYMMDDHourHourMinMin. The last four digits are milliseconds which I don't need.

I need to convert this to a date & time cell that is more legible.

I can think of some labor intensive ways to do this, but I suspect there is a much simpler way that an expert here can help with.

Thanks in advance for any advice

 

[njimack]

This will convert it to a date and time, but it will be displayed as a numeric value. The alternative is to use a TEXT function, but then you won't be able to use the result in other formulae, so it depends what you're using this for...

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))&TIME(MID(A1,9,2),MID(A1,11,2),0)

 

[Joe4]

Here is another way:

=DATEVALUE(CONCATENATE(LEFT(A1,4),"/",MID(A1,5,2),"/",MID(A1,7,2)))+TIMEVALUE(CONCATENATE(MID(A1,9,2),":",MID(A1,11,2)))

 

[karkas]

This will convert it to a date and time, but it will be displayed as a numeric value. The alternative is to use a TEXT function, but then you won't be able to use the result in other formulae, so it depends what you're using this for...

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))&TIME(MID(A1,9,2),MID(A1,11,2),0)

For some reason, this was giving me the wrong date 2018021408504900 --> 4/7/81 08:50 AM

=DATEVALUE(CONCATENATE(LEFT(A1,4),"/",MID(A1,5,2),"/",MID(A1,7,2)))+TIMEVALUE(CONCATENATE(MID(A1,9,2),":",MID(A1,11,2))) by Joe4, did work however.


Thank you both very much, you guys are genus's!

 

[MARZIOTULLIO]

b1=TEXT(VALUE(A1),"0000-00-00 00\:00\:00\.00")+0

format b1 as mm/dd/yyyy hh:mm:ss