Formula for Client-Specific Payroll Report

1

Hi! I work for a general contractor and I’m hoping to migrate to Coda full-time soon for all aspects of our project management and human resources.

I’ve managed to learn quite a bit by combing through these threads and working through a lot of trial and error with lots of different formulas, but this one in particular has me stumped.

I want to be able to open up a “Payroll Report” section each week in order to run payroll. I need this report to total up each employee’s hours by day (I’ve managed to do this). AND I’d like to be able to SUM each time entry per job site. I’ve created a dummy doc to give you an idea of what I’m trying to do:

The Payroll Report section gives an example of the table that I’m stuck with, including the formula I can’t seem to get right. And below that is an example of what I’d like that table to ultimately look like.

It’s important to me to be able to have the control filter functionality based on date at the top of the page, which is why I thought that a “View of” the Time Log table would work best, so I wouldn’t have to continually update the data.

It seems like something like this would be possible but I just can’t figure it out :sob: Thanks in advance for your help and your input!

Farrah

2

Hello Farrah! There are a couple of things you can do in this scenario:

  1. In your view of Time Log that you have in the Payroll Report you can group also by Jobsite and then summarize, it would look like this:
    image

  2. The second option is a bit more cumbersome, you would need to create a new table with the combination of the User and Jobsite. Then you can make a filter to get the hours worked by each.
    In this case I recommend a Section to fill the needed data of the hours worked and with the push of a button it fills both the Time Log table and the New table.

I just found your Hours per Jobsite Column and, though I do not recommend doing that because of performace reason as you would do the same calculation a couple of times when the same conditions are met, it didn’t work because you were always comparing for example the date with thisRow, and those are two different types of data, to make it work you have to compare dates with dates and Users with users, so instead of Date=thisRow it would be Date=thisRow.Date. So the formula would look like this:
[Time Log].Filter(Date=thisRow.Date and Jobsite=thisRow.Jobsite and User=thisRow.User).[Hours Worked].Sum()

3

Wow, I had no idea you could group multiple columns! This is perfect, thank you so much.

1 Like
4

Hi Saul:

I hit another roadblock! I’m trying to create some version of a “utilization report” to show how much time each employee will be allotted to complete each task, as well as if any of them are over- or under-scheduled. This is what that currently looks like:

33%20AM
33%20AM1550×1052 41.4 KB

I need to be able to see each individual team member, not how they are grouped together, and be able to look at individual dates as well. Is this possible?

Farrah

5

Hello Farrah!
Yes, it is possible. You have to make another table with all separate Dates and Persons. With the help of some buttons I was able to do just that. Check it out:

6

Cool, thanks so much for getting back to me! Is there any way to do this without creating a whole separate table? I’d like for this information to populate on its own without me having to build it out. Is that possible?

Thank you!

7

No problem, I’m glad to help!
Unfortunately no, it is a limitation of Charts in Coda :slightly_frowning_face:
But you can make an automation so you don’t have to press the button to update the chart