Forum Discussion

MStark's avatar
MStark
Icon for Helper III rankHelper III
2 years ago
Solved

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 wh...
Need Help
  • slorin's avatar
    slorin
    2 years ago

    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's avatar
slorin
Icon for Super User rankSuper User
2 years ago

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's avatar
MStark
Icon for Helper III rankHelper III
2 years ago

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!