How to Coda with start and end times
Calculating the duration per working day per person
I got a question to have a look at a specific problem. You have people and they start their working day and they end their day. The question was rather simple, based on a table like below: how many hours per day, per week and so on they work?
The checked time is the time in and out per day per employee
What we need is a list of date-times an employee comes in and gets out per day.
That is all together not difficult, certainly not if you read a few of my previous blogs.
We take the list of all employees and compare each item with all the items in the same list. If we find a match(is true), we ask for the date-time stamp.
This is how a filter formula works. Below how this looks like — the code part.
What you see is a filter to get per employee the date and time stamps for in and out. So far the easy part most Coda Makers are familiar with.
The next step is to acknowledge that we generated a list of items per employee in each cell. Dealing with a list of items in a cell, thus subsets in a standard list requires the application of a virtual index to deal with each individual item. The first thing I did was setting up a sequence. First you define the count per sub set.
Next you wrap a Sequence() around it and here it is, the virtual index. As you can see in the example the first time is the start, the second the end, the third the start, the fourth the end and so on. What I need is to subtract two items. In other words item two minus item one and item four minus item three.
The sequence formula permits for a bit of jumping, thus instead of 1, 2, 3, 4 you can also have 1, 3, 5 etc. You use the ‘by’ part at the end. But this did not bring me anywhere. I felt a bit stuck.
Then I noticed that I was subtracting an odd number with an even number and that if I could turn this observation into a formula, we might have something. Coda has a great toolbox and we have IsEven() and the function IsOdd(). The main question is how to relate these functions to the items in the subsets? I started with the exercise below.
First I used WithName() to reference the number range and second I used this one in combination with a Filter. below how it looks:
Once this is clear, the even numbers can also be subtracted and related. This is the final step in which I define the ranges for Odd and Even numbers and I end with subtracting.
Once I noticed the IsOdd & IsEven logic I had the solution, it was just a matter of letting my fingers do the job. This data can be used further in various forms.
These patterns also might help to deal with time table calculations and accounting related challenges. Instead of using a previous row logic, you use this type of filter which goes fast in Coda and will show to be easier as well in the end. I am not saying that in every case you have odd and even as in the above example. I point to the fact that filtering on the positions of items in a subset goes faster than dealing with relative numbers using Find(), certainly in larger data sets.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I am a Coda Consultant & Expert and rely mainly on Coda, Mailjet, Zapier & Paperform to get the job done.
