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.