0

So I'm making a punch in/out dashboard in google sheets. It uses a google form to populate a sheet with my employees punches like so:

Timestamp          | Name        |  Punch Type      | Time
6/2/2020 15:09:55  | Bob         |  1. Start Shift  | 7:30:00 AM
6/2/2020 15:10:45  | Bob         |  2. Start Lunch  | 11:00:00 AM
6/2/2020 15:11:08  | Bob         |  3. End Lunch    | 11:30:00 AM
6/2/2020 16:01:04  | Bob         |  4. End Day      | 4:00:00 PM
...

I then used this source data to make a pivot table that looks like this:

AVERAGE of Time | Punch Type        
Name            | 1. Start Shift    | 2. Start Lunch    | 3. End Lunch  | 4. End Day
Bob             | 7:30:00 AM        | 11:00:00 AM       | 11:30:00 AM   | 4:00:00 PM
...

In this pivot table, I want to add a column at the end that is a calculated field of

("4. End Day" - "1. Start Shift") - ("3. End Lunch" - "2. Start Lunch").

I'm encountering two road blocks here. First is when I go to add a calculated field in the pivot table editor panel, it creates 4 new columns instead of just one:

                | Punch Type | Values   
                | 1. Start Shift      | 2. Start Lunch      | 3. End Lunch         | 4. End Day
Name            | AVERAGE of Time..   | AVERAGE of Time..   | AVERAGE of Time..    | AVERAGE of Time..
Bob             | 7:30:00 AM | 0      | 11:00:00 AM | 0     | 11:30:00 AM | 0      | 4:00:00 PM | 0
...

I the second issue is I can't figure out of to reference the columns with the timestamps to do this calculation.

Basically my end goal is a pivot table that looks like this:

AVERAGE of Time | Punch Type        
Name            | 1. Start Shift    | 2. Start Lunch    | 3. End Lunch  | 4. End Day | Total Hours
Bob             | 7:30:00 AM        | 11:00:00 AM       | 11:30:00 AM   | 4:00:00 PM | 8.0
...

Displayed below is how I have my Pivot Table settings in the Pivot Table Editor Panel, before I attempt to add the calculated field

enter image description here

enter image description here

Phillyclause89
  • 674
  • 4
  • 12
  • As an update I was able to make this total hours column by just adding a formula outside the pivot table that ref's the specific cells. This isn't ideal for me because I have to adjust the range as my staff size changes, but it works. Would love to know how/if I can do this from within the pivot table its self. – Phillyclause89 Jun 03 '20 at 22:06
  • Could you please share a copy of the spreadsheet you are working on? – Iamblichus Jun 04 '20 at 12:26

0 Answers0