Forum Discussion

Ali5457's avatar
Ali5457
Frequent Visitor
3 years ago
Solved

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

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.

Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    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"

2 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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"