Google Spreadsheet: Extract proper dates from funny date strings

Published 2018-09-18, 13:11

Recently Amazon sent me a „Datenauskunft“ that included a .csv of my Kindle’s Whispersync activity with a column containing dates, but in this funny format:

FriApr1412:21:01UTC2017
WedApr2610:43:54UTC2017
WedApr2611:30:41UTC2017
WedApr2611:30:40UTC2017
FriMay1119:26:44UTC2012
TueNov0819:47:36UTC2016
SunJan1022:12:49UTC2016

To get a proper date out of this, I built this formula to be used in a new column:

=DATE(RIGHT(C2;4);MATCH(MID(LEFT(C2;8);4;3);{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0);RIGHT(LEFT(C2;8);2))

`C2` being the column with the date here, this first extracts the year (4 characters from the right), then converts a 3 character month name to a number (which it got from extracting char 4-6 from the first 8 chars of the string), and finally the date which are the last 2 chars of the first 8 char block. The time is not needed here and discarded.


More variants:

WedApr2611:30:41UTC2017=DATE(RIGHT(C2;4);MATCH(MID(LEFT(C2;8);4;3);{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0);RIGHT(LEFT(C2;8);2))
31-DEC-2015 17:26:15=DATE(LEFT(RIGHT(F2;13);4);MATCH(MID(F2;4;3);{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0);LEFT(F2;2))
Sun May 08 09:47:24 UTC 2011=DATE(RIGHT(D2;4);MATCH(MID(LEFT(D2;8);5;3);{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0);RIGHT(LEFT(D2;10);2))
09/May/2011 11:49:10 UTC=DATE(MID(D2;8;4);MATCH(MID(D2;4;3);{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0);LEFT(D2;2))



Topic(s): Notiz 1 single comment - :/

Diskussion zum Artikel

» Selbst kommentieren

  1. ( 1 )

    […] with Google Spreadsheet then enabled me to better format the available data and add columns to e.g. parse strange date format columns, sort by date columns and so […]

    Pingback von Results of an Amazon (DE) „Datenauskunft“ » Betamode am 18. September 2018

Selbst kommentieren

Trackback-URI, Kommentarfeed. XML-Feed





17 queries. 0,103 seconds.