Counting the time between two date

[CauseAndEffect]

Hey people!

We want to measure time time between two dates (two separate columns with the same format)

Format1 : YYYY-MM-DD HH:MM:SS

Format2:  YYYY-MM-DD HH:MM:SS

The needed output would be a column showing: HH:MM:SS with Date/time format, to be able to make calculations.

Tried the datefiff function but the return is only a singular whole number.

Time DIFF = 2019-09-21 10:16:39; 2019-09-21 10:21:22; MINUTE)

Output: 5

Wanted output: 00:04:43

I'm able to achieve this by using a function I found online: 

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486Duration

But this returns the time as text, and therefore I cannot make any calculations.

Thankful for any tips or insights!

[v-eachen-msft · Accepted solution]

Hi @CauseAndEffect ,

Just minus two dates. Then set the date type to be Time ( HH:mm:ss ).
Here is the result.

[CauseAndEffect]

Thanks! This worked! 

[CauseAndEffect]

Relised that the topic was moved, here is the function:

Duration = 
// Duration formatting 
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = [Duration in Seconds]
// There are 3,600 seconds in an hour
VAR Hours =
    INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
    INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds =
    ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
// Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
// Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
    CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )