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.
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).
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.
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.
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.
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.
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.
