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

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!

These two customers are the only ones that have this requirement. That's why I'm trying to figure out a way to do this calculation in a column next to the totals shown on the pivot table. I used to just do the calculation manually in the spreadsheet, by filtering by those two customers, and then entering =E2-1 or =E2-2, for example, since there was only one line per employee, per job, per day. But, now that there are multiple lines, I can't do it this way. If I filter and then enter the formula on the spreadsheet, the way I was doing it previously, I would actually end up taking out 3 hours, if there were 3 rows per employee, or how many ever lines there are for each employee on that job. So, if they had 8 "punches" for the day, I'd actually subtract 8 hours total, which isn't what I want to do. I only need to subtract 1 or 2 hours.

Yes, Rory, they are, and I have about 5,000 more rows of data that I need to apply this to, thus the need to have this information calculated automatically, if at all possible. I could do it manually, after I've filtered the pivot table by Customer, but I'd obviously rather not have to do it that way. Thanks!

I'm sorry, Glenn, but I didn't understand your reference to the "Account" page of Excel. As it turns out, I wasn't seeing what you were talking about, probably because I'm not using O365, like I thought I was. I have two different computers. My personal one and a company laptop. The laptop is using O365, but my personal one only has Office Pro 2016. I've never upgraded my home computer. I thought both machines had the same version. Unfortunately, the version of the software being used on my laptop is dictated by our IT Dept., so I may not be able to upgrade it myself, as only IT team members have admin rights to install new software. However, I'm going to try, and then see if I can use your version to accomplish the task at hand. Thank you!