We allocate tips and sales bonuses at our company based on the time frames an employee worked. If someone comes in and buys our product at 1 PM, the various employees on the clock at the time of that transaction will receive a % of that sale and a share of any tips from the transaction (both split evenly between any employees clocked in).
---
**I have 2 tabs of data**:
* The Time Entries tab has individual clock in/out entries for the pay period - formatted like this:
* The Transactions tab has individual sales transactions for the pay period - formatted like this:
I was able to use SUMIFS to sum the Net Total (column C from my second screenshot) of transactions for each clock in/out time frame on the Time Entries tab, but this double counts transactions because we have multiple employees clocked in at the same time. Instead, I think I have to start with the Transactions tab and somehow associate a set of employees with each sale.
---
**Here are the alternative ways I've looked at formatting this:**
* I created a tab for the entire pay period from 12 AM of the first day through 11:59 PM of the last day, and each row represents a 10 minute increment. Then I summed the Total Sales from each 10 minute period based on the data from the Transactions tab. Here's what it looks like: . I created columns for each employee that would have formulas to determine if an employee was clocked in during that time frame (simple True/False would work), but I'm not sure where to go from there.
* On the Transactions tab, I added columns for each employee similar to the previous paragraph and need help with a formula that would determine whether the sale was during an employee's shift based on its timestamp. Here's what it looks like: . It would give a True/False response based on the employee's name in the column header matching with the employee's name associated with the time entry, so if multiple employees are clocked in at the time of the transaction, then those employees would all return Yes.
If I can get either of those 2 methods to return True/False answers by employee, it's simple math to divide up their tips and sales bonuses from there. What do you think? Is there a better way to slice this data?
Thanks!
---
**I have 2 tabs of data**:
* The Time Entries tab has individual clock in/out entries for the pay period - formatted like this:
* The Transactions tab has individual sales transactions for the pay period - formatted like this:
I was able to use SUMIFS to sum the Net Total (column C from my second screenshot) of transactions for each clock in/out time frame on the Time Entries tab, but this double counts transactions because we have multiple employees clocked in at the same time. Instead, I think I have to start with the Transactions tab and somehow associate a set of employees with each sale.
---
**Here are the alternative ways I've looked at formatting this:**
* I created a tab for the entire pay period from 12 AM of the first day through 11:59 PM of the last day, and each row represents a 10 minute increment. Then I summed the Total Sales from each 10 minute period based on the data from the Transactions tab. Here's what it looks like: . I created columns for each employee that would have formulas to determine if an employee was clocked in during that time frame (simple True/False would work), but I'm not sure where to go from there.
* On the Transactions tab, I added columns for each employee similar to the previous paragraph and need help with a formula that would determine whether the sale was during an employee's shift based on its timestamp. Here's what it looks like: . It would give a True/False response based on the employee's name in the column header matching with the employee's name associated with the time entry, so if multiple employees are clocked in at the time of the transaction, then those employees would all return Yes.
If I can get either of those 2 methods to return True/False answers by employee, it's simple math to divide up their tips and sales bonuses from there. What do you think? Is there a better way to slice this data?
Thanks!
