Re: Calculate time worked on project

E_R1 · 08-29-2019 11:40 AM

Hello all;
I’m new here and maybe this is simple, but I’m having a hard time figuring this out.
I’m working on an app where I wanna be able to track time spent on certain tasks within a project.

I have a sheet for ‘time punch’ where users punch that has the following columns:
Date, Time Start, Time Finish, Total Time Task, Project, Comment, By
Task, and project is referenced to the main ‘project’ sheet and is chosen by drop-down and total time is auto calculated from time start and time finish.

I did another sheet where I wanna be able to have a total of hours worked on each task within the project extracted from the time punch sheet. here are the columns in that sheet:
Date, Project, Sub Project, Customer, Task1, Task2, etc.
I need the column of each task to Sum all hours worked on ‘Project’ from the ‘time punch’ sheet. In other words, search sheet ‘time punch’ for ‘task1’ together with ‘project’ and return the sum. I will have multiple rows for each task, as the tasks are usually not finished in 1 time punch.

Hope I’m clear.
Please advise.

Mariano_Sanz_Gi

Hi @E_R
I’m working on that too now. I have the data in Google sheet with the structure project > task > time worked and in a tab I make the sum of hours per project and other parameters that can be extracted from the table of hours using Google’s QUERY function in this way:

This is my table of hours worked on each task:

This is the summary table of each project generated by the QUERY function with that SELECT

I hope it helps you.

E_R1

Sorry, I didn’t get it as I’m not at all familiar with google query’s.
I’m looking for a formula that will work in appsheet.
Sum of (time punch[total time]) for all rows that contain [project name] & [task].

Mariano_Sanz_Gi

A virtual field in the parent table with a type expression might be used to get the sum of the child table values:
SUM(SELECT(timepunch[total time],AND([projectname]=[_THISROW].[projectname],[task]=[_THISROW].[task])))

E_R1

Slightly changed as follows:
SUM(SELECT(timepunch[total time],AND([projectname]=[_THISROW].[projectname],[task]=task)))

It works. Thanks!

P.S. Is there an expression list?

Steve

Toward the bottom.