Excel: US-Datum in Datum umwandeln

Zum Sortieren einer Tabelle nach Datum muss dieses von Excel als „echtes“ (numerisches) Datum erkannt werden. Wenn die betreffende Spalte in einem im deutschen Sprachraum herkömmlichen Format vorliegt, z.B. dd.mm.jjjj, ist das recht einfach mit der Funktion DATWERT zu machen (siehe Office Support).

Wenn das Datum allerdings im US-Format m/d/y vorliegt, ist die Umwandlung nicht so einfach. Funktioniert hat für mich letztlich folgende Formel, wenn die Zelle A1 das Datum im US-Format enthält:

=DATUM(GLÄTTEN(TEIL(WECHSELN(A1;"/";"          ");21;10));GLÄTTEN(TEIL(WECHSELN(A1;"/";"          ");1;10));GLÄTTEN(TEIL(WECHSELN(A1;"/";"          ");11;10)))

My i3 dual screen workflow

Using the i3 tiling window manager on two screens („outputs“) can be challenging. The number of workspaces grows twice as fast than with a single screen setup and it is easy to lose track of the numbers and contents of workspaces. For me personally it is more intuitive to remember a certain sequence of workspaces on each screen, which seemingly extends above and below the workspace presently displayed. (It may be that this intuition has been coined by using the GNOME Shell over extended periods.)

In order to achieve a comparable user experience within i3, I use the following Python script with the keybindings presented below. The script is inspired by an article on the i3 homepage by user captnfab. It has one dependency: ziberna/i3-py, which can be installed with pip3 install i3-py. As is apparent from the keybindings, Ctrl+Alt+Up/Down are used to switch the present workspace on the focused output. With the same keys together with +Shift you can take the focused window with you.

#!/usr/bin/python3
#
# i3-switch-workspace.py
# by Fabian Stanke
#
# Sequentially switch workspaces on present output

import i3
import argparse

parser = argparse.ArgumentParser(
description='i3 workspace switcher.')

parser.add_argument(
'--move', action='store_true',
help='take the focused container with you when moving.')
parser.add_argument(
'direction', choices=['next', 'prev'],
help='defines in which direction to switch.')

args = parser.parse_args()

workspaces = i3.get_workspaces()

# Determine focused workspace (and thus the focused output)
focused_ws = next((w for w in workspaces if w['focused']))

# Collect all workspaces of the focused output
ws_names = list(w['name']
for w in workspaces
if w['output'] == focused_ws['output'])

# Determine position of focused worspace in that collection
idx = ws_names.index(focused_ws['name'])
target = focused_ws['name']

if args.direction == 'next':
# Determine next workspace

if (idx + 1 < len(ws_names)):
target = ws_names[idx + 1]
else:
# Determine last number used on this output
maxidx = 1
# Determine unused numbers
used = {}
for w in workspaces:
try:
widx = int(w['name'])
used[widx] = True
if w['output'] == focused_ws['output']:
maxidx = max(widx, maxidx)
except:
continue
# Increment to create new name
while used.get(maxidx, False):
print(maxidx)
maxidx += 1
target = str(maxidx)

elif args.direction == 'prev':
# Determine previous workspace

if (idx - 1 >= 0):
target = ws_names[idx - 1]
#else remain at first workspace

if args.move:
# Move the focused container to the target workspace first
i3.command('move', 'container to workspace ' + target)

# Switch
#print("switch to " + target)
i3.workspace(target)

My preferred keybindings to actually use the above script are:

bindsym Ctrl+Mod1+Down exec i3-switch-workspace.py next
bindsym Ctrl+Mod1+Up exec i3-switch-workspace.py prev
bindsym Ctrl+Mod1+Shift+Down exec i3-switch-workspace.py --move next
bindsym Ctrl+Mod1+Shift+Up exec i3-switch-workspace.py --move prev

Interoperating Exim with Rspamd

Recently I have been implementing Rspamd for providing some automatic filtering for the emails hosted on my server, which are presently delivered by Exim to the Dovecot LDA.

For integrating Exim with Rspamd, I followed the Rspamd manual, but thing weren’t exactly working as expected.

I was frequently getting error messages such as:

Feb  7 11:10:44 stanke rspamd[2939]: (normal) <3a1c27>; task; accept_socket: accepted connection from 127.0.0.1 port 51289
Feb  7 11:10:44 stanke rspamd[2939]: (normal) <3a1c27>; task; rspamd_worker_error_handler: abnormally closing connection from: 127.0.0.1, error: IO timeout

with Exim at the same time reporting

2016-02-07 11:10:44 1aSMIm-0005QH-3P spam acl condition: Broken pipe on spamd socket
2016-02-07 11:10:44 1aSMIm-0005QH-3P H=mail1.mcsignup.com [205.201.139.33]:49767 Warning: ACL "warn" statement skipped: condition test deferred

In the majority of cases however the scan succeeded, so that a general misconfiguration could be ruled out. For some reason the socket connection between Exim and Rspamd seemed to break in some cases. I tried playing with the spamd_address options of exim, in particular with tmo and retry (see the Exim documentation on content scanning at ACL time), but to no avail.

What seems to have fixed the problem however was to define two entries for spam servers that Exim can connect to, despite the fact that they both refer to the same Rspamd instance. Exim seems to simply switch to the next (in fact same) server when seeing a „broken pipe“ and the reconnect seems to suffice to iron things out. Here is the relevant snippet to be inserted in /etc/exim4/conf.d/main/02_exim4-config_options:

spamd_address = 127.0.0.1 11333 tmo=1m retry=10s variant=rspamd : \
                127.0.0.1 11333 tmo=1m retry=10s variant=rspamd

fgallery on Mac OS X

fgallery is a generator for beautiful static web galleries, which I used e.g. for producing hochzeitsball.eu. The instructions on it’s website for running on OS X however do not overlap with my preferences (i.e. are not based on Homebrew).

To get fgallery running on OS X using Homebrew, the following steps worked for me:

brew install imagemagick lcms2 jpeg jpegoptim pngcrush fbida
cpan -i JSON JSON::XS Image::ExifTool
export PERL5LIB=$BREWPATH/Cellar/exiftool/9.61/libexec/lib:$HOME/perl5/lib/perl5
./fgallery --help