Hours worked onsite for Entire Project

Jon L

I have a project that is setup in a worksheet, and each site has its own folder and project plan.

I am wanting to have the days calculated that have been worked, or passed, but only "onsite" parts of my project plan, not the office clerical side.

I then want to turn that to hours worked.

I then need it to go back to a central location, to log total hours worked on this project for a health and safety dashboard.

I am thinking I will need to identify each row that is considered "onsite" work, and then use a if today() then type statement to see if today has passed or not, and get a total at the bottom of the sheet. then multiply that by the hours of a typical day, I know it will not be exact but that is the best idea I currently have.

Am I on the right track above?

How would I get that to go back to the main project tracker, and when I make new folders for the next new project?

Thank you in advance for the help.

Genevieve P.

Hi @Jon L

Do you already have a column in your sheet that calculates the number of working days each row took to complete?

If so, you could use a SUMIFS function to add together all the days worked IF the row has a marker indicating it was Onsite and IF the End Date is in the past. Then you can multiply it by 8 (or however many hours you have in a working day).

For example:

=SUMIFS([Days Column]:[Days Column], [Work Location]:[Work Location], "Onsite", [End Date]:[End Date], < TODAY()) * 8

Depending on your plan type, you could put this in a Sheet Summary field or use a cross-sheet formula in a separate sheet:

=SUMIFS({Column with Days}, {Work Location Column}, "Onsite", {End Date Column}, < TODAY()) * 8

Let me know if this helped! If not, it would be useful to see a screen capture, but please block out sensitive data.

Thanks,

Genevieve