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:


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


`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:

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 by Results of an Amazon (DE) „Datenauskunft“ » Betamode am 18. September 2018

Sorry, the comment form is closed at this time.

21 queries. 0,249 seconds.