ExcelBanter - Change to day format

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Change to day format (https://www.excelbanter.com/excel-worksheet-functions/151619-change-day-format.html)

Blue Fish

Change to day format

Hello:

I have import a text file that the Raw Data is "20070722130054". How
can I make the excel to recognize as "2007-07-22 13:00:54"?

Thanks!

Pete_UK

Change to day format

Assuming your value is in A2, but this in B2:

=DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))
+VALUE(MID(A2,9,2)&":"&MID(A2,11,2)&":"&RIGHT(A2,2 ))

Format the cell as Custom -- yyyy-mm-dd hh:mm:ss, then copy the
formula down.

Hope this helps.

On Jul 25, 11:02 am, Blue Fish wrote:
Hello:

I have import a text file that the Raw Data is "20070722130054". How
can I make the excel to recognize as "2007-07-22 13:00:54"?

Thanks!

Toppers

Change to day format

In a helper column:

try:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID (A1,9,2),MID(A1,11,2),MID(A1,13,2))

Assuming text field is in A1 (etc)

format as CUSTOM: yyyy-mm-dd hh:mm:ss

Then copy/paste special-values in your helper column.

HTH

"Blue Fish" wrote:

Hello:

I have import a text file that the Raw Data is "20070722130054". How
can I make the excel to recognize as "2007-07-22 13:00:54"?

Thanks!

All times are GMT +1. The time now is 11:49 AM.