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:49UTC2016To 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 - :/
( 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