Forum Discussion

Arnaud_37's avatar
Arnaud_37
New Member
4 years ago
Solved

Calculate date between 2 rows

Hi all,

 

I want to calculate the duration of an action.

I have a row which indicates the DateTime when the action starts. The column "Message" ends with "démarrée".

I have another row which indicates the DateTime when the action ends. The column "Message" ends with "terminée"

I want to add a column and calculate the Date difference between "terminée" and "démarrée"

 

thanks for your help

 

Arnaud_37

Need Help
  • BITomS's avatar
    BITomS
    4 years ago

    Arnaud_37 

     

    Okay, so if you revert to your second screenshot (your initial response to me), you could select one of the pivoted columns (start or end) and then select 'Fill' from the Transform tab within the ribbon (Up or Down will depend on if you've used start or end). Then you can just deselect the NULLs from the other column and I think this should get you there.

9 Replies

  • BITomS's avatar
    BITomS
    Solution Supplier
    4 years ago

    Hi Arnaud_37,

     

    You can use the Pivot Column functionality. If you select the Message column and go to 'Transform' in the ribbon > Pivot Column, you can Set the value as DateHeure (Don't Aggregate):

     

     

    Then you can go to 'Add Column' within the ribbon to add a new custom column which will be a simple End minus Start time formula to get the duration. Hope this helps.

    • Arnaud_37's avatar
      Arnaud_37
      New Member
      4 years ago

      Hi BITomS ,

      Thank you for your answer

      The problem is that I always find myself having to calculate a duration according to 2 dates that are on two lines

      Regards

      • BITomS's avatar
        BITomS
        Solution Supplier
        4 years ago

        Arnaud_37 providing all other column values, other than Message and DateHeure, are the same for the start and end rows, this should not happen (i.e. if all other columns have consistent values that relate to the same 'action', pivoting the data will consolidate everything into one row).

         

        Your latest screenshot suggests there is (at least) one column where the row values do not match for the same action, so you need to ensure your table only contains relevant columns with consistent values per action - looking at your screenshot from your original post, I am wondering if you have removed the 'Heure' column before attempting to pivot the data? As this is different across both start and end rows, it is probably what is causing your data to not pivot correctly. Try removing this column first and see if this makes a difference.