Creating A Column In A Pivot Table Based On Certain Data In The Table

[WmSWoodson]

Hello All!

I have an Excel spreadsheet that I've exported from our timekeeping software, which includes columns for Job Description, Employee Name, Start & End Times, and a Total for that particular punch time. The issue I'm having is that certain customers require that I report the daily time for each employee as a total, minus any travel time to and from the job. Previously, this was an easy task, since there was only one row per job, per day, per employee, and I could create a new column, only subtracting the specific travel time for that entry. However, starting this month, we've been required to breakdown the punch times for various work stoppages (i.e., customer delays, equipment delays, meal periods, etc.), so there may be as many as 8-10 rows for the same job, for the same day, for the same employee, and there's no way to get a total of the hours worked per day, except to create a pivot table. I'm struggling with how to create a column on the pivot table that would allow me to automatically subtract an hour from the total hours shown as the pivot table will change size, dynamically, depending on which job I filter on.

As an example, any job description containing the customer, FM Safford, would need to have a total of 1 hour subtracted from each employee's total time for that job on whatever day it was reported, whereas, if the customer is FM Morenci, then that job needs to have 2 hours subtracted from the total time shown for each employee.

I have attached a sample spreadsheet and pivot table the way I need them to appear.

Thank you for any help you can provide!