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

Berlin focused Facebook Flohmarkt groups

Published 2018-09-14, 12:40

Any important ones missing?

Topic(s): Link No comments - :(

„Turn off“ output truncation of RSpec output

Published 2018-08-27, 19:16

Use this code to increase the output length before RSpec trancates output like e.g. `expected` and `got` in test failure results:

RSpec::Support::ObjectFormatter.default_instance.max_formatted_output_length = 1024

Topic(s): Technik No comments - :(

Measure command line program execution time on Windows

Published 2018-08-20, 10:05

Linux has the `time` command, which can be used to measure execution time of CLI commands:

$ help time

time: time [-p] PIPELINE
    Execute PIPELINE and print a summary of the real time, user CPU time,
    and system CPU time spent executing PIPELINE when it terminates.
    The return status is the return status of PIPELINE.  The `-p' option
    prints the timing summary in a slightly different format.  This uses
    the value of the TIMEFORMAT variable as the output format.


$ time sleep 2
real    0m2.009s
user    0m0.000s
sys     0m0.004s

Unfortunately this command doesn’t exist on Windows. But there are alternatives:


ptime is a simple executable that you use to run your normal command, afterwards it will out output the execution time it measured:

Execution time: 7.844 s

Download it, put it in a place with a simple path and just prefix your command with the `ptime.exe`.


A command line utility to prepend timestamp information to the standard output of another command. Useful for long-running processes where you’d like a historical record of what’s taking so long.

gnomon does a little bit more, as it times each line of the output of your command. But at the end it also outputs a total that can be used to measure the total execution time of your command, at the benefit of also knowing which lines took most of that time:

Total   6.5076s

You install it with npm running `npm install -g gnomon` and then just pipe your command to gnomon:

command | gnomon
Topic(s): Kram No comments - :(

Avoid accidentally closing programs on macOS with Cmd + Q

Published 2018-08-18, 15:24

I am a predominant Windows user. But as I dabble in mobile apps, I of course also have to use a Mac now and then.

This tended to be a pretty frustrating experience, as my Windows shortcut memory always uses Alt Gr + Q to type the @ sign in email addresses – which translates to Cmd + Q on Mac and closes your current program without any additional prompts.

My solution: System Preferences –> Keyboard –> Shortcuts –> Accessibility –> Invert colors: Activate and set to Cmd + Q.

No more accidentally closed programs when typing my email address :)

Topic(s): Technik No comments - :(

How to install Laravel (5.6) on Uberspace (7)

Published 2018-07-16, 12:57

Laravel is a great PHP framework, and Uberspace is a great and nice PHP host. Why not mix both?

Installing Laravel on shared hosting – which Uberspace still is despite the shell access etc. – can be difficult, especially because of public folder that is used to respond to requests instead of the project root.

Here are the basic instructions for getting a new Laravel project to respond to calls of your <username> domain:

  1. Switch folder to /var/www/virtual/<username>.
  2. composer global require "laravel/installer" to install the Laravel Installer.
  3. Create a new Laravel project: laravel new <projectname>.
  4. Delete /var/www/virtual/<username>/html (make sure it is empty, or just rename it maybe) and replace it with a symlink from html to <projectname>/public.
  5. The default Laravel start page should now be available at your <username>

To be able to use the database in the project, you have to make some changes:

  1. Get your generated MySQL password from ~/.my.cnf.
  2. Update the .env file in your Laravel project:
    Username and database should be changed to your username, the password to the one you just retrieved.
  3. Caution: At the time of writing Uberspaces uses MariaDB 10.1.34 (find out by using the command mysql -v). Laravel needs some tiny changes if you work with MariaDB <10.2.2:
    • Edit the app/providers/AppServiceProvider.php file and add the following:
      use Illuminate\Support\Facades\Schema;
      public function boot()

      (Add both the method call and the import!)

  4. Now you can run php artisan migrate in your Laravel project to create the default tables.

Of course you probably don’t want to host your project at your domain, not create a new project but check out your already developed project from git, and also not use the default database – but I am sure you can find your way from here.

Laravel Homestead: Run phpunit with another PHP version

Published 2018-06-22, 13:09

Laravel Homestead is a nice development environment/server, that comes with multiple PHP versions installed. You can configure each project you are running through it for its own PHP version, and use e.g. php5.6 on the command line to execute code with a specific PHP version. Unfortunately phpunit always uses the default PHP version. What if the PHPUnit you installed in a project needs a specific PHP version to work and execute your tests (for example because the whole project only works with PHP 5.6)?

You can run phpunit with a specific PHP version by using this command:

php5.6 vendor/phpunit/phpunit/phpunit
Topic(s): Laravel, PHP No comments - :(

How to add spacers to the macOS dock

Published 2018-01-12, 23:18

  1. Open Terminal
  2. defaults write persistent-apps -array-add '{tile-data={}; tile-type="spacer-tile";}'
  3. killall Dock


Topic(s): Kram No comments - :(

How to Install Ruby with rbenv on Ubuntu 16.04

Published 2017-12-13, 14:48

Each time I have to install Ruby + rbenv + bundler manually on a Ubuntu machine I somehow mess it up and have to google for a tutorial, then try some until I find a working one.

This one here did work perfectly yesterday, when it was time to do it once again:

Topic(s): Development, Link No comments - :(

How to create a macOS High Sierra VM with VMware

Published 2017-12-01, 22:07

Topic(s): Link No comments - :(

15 queries. 0,245 seconds.