Calculating Hours by person by projects from date range

[cvoith]

Hey guys,

I am trying to calculate out hours for our personnel worked on projects each day. We only have the start and end dates of projects, we were not tracking hours individually so this'll be a rough estimate at the end of the day. Here what I'm working with:

Project Tracker table: This is the hub for all the project information including the start date and end date for each project. It links to the personnel table through a crew linked field.
Personnel table: Contains all the information about a person.

What I'm looking for:
I need to calculate from a persons allotted 8 hours how many hours were worked on each project for each day. These hours will be split evenly to total 8 hours. The final numbers would show us:
- Person total hours per project
- Project Total hours from all personnel

Here's a visual of what I'm thinking (not how it would look in airtable)

Hopefully this makes sense. Please let me know if anyone has any idea on how to accomplish this.

Thanks!

[Matt_Jastremski]

I've built something like this recently for a client.

You will likely need a third table along the lines of "Time Entries" where each record links a person, a project, and a date, and the number of hours that person worked on that project that day.

Then with rollups and other methods you can show how many hours a given person worked on a project, how many hours they worked on a day (week, etc), total hours worked on a project, etc.

[TheTimeSavingCo]

I think you're going to need a table where each record represents a single day and person, and to each of these you'd link all the projects they were working on for that day.  You'd then be able to find that person's split for each project for that day, and you could then create another table where each record represented a single person and project, and you'd create a roll up summing up the splits there, does that make sense?

[cvoith]

I understand the "Dates" table where each record is a single day and person with all project linked to that record. Can you explain the second part/table? I get the each record is a person + project, but the roll up I am confused on. Where am I rolling up from, personnel, project tracker, or dates table?

Thanks!

[TheTimeSavingCo · Accepted solution]

You'd be rolling up from the single-day and person table!  I've put something together here for you to check out:

[cvoith]

Thats whats I'm looking for, that Person <> Projects table. Amazing!

Got another follow up for you (sorry), would you know how to make an automation to automatically make the Single Day <> Person link the the Person <> Project table? I'm trying to build it but I can't seem to get it to find records that contain Person and Project since a day can have multiple projects attached to it. Let me know if you can help me with this one. 

Thank you so much for what you've provided so far!

[TheTimeSavingCo · Accepted solution]

Yeap try creating a formula field with this formula:

Person & " - " & 
SUBSTITUTE(
  Projects,
  ',',
  ',' & Person & " - "
)

Then get an automation to paste its value to the linked field

 

[cvoith]

Thank you so much!