Forum Discussion
Calculate Time Difference between 2 cross days
- 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 -
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.
- Matrix -
- hemas-naidu2 years agoFrequent Visitor
I will try and update you soon.. thanks