Forum Discussion
Split Time
I have 2 Columns with Time in and Time out in Excel which I brought into Power Query (formatted as Date/Time). I want to create a column with the hours between 7a-3p. I tried the following formula which works for most times
let
timeIn = Time.From([Pay Rule Start]),
timeOut = Time.From([Pay Rule End]),
workStart = #time(7, 0, 0), // 7 am
workEnd = #time(15, 0, 0), // 3 pm
duration = if timeIn <= workStart then
if timeOut >= workEnd then workEnd - workStart else timeOut - workStart
else if timeIn >= workEnd then #duration(0) // No hours worked
else if timeOut >= workEnd then workEnd - timeIn
else timeOut - timeIn,
hoursWorked = if Duration.From(duration) < #duration(0, 0, 0, 0) then #duration(0, 0, 0, 0) else Duration.From(duration) / #duration(0, 1, 0, 0) // Convert duration to hours
in
hoursWorked
Im getting an error for some times thought that Expression.Error: 1 arguments were passed to a function which expects 4.
Details:
Pattern=
Arguments=[List]
example of times that getting this error is 9/17/2023 6:45:00 PM and 9/17/2023 7:15:00 PM
1 - why is this formula not working for all times and how can I update to work?
2 - is there a simpler formula that can be used?
Appreciate you spending the time looking at this and Thanks in advance for your help!
Hi,
if Time.From([Pay Rule End]) > Time.From([Pay Rule Start])
then List.Max(
{0,List.Min({15, 24*Number.From(Time.From([Pay Rule End]))})
-List.Max({7, 24*Number.From(Time.From([Pay Rule Start]))})})
else List.Max({0, 15 - 24*Number.From(Time.From([Pay Rule Start]))})
+List.Max({0, 24*Number.From(Time.From([Pay Rule End]))-7}))Stéphane
10 Replies
a visual inspection shows that this line is not calling the duration function properly:
else if timeIn >= workEnd then #duration(0) // No hours worked
Provide some sample data and expected results and i'll have a look.
It won't be for a few hours though.
See below. Besides for the issue of getting the hours for the 11p-7a shift, the bolded times below, dont calcuate with formula above for hours between 7a-3p. Think its because shift starts the night before.
1- how can I update the above formula to include hours between 7a-3p even if shift starts night before
2 - what formula can I use to calcuate hours between 11p-7a (if I get the correct formula for 7a-3p ad 3p-7a, I really can take total hours worked minus hours from 7a-3p and 3p-11p)Pay Rule StartPay Rule End
9/18/2023 23:30 9/19/2023 7:30 9/17/2023 6:30 9/17/2023 15:00 9/18/2023 23:30 9/19/2023 7:15 9/17/2023 6:30 9/17/2023 7:00 9/19/2023 6:30 9/19/2023 19:00 9/17/2023 5:45 9/17/2023 22:15 9/17/2023 6:30 9/17/2023 22:00 9/17/2023 6:15 9/17/2023 20:45 9/18/2023 6:30 9/18/2023 23:30 9/18/2023 6:15 9/18/2023 14:00 9/19/2023 5:45 9/19/2023 6:00 9/17/2023 6:15 9/17/2023 10:00 9/18/2023 6:15 9/18/2023 18:30 9/18/2023 6:30 9/18/2023 23:15 9/18/2023 23:45 9/19/2023 6:45 9/19/2023 23:30 9/20/2023 7:00 9/19/2023 23:45 9/20/2023 7:00 9/19/2023 23:45 9/20/2023 7:00 9/18/2023 23:45 9/19/2023 7:00 9/18/2023 6:30 9/18/2023 14:00 9/18/2023 23:45 9/19/2023 7:00 9/18/2023 23:45 9/19/2023 7:00 9/19/2023 6:30 9/19/2023 23:30 9/19/2023 6:30 9/19/2023 7:00 9/19/2023 6:30 9/19/2023 15:00
Hi
or
List.Max(
{List.Min({#time(15,0,0),Time.From([Pay Rule End])})
-List.Max({#time(7,0,0),Time.From([Pay Rule Start])}),
#duration(0,0,0,0)})
/#duration(0,1,0,0)Stéphane
Thanks slorin! appreciate you looking at this! this formula is much simpler than the original and easier to use.
As you see above, Im having an issue with shifts starting night before are not calculating correctly. This is happening with this formula as well. For example, a shift that starts 23:30 and ends 8:30, Im getting 0 even if I should get 1.5
Can you review and let me know how I can update to include those hours?Thanks in advance!
Hi,
if Time.From([Pay Rule End]) > Time.From([Pay Rule Start])
then List.Max(
{0,List.Min({15, 24*Number.From(Time.From([Pay Rule End]))})
-List.Max({7, 24*Number.From(Time.From([Pay Rule Start]))})})
else List.Max({0, 15 - 24*Number.From(Time.From([Pay Rule Start]))})
+List.Max({0, 24*Number.From(Time.From([Pay Rule End]))-7}))Stéphane
slorin Found another issue with this formula. Its generating more than 8 hours for some punches even though there cant be more than 8 hours between 7 and 3
Example -
Second Punch In =9/21/2023 11:03:00 PM
Second Punch Out =9/22/2023 3:31:00 PM
formula=if [Second Punch In] = null then 0
else if Time.From([Second Punch Out]) > Time.From([Second Punch In]) then
List.Max(
{0, List.Min({15, 24 * Number.From(Time.From([Second Punch Out]))})
- List.Max({7, 24 * Number.From(Time.From([Second Punch In]))})})
else
List.Max({0, 15 - 24 * Number.From(Time.From([Second Punch In]))})
+ List.Max({0, 24 * Number.From(Time.From([Second Punch Out])) - 7})
What am I missing?
Thanks in advanced!Anyone can help me with this? Im trying to split punches by shift time but formula below is giving me more than 8 hours for some shifts which doesnt make sense. Any assistance would be appreciated!
Example -
Second Punch In =9/21/2023 11:03:00 PM
Second Punch Out =9/22/2023 3:31:00 PM
formula=if [Second Punch In] = null then 0
else if Time.From([Second Punch Out]) > Time.From([Second Punch In]) then
List.Max(
{0, List.Min({15, 24 * Number.From(Time.From([Second Punch Out]))})
- List.Max({7, 24 * Number.From(Time.From([Second Punch In]))})})
else
List.Max({0, 15 - 24 * Number.From(Time.From([Second Punch In]))})
+ List.Max({0, 24 * Number.From(Time.From([Second Punch Out])) - 7})Thanks in advanced!
