Photo by Christina @ wocintechchat.com on Unsplash

Coda Only The Active Employees

Filtering out per active month the dates people worked

The use cases

We want to create a table that is filled out with dates people work for the company. Information about employees lives in the table DB Employees and more specially this table contains a ‘start date’ and end date column. Both are important in building up the date range.

Case 01

What happens in case a holiday worker comes in for two weeks and starts on day 5 and works until day 19. We know that this worker should work between these two days and we thus should generate a table containing rows for day 5, 6, 7 up to 19. Obviously, we use a Sequence() to get this date range. This one seems rather easy.

Case 02

What if somebody started early 2002, thus 20 years ago and stops at the end of this month. In this case we have way too many dates. However we can filter these dates out with a function that contains all the dates of this active month. This gives the result we need.

Case 03

But we are not there yet. What if an employee started before this month and there is not an end date yet? Well, in this case we assume that you still work this end of the month and we do that by stating:

EndDate.IfBlank(_AllDatesActiveMonth.Max()

Together these insights generated the code you see below.

the approach to filter on active dates in a month

In short: we take out the people on maternity leave, we count the ‘left over’ and we evaluate each one of them for their respective start & end date.

Let me break it down into a few steps.

Step 01 is to get only the list of people that are active (maybe not this month, but are active).

Step 01

The second step is to count the active people to use this number as a virtual index together with my FormulaMap() Í try to give names that inform me about the data I work with, in this case the number of people.

step 02 : counting the people

In step 3 we use the start & end dates to get the date ranges per employee, while using the virtual index developed in step 02 to get a list per employee.

Step 03

The square brackets you observe indicate the sub lists in the total list, a sub list is the date range per employee.

However this function results in too many dates. We need to bring the date range down to the dates that correspond with the dates of the month we are working in. I reference a named function (AllDatesActiveMonth), I wrote about this subject recently:

The last step is thus adding the filter as you see below and we are back where we started.

Step 03: adding the filter

Due to the applied filter the max date range per month is equal to all the dates part of the named function AllDatesActiveMonth. It is less when the start and end date to not coincide with the first and last day of the month.

The result is that in my overview table with this logic I only show the dates somebody is set present, no matter how many days per month like below

As you see above, I had to integrate this logic in a button that filles out a table. Below how that function looks like (for the first part). Writing these functions is time consuming and one should not consider to resolve this kind of work a jiffy.

the integration of the function in the button

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 and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.

Not to forget: the Coda Community provides great insights for free once you add a sample doc.

Christiaan on “Coda Only Active Employees”

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Christiaan Huizer

Christiaan Huizer

I use Coda mainly for planning & calculations of all sorts. Follow me to learn how to Coda with numbers. I blog at least once per week.