Forum Discussion

QuasemS's avatar
QuasemS
Frequent Visitor
10 years ago
Solved

Calculating day/time difference

I have two columns, both with dates and times in the same format. I want to create a new column to calculate the days/times differences between the two columns. But when I try to change the data type...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    QuasemS

     

    You can also add a custom column in Query Editor.

    =Duration.ToText([AlarmClearedUTC]-[AlarmSentUTC])

     

     

    And then in DAX

    Column = 
IF (
    LEFT ( Table1[Duration], IFERROR ( SEARCH ( ".", Table1[Duration] ), 1 ) - 1 )
        = "",
    0,
    LEFT ( Table1[Duration], IFERROR ( SEARCH ( ".", Table1[Duration] ), 1 ) - 1 )
)
    & " DAY "
    & RIGHT ( Table1[Duration], 8 )

     

    Or Duration.ToRecord and expand?

    Duration.ToRecord([AlarmClearedUTC]-[AlarmSentUTC])

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
10 years ago
QuasemS wrote:

I have two columns, both with dates and times in the same format. I want to create a new column to calculate the days/times differences between the two columns. But when I try to change the data type to date/time, it gives me an error which makes sense because it's looking for an actualy date, whereas I am trying to calculate the difference between two dates.

 

Anybody know a good way to do this?

 

Thanks

QuasemS

 

What is the format of those two column? The data type conversion error indicates the format can't predicated correctly. Please post some sample of those column, if they're in the same format, it can be easy to re-format and convert to date.

  • QuasemS's avatar
    QuasemS
    Frequent Visitor
    10 years ago

    Here's a snapshot of the two columns. I'm trying to find the difference between the second and the first one:

     

    So the result wouldn't be a date, but in days/minutes/seconds format.

     

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      10 years ago

      In your Query Editor window, add a custom column with a formula of:

       

      [AlarmSentUTC] - [AlarmClearedUTC]

      This will result in a Duration field that you can then use the blog article I posted earlier to format into the format you want.

      • QuasemS's avatar
        QuasemS
        Frequent Visitor
        10 years ago

        I would get a raw number for that column. Do I format it using this:

         

        Hours = INT([Duration in Seconds]/3600)
        Minutes = INT(MOD([Duration in Seconds],3600)/60)
        Seconds = MOD(MOD([Duration in Seconds],3600),60)
        H = IF(LEN([Hours])=1,CONCATENATE("0",[Hours]),CONCATENATE("",[Hours]))
        M = IF(LEN([Minutes])=1,CONCATENATE("0",[Minutes]),CONCATENATE("",[Minutes]))
        S = IF(LEN([Seconds])=1,CONCATENATE("0",[Seconds]),CONCATENATE("",[Seconds]))