Converting text string date and time

When running infocapture report, you will find that the date and time are displayed in YYYYMMDDHHMMSS format for example:

20151119193000

Date and time format are vary depending which country. This is one of the reasons why string YYYYMMDDHHMMSS format is commonly used in the database table.

Here is how to convert the following date format in Excel

Assuming you have  20151119193000 in A1 and wish to conver this to MM/DD/YYYY (USA Format)

In A2 (for Date) enter:

=MID(A1,5,2)&"/"&MID(A1,7,2)&"/"&LEFT(A1,4)

the result will be:  11/19/2015

For DD/MM/YYYY Format use the following:

=MID(A1,7,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)

In A3 (for Time) enter:

=MID(A1,9,2)&":"&MID(A1,11,2)&":"&RIGHT(A1,2)

the result will be: 19:30:00

 

 

Last modified on 29 January 2018 by Mhairi Hutton
Created on 19 February 2016 by Michael Christian

Was this helpful?  

1 Like
Share