Efficient date and time handling – employees efficiency on a punchcard

Martin Večeřa

Are your employees as efficient as possible? Why don’t you calculate employees efficiency on a few clicks of a mouse button?

You can then figure out if the employees efficiency is an issues and try some ways to improve it!

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.

Understand Date and Time

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.

employees efficiency calculation

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:

YYYY, YYYears 2021, 21
MM, M, MMM, MMMMMonths 01-12, 1-12, Jan-Dec, January-December
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

Calculate Employees Efficiency

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.

employees efficiency result