Forum Discussion

hemas-naidu's avatar
hemas-naidu
Frequent Visitor
2 years ago
Solved

Calculate Time Difference between 2 cross days

Hi guys, Intention is to have the Diff hours based on Date cut off Need help to calculate the following Duration_Mins. Currently at Row 5. the StartTimeStamp (17th Jul 23:17) and EndTimeStamp (18th...
  • mickey64's avatar
    2 years ago

    Step 0: I use these data and calculate by 'Minutes'.

     

    Step 1: I duplicate the 'Duration_Mins' column.

     

    Step 2: I chande the type of 'Duration_Mins - Copy' column.

        'Duration_Mins - Copy' column: 'Time' -->  'Decimal Number'

     

    Step 3: I rename the 'Duration_Mins - Copy' column.

        'Duration_Mins - Copy' --> 'Duration_Day'

     

    Step 4: I add a 'Duration_Min' column and change the type of the column.

     

    Step 5: I add a 'Day' column and rename it. ('Day' --> 'S_Day')

     

    Step 6: I add a 'Day' column and rename it. ('Day' --> 'E_Day')

     

    Step 7: I add the 'Flag' column.

     

    Step 8: I unpivot 'StartTimeStamp' and 'EndTimeStamp' columns.

    - Before -

    - After -

     

    Step 8: I add a column and make a matrix.

    Cal_Duration_min = IF([Flag]="Divide" && [Attribute]="StartTimeStamp",DATEDIFF([Value],DATE(YEAR([Value]),MONTH([Value]),[E_Day]),MINUTE),IF([Flag]="Divide" && [Attribute]="EndTimeStamp",DATEDIFF(DATE(YEAR([Value]),MONTH([Value]),[E_Day]),[Value],MINUTE),IF([Attribute]="StartTimeStamp",1440*[Duration_Mins],0)))

     - Matrix -