Forum Discussion
Splitting Employee Punches by Factory Shift
Thank you spinfuzer! This works for the 11p-7a shift but not generating correctly for all punches. For example getting 0 for these punches when theres around 1.5 hours that fall in the 11p-7a shift
First Punch InFirst Punch Out
| 12/1/2022 5:35 | 12/1/2022 14:28 |
Im also having an issue with the code Im using for the 3p-11p shift since some punches are generating more than 8 hours.
For Example -
Code Im using to generate the 3-11 1 column is:
if Time.From([First Punch Out]) > Time.From([First Punch In])
then List.Max(
{0,List.Min({23, 24*Number.From(Time.From([First Punch Out]))})
-List.Max({15, 24*Number.From(Time.From([First Punch In]))})})
else List.Max({0, 23 - 24*Number.From(Time.From([First Punch In]))})
+List.Max({0, 24*Number.From(Time.From([First Punch Out]))-15})
What am I doing wrong here since its not possible to have more than 8 hours between 3p-11p (unless the punch times are over 24 hours which none are). Any way you can help?
Thanks in advance!
Check this one for the 2nd shift. I still believe that doing it the original way i posted, or anyone elses that breaks up the shifts into 8 hours increments and then pivoting the results is the best approach. What if you have someone who works so long that they are in shift 2 prior day and end in shift 2 next day for some reason? Forcing the times into 3 columns implies that we are never going to have employees that work into the same shift in the next day(s).
Let me think about the other shift formula. It is probably a similar approach to below.
if [First Punch In] = null then 0
else
List.Max({Duration.TotalMinutes(
if [First Punch Out] >= Date.From([First Punch In]) & #time(23,0,0) // end after start of shift
then
List.Min(
{
[First Punch Out],
Date.From([First Punch In]) & #time(0,0,0) + #duration(1,7,0,0) // next day end of shift
}
) -
List.Max(
{
[First Punch In],
Date.From([First Punch In]) & #time(23,0,0)
}
)
else //if [First Punch In] <= Date.From([First Punch In]) & #time(7,0,0) then Duration.TotalMinutes(
List.Min(
{
[First Punch Out],
Date.From([First Punch In]) & #time(7,0,0) // same day end of shift
}
) -
List.Max(
{
[First Punch In],
Date.From([First Punch In]) & #time(0,0,0) - #duration(1,0,0,0) // prior start of shift
}
)
)/60
,0
})
Appreciate all your help spinfuzer! Trying the code in your original response but getting the following error
Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[List]let
punch_in = [First Punch In], // update to punch in column
punch_out = [First Punch Out], //update to punch out column
shifts = {#time(7,0,0), #time(15,0,0), #time(23,0,0)},
start_of_shift = Date.From(punch_in) &
List.Select(
shifts,
(x) => [
a = Duration.Hours(punch_in-(Date.From(punch_in) & x)),
b = a < 8 and a >= 0
][b]
){0},
generate_shifts = List.Generate( () =>
[start = start_of_shift, end = start + #duration(0,8,0,0)],
(x) => punch_out > x[start],
(x) => [start = x[start] + #duration(0,8,0,0), end = start + #duration(0,8,0,0)],
(x) => [
shift_start = x[start],
shift_end = x[end],
hrs = Duration.TotalMinutes(
List.Min({punch_out,shift_end})-
List.Max({punch_in,shift_start})
)/60,
shift = Text.From(Number.Mod(Time.Hour(shift_start),12)) & " - " & Text.From(Number.Mod(Time.Hour(shift_end),12))
]
)
in
Table.FromRecords(generate_shifts)Try this one. Did not account for start of shifts in the prior day.
let punch_in = [First Punch In], // update to punch in column punch_out = [First Punch Out], //update to punch out column shifts = {#duration(0,7,0,0), #duration(0,15,0,0), #duration(0,23,0,0), #duration(-1,7,0,0), #duration(-1,15,0,0), #duration(-1,23,0,0)}, start_of_shift = Date.From(punch_in) & #time(0,0,0) + List.Select( shifts, (x) => [ a = Duration.Hours(punch_in-(Date.From(punch_in) & #time(0,0,0) + x)), b = a < 8 and a >= 0 ][b] ){0} , generate_shifts = List.Generate( () => [start = start_of_shift, end = start + #duration(0,8,0,0)], (x) => punch_out > x[start], (x) => [start = x[start] + #duration(0,8,0,0), end = start + #duration(0,8,0,0)], (x) => [ shift_start = x[start], shift_end = x[end], hrs = Duration.TotalMinutes( List.Min({punch_out,shift_end})- List.Max({punch_in,shift_start}) )/60, shift = Text.From(Number.Mod(Time.Hour(shift_start),12)) & " - " & Text.From(Number.Mod(Time.Hour(shift_end),12)) ] ) in try Table.FromRecords(generate_shifts) otherwise null