Converting Timestamps

[Steed32]

Hello,

PC/Windows 10

Microsoft 365; Excel Version 2211

*I am not able to post a sample file (error message that .xlsx file not supported when I drag and drop)...here is a screenshot of the worksheet:

I have a report that exports into Excel that contains a timestamp. The Timestamp in the export is a General string of numbers representing year, month, day, hours, minutes, seconds and milliseconds. I need to convert that string to a format that Excel will recognize as a Date:

From: 2021062708415900 to 06/27/2021 08:41:59:00 

-I don't care about the seconds and milliseconds characters so cutting those to make it work is fine. 

-The only way I have been able to make this work is to parse out the date from the time into separate columns, reformat each as a Date and Time and then recombine the two cells. I am hoping there is an easier way with fewer steps. 

-I also have tried pulling the file into power query with no success.

Any help is appreciated!

[#2 · Accepted solution]

@Steed32 

Presuming the string is in A2:

This solution discards milliseconds:

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

[#3]

Answered! Thanks - this worked like a charm!

[#4]

You're welcome! Glad it worked.