cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

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

9 REPLIES 9
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 II

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

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

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 II

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 II

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors