How to calculate the sum of hours spent per employee on each task at the end of the day?

[Diego_Merlino]

Per day, the employee works 8 hours and can be distributed in one or more projects (tasks) in this period.

If he worked on only one project it would be 8 hours allocated to the project (easy), however if he worked on two projects it would be 4 hours each and so on.

I need to generate a report of each person’s allocated hours by project.

The challenge is that the tasks from different projects overlap for a period of days and I don’t know how to evenly subdivide those 8 hours by the amount of overlapping tasks.

[Databaser]

Hi @Diego_Merlino and welcome to the community!

You can create 3 tables:

1. employees
2. projects
3. time table

In your “time table” (first field can be “date”), create a number/duration field for time tracking (I would use a number field) + 2 linked fields: one for selecting employee (only 1 allowed) and one for selecting the project (multiple allowed). That way, your employee can add time to multiple projects for the same time duration.

In the “projects” table, add a rollup field that takes the “sum(values)” for the linked field (linked to your “time table”). Here you can add conditions, eg, only rollup the time spend by employee x. If you don’t have to many of them, you can have 1 field per employee per project. Add a formula field to add everything so you can calculate the time spent over the total project.

Or, of course, you can also use a chart via the dashboard functionality to get the same info, but then you won’t be able to do some calculations (eg, hours per employee per project x hourly cost).

[Diego_Merlino]

Thank you for your quick answer. I appreciated it.
I’ve put my question better detailed in a real example attached here.
I have projects that sometimes overlap each other, and during these days, the employee will distribute his 8 hours equally for each project.

Many thanks for your assistance.

[Databaser]

Hmm, I don’t really see the challenge here. If your employees fill in their “time table” right each day they have 2 choices imo:

• allocate their time (eg 4h) to x projects (so 4h can be charged several times over different projects)
• split their time (eg 4h) over x projects (so 4h could be 4 x 1h allocated to 4 different projects)

If you want both, you could add a field to add their real workings hours (eg 4h) in addition to the hours that are charged (eg 4h for 2 projects each).

But maybe I’m missing something :blush:

[BogdanB]

Hallo,

I’m already doing what you want to achieve, but I’m using Integromat for automation and Toggl for time tracking, all alongside Airtable. I don’t know if this suits your needs, but let me explain the workflow in few words.

Toggl give me the chance to change the client or/and the project everytime I want, so the time tracking is not a problem at all. After that, with Integromat, I can watch at the end of the day / week all the time entries and then, by using Integromat, I can send them to specified Airtable fields.

Unfortunately, it’s not an 100% Airtable pure solution but I think it can help you.

[ClarifyThis]

Hi @Diego_Merlino sorry to resurrect this old post but, I'm looking into a very similar challenge and wondering if you found a way to solve your problem?

My version is: each record expresses an $X over dates A to B. We apportion that out to a daily rate over a period. Every day will have different amounts from overlapping (by date) records. I'm interested in the day-by-day sum / overall trend of that value.

A standard thing to do in Excel / PBI / SQL, but I can't see an elegant solution coming out of [just] Airtable?