Forum Discussion

MStark's avatar
MStark
Helper 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
Super User
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 

MStark's avatar
MStark
Helper III
2 years ago

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