Oct 18 2021 09:26 AM - edited Oct 18 2021 11:01 AM
Hello,
I've been trying to setup a query to on a time detail report where each set of punches is on a different row. Originally we were accomplishing this with pivot tables, but recently noticed that the time wasn't being calculated correctly because some employees would have more than 4 punches a day. I've hit a wall and hope there is a solution. I've tried merging query's and unpivoting the columns then putting them back together. I cant seem to find a solution to getting all of the punches on one row. Any help would be appreciated.
Oct 18 2021 09:51 AM
@JamesMcQueen Difficult to say without the file on hand, I guess you need to calculate duration per row, then group by employee and day and sum duration. I understand you can't upload you real file, but perhaps you can extract the first 50 rows or so, replacing real names with some imaginary ones. Much easier to play with "real data".
Oct 18 2021 11:03 AM
Oct 18 2021 08:56 PM
@JamesMcQueen Not sure what kind of summary you have in mind mind. But perhaps something like in the attached file.
Oct 19 2021 05:29 AM
@Riny_van_Eekelen Thank you again for the assistance. I have attached a snippet of the how I'm trying to make the end result look like. For the the report that this data will be injected into I will need to show the start and end time punch's. and the max end time since the OT adds an additional clock in for some reason.
Oct 19 2021 06:03 AM
@JamesMcQueen So, when an employee has punched in and out 4 times during the day, you want 8 columns with these 4 start and 4 end times?
Oct 19 2021 06:16 AM
Oct 19 2021 06:41 AM
@JamesMcQueen Took a closer look at the data. It's a mess, if I may say so. You need to clarify what we are looking at. People with UNPAID meal breaks (i.e. not just a gap between two blocks of time), double and overlapping time entries with different work codes. There are employees with 6, 7 or even 8 line-items on the same day. How would you determine the LunchOUT and the LunchIN. Is it perhaps only the WRK codes you want to use for the summary report?
Oct 19 2021 07:44 AM
Oct 19 2021 11:15 PM
@JamesMcQueen For "Employee4112", your file contains the following time entries (rows 40 to 46):
How would you translate that to the 4 time columns that you mentioned before, if you had to do it manually?
The first start time (14:55) and the last end time (23:33) are easy. But where would you put the "LunchOut" and "LunchIN", where in this case, there is no obvious (lunch) break in the chain of events, although there are certain overlaps. My guess is that the total hours for this particular case equals 6.83+0.50+0.75+0.55 = 8.63. So, how would you want to reflect this in your report? And why? What's the logic?
Oct 20 2021 03:50 AM
In this case the "LunchOut" would be 21:45 and the "LunchIn" would be 22:15. PREM is for premium pay so those punch's are generated by the payroll system. The 23:03 clock out and clock in are generated by the payroll system. Any time an employee goes into OT the payroll system clocks them out and back in.
I have been using a combination of pivot tables and formulas off to the side to convert the data to this format, but recently noticed that this scenario and employee working into the next day would throw off the data.
We use this format because the tool we use to calculate productivity needs the payroll info to be formatted in this way. Thank you again for all your help with this.
Oct 20 2021 03:56 AM
Not quite. The goal is to get the four punch's for that day onto one row.
Thank you for helping to solve this.
Oct 20 2021 10:16 AM
Not quiet?? ok how many lines do you see for EMP01 on 10/1/2021 and how many punches do you see in the image and how does that not satisfy the specification of all punches for the day in one line? Well I hope you find your solution.
cheers
Oct 20 2021 01:09 PM
Oct 20 2021 02:08 PM
That's because it was my initial presentation to you inquiring if your desired result was something similar to the image I presented (so you can't expect in depth analysis to the data set).
So what do you suggest to do with the surprise bugs that come out of the wood work such as these:
employees with not four but six punches (probably the reason why it stuck out like a sore thumb in the initial image i presented)
Oct 20 2021 04:40 PM
@Yea_So In that situation we would take the last (Max) End Time punch. When an employee rolls into overtime the payroll system generates a start time and end time punch making it look like the employee entered six punches when they only entered four. Those generated punches aren't needed.
Oct 20 2021 04:42 PM
In other words the 2 punches in the middle will be removed from the spreadsheet and put where for auditing purposes?
Oct 20 2021 04:46 PM - edited Oct 20 2021 04:47 PM
The punches will always remain in the payroll system for auditing purposes. this report is used to measure performance and productivity. ...and yes the 2 middle punches will be removed.