Calculating time duration between two times with the whole number and 24 hr formats

[Ali5457]

Hi,

I would like to calculate the time duration between the following two fields as hh:mm or mm. These two columnes are stored as a whole number, 24hr format. Also, as you could see not all of them are 4 digits. I have tried different solutions like converting them to the text as 4 digists like 1056 or 0856 (adding 0 to the beginning of those cells that their lenghts are less than 4) as stored them as the text, or changing them to 10:56 or 08:56 format and changed the type to the text, or even changing their data type to the time but none of them worked. In the latter case, all cells are changed to "12:00:00 am". In the other cases, I get all the errors related to data type that I cannot convert the value of type text to type number.

I have looked at all the post related to time function and text to time functions but nothing worked.

Thanks.

[Ali5457]

Yes, that worked. Thanks @ImkeF .

[ImkeF · Accepted solution]

Hi @Ali5457 ,
please check the following solution.
I am using the #time-function in there to construct the result:

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText("i45WsjA0U9JRsjQ1U4rViVYyNDa1BHINTUwMlGJjAQ==", BinaryEncoding.Base64),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [LogTime = _t, tmAtScene = _t]
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {{"LogTime", Int64.Type}, {"tmAtScene", Int64.Type}}
    ),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "DiffInTime",
        each #time(Number.IntegerDivide([tmAtScene], 100), Number.Mod([tmAtScene], 100), 0)
            - #time(Number.IntegerDivide([LogTime], 100), Number.Mod([LogTime], 100), 0)
    ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"DiffInTime", type duration}}),
    #"Inserted Total Minutes" = Table.AddColumn(
        #"Changed Type1",
        "Total Minutes",
        each Duration.TotalMinutes([DiffInTime]),
        type number
    )
in
    #"Inserted Total Minutes"