Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Time Difference in Power Query

Hi Experts   How would you calculate the difference in meeting start time and meeting end time in Power Query.  Sample Data   Start Time End Time 11:30:00   13:00:00 16:30:00   15:...
Need Help
  • vojtechsima's avatar
    vojtechsima
    4 years ago

    Anonymous 
    Add a new column like this:

     

    let
Difference = [End Time] - [Start Time],
Day = Number.Abs(Duration.Days(Difference)),
Hour =  Number.Abs(Duration.Hours(Difference)),
Minute =  Number.Abs(Duration.Minutes(Difference)),
Second =  Number.Abs(Duration.Seconds(Difference)),
FullDurationAbs = #duration(Day, Hour, Minute, Second),
NextDayDuration = #duration(0,24,0,0) - FullDurationAbs

in
if [Start Time] > [End Time] then NextDayDuration  else Difference

     

     

vojtechsima's avatar
vojtechsima
Super User
4 years ago

Hi, Anonymous 
simply like this:

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi - thats what i did but i get negitive time...

  • vojtechsima's avatar
    vojtechsima
    Super User
    4 years ago

    Anonymous 
    Because Start Time is bigger than End Time, do you want me to recreate and check if the Start Time is bigger, it takes the time to another day? so it would 22,5 hours?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      yes please

      • vojtechsima's avatar
        vojtechsima
        Super User
        4 years ago

        Anonymous 
        Add a new column like this:

         

        let
Difference = [End Time] - [Start Time],
Day = Number.Abs(Duration.Days(Difference)),
Hour =  Number.Abs(Duration.Hours(Difference)),
Minute =  Number.Abs(Duration.Minutes(Difference)),
Second =  Number.Abs(Duration.Seconds(Difference)),
FullDurationAbs = #duration(Day, Hour, Minute, Second),
NextDayDuration = #duration(0,24,0,0) - FullDurationAbs

in
if [Start Time] > [End Time] then NextDayDuration  else Difference