Split Time

[MStark]

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!

[slorin · Accepted solution]

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 

[MStark]

@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!

[MStark]

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!

[MStark]

@slorin This works! Ive been spending so much time on this and this is really helpful!! Appreciate all your time and help!

[slorin]

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 

[MStark]

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!

[HotChilli]

Provide some sample data and expected results and i'll have a look.

It won't be for a few hours though.

[MStark]

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

[HotChilli]

a visual inspection shows that this line is not calling the duration function properly:

else if timeIn >= workEnd then #duration(0) // No hours worked

[MStark]

Wow! @HotChilli your a genius! Cant believe I missed that!

Next question, this doesnt work for the 11p-7a shift and I believe its because the range is over midnight. Is that correct and how can I update so it works for that overnight shift?

Thanks so much!!