Log In
Register
Home
Excel
Word
Outlook
Excel Expert
Power Pivot
PowerPoint
April 10, 2024
Offline
Hi, I have been asked to identify for each employee how many "shifts" they have worked on a day.
the emps clock in an clock out multiple times a day.
if they do not have more than a 1 hour break between a pair of clock in/outs, they have one "shift" for the day - their earliest clock in and their latest clock out time.
If they have more than an hour break then I need to split the clockings into two / three etc "shifts" for the same day.
I am a little lost.
I have attached the end result I am looking for.
In my own workbook (not the attached), I have been able to identify the cumulative count of shifts per employee per day and the gaps between clockings to find those that are more than an hour, and have tried using Min, Max on the dates/time to bring the eariest and latest times but it feels very clumsy and im not even there yet!
Was curious to see if anyone else had the time or inclination to share some savvy ways to get where I need to?
Moderators
January 31, 2022
Offline
Please see attached. It's not the neatest solution and probably not the best either. But it's the best I can think of at the moment.
Note that the first row is special and requires two cells to be fixed with a value of 01:00 but displayed as a blank through a custom format.
Other formulas return either 0 or a value greater than 0. Zeroes in columns J:G get suppressed through a custom format.
The most difficult part was the End column. The formula took some trial and error cycles to capture all options, but I tested it by entering another break of more than 1 hour and believe it works.
Oh, and this only works for one employee at the time. So, each employee would require his/her own special first row and set range of clock times.
1 Guest(s)
