Extracting useful dates from bank CSV files

I download activity from my bank in CSV format to work on it in a spreadsheet, which is pretty useful when you’re reconciling accounts, etc. Unfortunately, the date as given in the file isn’t in a format recognizable by a spreadsheet (Excel in this case, but most other spreadsheet programs use an identical formula language). Here’s how to change that.

The date format as it comes from the bank looks like this:

20081009120000[0:GMT]

From a datestamp perspective, it’s pretty useful – gives the whole date and time, plus the timezone. In my bank’s case, the time and timezone is always noon and GMT, so I’d much rather have just the date.

Assuming that the above value is in cell B1, this formula will extract a date in the considerably more useful YYYY-MM-DD format:

= LEFT(B1,4) & "-" & RIGHT(LEFT(B1,6),2) & "-" & RIGHT(LEFT(B1,8),2)

This formula prints 2008-10-09 for the above date string. If you want to get rid of the original date column, copy the new column of dates, then do a “Paste Special…” selecting “Values”, and it will no longer be dependent on the original date column.