troubleshooting Question

Formula or Pivot to figure out how many times a person "traded work shifts"

Avatar of Brent
BrentFlag for United States of America asked on
Microsoft ExcelMicrosoft Applications
5 Comments1 Solution298 ViewsLast Modified:

As firefighters we work 24 hour shifts. As part of our work schedule, we are allowed to "trade time" between 2 employees. If I want to take off X # of hours, then I can have another employee come to work for me and then at some point in the future, I will work for them to "payback" the hours I took off. Part of my job is to track these "trade times" or what we refer to as contracts between the two employees. One piece of data I need to know is how many of these contracts were completed each month. I attached a example worksheet to problem with coming up with this number.

The trouble is that as firefighters we are shifted between fire stations during our shifts. So, for the person taking the time off, there is only one entry (FF Smith took of 24 hours). But, for the person who is replacing him/her they might show up in the excel sheet 3,4 or 5 times because of them moving between stations. This data is exported out of a MySQL database, how it appears I can't control.

My logic tells me if I can count the names of people that are off for the date, then I can divide by 2 and it will give my the number of "contracts" for the date. I don't care if "JOE SMITH" shows up 10 times on the same date, I just want to count JOE SMITH once if he shows up on the date. I tried a pivot, but could not manipulate it how I wanted it.

I hope I made my goal clear enough.

Thank you. Brent
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros