Converting text string date and time for Excel

When dealing with an Infocapture report, the date and time are displayed in YYYYMMDDHHMMSS format.

e.g.

20151119193000

 

Date and time format vary by country, but the string YYYYMMDDHHMMSS format is commonly used in database tables.

If your Intranet management team uses Excel to open Infocapture report downloads, this program does not accurately represent the string format out of the box, so your team will likely need to convert these using equations in Excel.

The alternative of course is to use a different program to open Claromentis CSV data in, as it is known Excel can cause issues with data formatting and encoding, so unless standardisation of features can be achieved, another program would be preferred for use with Intranet data.

 

Here is how to convert the following date format in Excel to have this appear correctly:
 

Assuming you have 20151119193000 in A1 and wish to convert 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 30 November 2023 by Hannah Door
Created on 19 February 2016 by Michael Christian

Was this helpful?  

Share