Efficient date and time handling – employees efficiency on a punchcard

Avatar
Martin Večeřa

In many systems, time is either just a piece of text or, in the better case, something that you can enter via a calendar popup dialog. The system itself then barely understands the meaning.

On the other hand, we people understand time pretty well. We know that a certain date is associated with a day of week. We know that we typically do not work during the weekend. We know that Christmas are in the fourth quarter.

We would like our systems to naturally understand this as well. And guess what, Lumeer does exactly that.

The easiest way to proceed is to tell Lumeer what format of date and time we have or expect. Lumeer tries to guess that for you, however, it might be difficult to recognize month and day when the numbers do not exceed 12 for instance.

Let’s have a look at our example with data about calls made by our sales department. In the table with calls statistics, we right-click on the Call Time column header, select Attribute type and switch it to Date.

Next, we select a Custom format and enter a sort of a magic formula (see below) that reflects our format. We can see in the example that the format matches.

In the following table, we can see what is the meaning of individual codes in the date time format formula:

FormulaMeaning
YYYY, YYYears 2021, 21
MM, MMonths 01-12, 1-12
DD, DDays 00-31, 0-31
HH, HHours 00-23, 0-23
hh, hHours 00-12, 0-12
mm, mMinutes 00-59, 0-59
ss, sSeconds 00-59, 0-59
S, SS, SSSMilliseconds 0-999
a, Aam/pm, AM/PM
DDD, DDDDDay of year 1-365
ddd, ddddDay of week Mon, Monday
e, EDay of week 0-6, 1-7
WW, WWeek of year 01-53, 1-53
x, XTimestamp 1410715640579, 1410715640.579
Z, ZZOffset from UTC +12:00

The advantage of Lumeer understanding the format is that we can create a Pivot table that counts total and unique calls per day of week and hour of the day.