Looking to creat a table that tracks the average hours spent on particular job types

[Cole_Hamel]

Hey guys,

Thank you in advance for any help you may be able to offer.

I’m a Project Manager at aa design studio, and what I’m looking to do is create a base that takes my team’s hours from our time-tracking software, and be able to look into the amount of time spent per job and make an average by job-type. For example, if I’m starting a web design project, I’d like to be able to look into my base, and see what the average amount of time (hours) that we spent on our previous web design projects.

I’ve already built out a table that should work, and have all of our data imported to it. Now I’m wondering how to get the average, per job-type.

Let me know if this makes sense, or if you have any other questions.

Again, thanks in advance.

[Jeremy_Oglesby]

I assume you have a field(column) where you define the Job Type for each record. Perhaps it’s a Single Select field?

If you just Group your view by this field, then all Project records of the same Job Type will be grouped together under a header row. In that header row, you will have summary functions available that can give you an Avg time spent on that Job Type.

Here’s what that header row looks like in one of my bases:

(notice I even have nested Groups and can see averages per each grouping level)

[Cole_Hamel]

That’s pretty close to what my view looks like, however, the tricky part to this is that the time tracking software our team uses (Harvest) does not sort by job type.
What I’ve done to try and solve this is by filtering by [Project name contains: Web Design], then created views for each job type. That might not be the best solution though.

[Jeremy_Oglesby]

I would create a formula field that checks the names and assigns a job type based on the name.
Something like this:

IF(
   FIND("Web Design", {Name}),
   "Web Design",
   IF(
      FIND("Branding", {Name}),
      "Branding Suite",
      IF(
         FIND( etc...),
         "etc.."
      )
   )
)

Then you can group by that field, and see all of your “Project Type” groups in a single view.

[Jeremy_Oglesby]

You could also just build out a custom time-tracking system within Airtable too ¯\_(ツ)_/¯…

They do have a “Time Tracker” block that can be used to track time on individual tasks, which you could link back to a Project, and have the time-tracking and analytics all in one place.

I don’t work for Airtable… just sayin…

[Cole_Hamel]

Oooh, this could be good.
I’ll go back to the drawing board and try this out.

Thank you immensely for your help.

[Jeremy_Oglesby]

@Cole_Hamel – I see that Harvest is a web application that already integrates with some other web apps…

How are you getting your data from Harvest into Airtable? If Harvest has an API you could get it from, then the data could be processed before insertion into Airtable, and it could take care of this categorization for you in the import process.

[Cole_Hamel]

As of now, I just exported CSV from Harvest, into AirTable, but I’m also thinking about making a Zapier Zap to connect the two.