Counting days an employee is doing an event

1

Hey Community,

I apologize - I am a noob when it comes to Coda and formulas. I am hoping to get some insight.

We have a People table that is staff and contractors in our business. We also have an events table that we assign these people to. An event has a travel in date (first day we travel to it) then the event start and end dates, and then lastly the travel out. So ultimately it might be something like:

Travel In - May 1st
Event Start - May 2nd
Event End - May 6th
Travel Out - May 7th

May 1st - 7th.

Photo example: https://share.getcloudapp.com/OAub2Lpk

I am trying to create a bar graph that basically shows all our employees and contractors and figures out how many days they worked, based on how I associate them with the event; so this person would have a date range between May 1st to 7th, and I want to calculate that they were ‘working’ 7 days, in a bar graph: https://share.getcloudapp.com/nOu81Ngd

Where would I even start?

1 Like
2

Hey!

First you need to think how you want that summary per contractor segmented. Is it total since forever? Is it monthly? If it is monthly, then what to do if an event spans two months (e.g. starts on Mar 29 and ends on Apr 3)? Calculate days towards March and April separately, or add the whole duration to either?

Let’s say it’s a simple example where you need to calculate the total and not break by months. Then:

  1. Make a new column on your events table where you calculate the full duration that you’ll later use for calculation. I imagine this would just be thisRow.[Travel Out] - thisRow.[Travel In]. Name it e.g. Duration.

  2. Actually, that’s all you need! You can make a chart view out of the Events table already, select Duration sum as value, and Primary lead as horizontal axis:

image
image1645×917 45.1 KB

Alternatively, look up each person’s events into People table with a lookup column, then calculate the sum of durations of those events, then build a chart based on People table.

3

Thanks so much Paul - I now have the duration column, but I am not seeing how on a people table, I can reference how many days one single contractor has worked, based on both:

Duration totals
Associations to event

I guess I mean what you have said here “Alternatively, look up each person’s events into People table with a lookup column, then calculate the sum of durations of those events, then build a chart based on People table.”