Invoicing Time/Tracking Reference Formula

[daniel_jacob_self]

Hi!

Here’s what I’m trying to figure out a formula for:

I have a table where I track time spent on various projects for an organization (each with their own unique code/name). It has the usual columns: name/code, date, hours spent, description of service, rate… and then I have a toggle in each row of that table labeled “invoiced?”

Then, I have a separate page with my name, address, payment information, and a view of that table that is filtered for invoice toggle = off. I download a pdf of this page and send it to the org each month as an invoice! Then I toggle each line as “invoiced” and continue on my merry way!

Great!

Here’s where it gets tricky:

The org has just asked me to submit my invoices with one line per project with the total amount of uninvoiced hours spent on that project.

So instead of

12/1/23 rjc4590 $60/h 1h $60
12/15/23 rjc4590 $60/h 1h $60
12/17/23 rjc5550 $60/h 1h $60
12/20/23 rjc4590 $60/h 1h $60

They want

December 2023
rjc4590 $60/h 3h $180
rjc 5550 $60/h 1h $60

etc.

So now I think I need to create a whole separate table, referencing the original time tracking table but doing something fancy so that each line is adding up all the rows for each project name/code that have the “invoice” toggle off.

Any help in crafting that formula? Or better ideas?

Thank y’all so much!!

[daniel_jacob_self]

FIGURED IT OUT!

Here’s what I ended up with:

Created a new table with the display column as a reference/lookup to the time tracking table’s display column (i.e. the project name: rjc4590 etc.)

Then created a column called “hours” with this formula:

[Time Tracking].CountIf(AND([Invc'd]=false,Project=thisRow.Name))

Then I created another row that simply multiplied the “hours” row times my hourly rate to get my total!

VOILA!

Still would love to hear if anyone has any other thoughts / ideas / inspiration… hope this helps someone else!!!