cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

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

1 ACCEPTED SOLUTION
Super User

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 10
Super User

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

Helper III

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

Helper III

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})

Helper III

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

Super User

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

Helper III

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?

Super User

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

It won't be for a few hours though.

Helper III

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
Super User

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

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

Helper III

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.