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 

MStark's avatar
MStark
Helper III
2 years ago

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

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!