Converting text string date and time

By Michael Christian on 19 February 2016
0 Likes

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