Calculate elapsed time

[Syndicate_Admin]

Can you help me

To create a column where the elapsed time is shown with the format DD, HH:MM:SS , in some data the Close Date field is empty there should be placed the current day.

=IF(J3>0;SI(K3>0; ($K3-$J 3); (TODAY()-J3));"")

 

[amustafa · Accepted solution]

Here's how to fix it in Excel, M Query or DAX...

Excel: =IF(ISBLANK(B2),
IF(A2 > NOW(),
"0, 00:00:00",
TEXT(INT(NOW() - A2),"0") & ", " & TEXT(NOW() - A2, "hh:mm:ss")),
IF(A2 > B2,
"0, 00:00:00",
TEXT(INT(B2 - A2),"0") & ", " & TEXT(B2 - A2, "hh:mm:ss")))

M Query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\aliom\OneDrive\Power BI Samples\Time Lapsed with Condition\Sample.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"FECCHA APERTURA", type datetime}, {"FECHA CIERRE", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "TIEMPO (dd, hh:mm:ss) MCode", each 
        let
            fechaApertura = [FECCHA APERTURA],
            fechaCierre = if [FECHA CIERRE] <> null then [FECHA CIERRE] else DateTime.LocalNow(),
            duration = if fechaApertura > fechaCierre then #duration(0, 0, 0, 0) else fechaCierre - fechaApertura,
            days = Duration.Days(duration),
            hours = Duration.Hours(duration - #duration(days, 0, 0, 0)),
            minutes = Duration.Minutes(duration - #duration(days, hours, 0, 0)),
            seconds = Duration.Seconds(duration - #duration(days, hours, minutes, 0)),
            durationText = if fechaApertura > fechaCierre then
                                "0, 00:00:00"
                            else
                                Text.From(days) & ", " & Text.PadStart(Text.From(hours), 2, "0") & ":" & 
                                Text.PadStart(Text.From(minutes), 2, "0") & ":" & 
                                Text.PadStart(Text.From(Number.RoundDown(seconds)), 2, "0")
        in
            durationText)
in
    #"Added Custom"

DAX:

TIEMPO (dd, hh:mm:ss) DAX = 
VAR FechaApertura = Table1[FECCHA APERTURA]
VAR FechaCierre = IF(ISBLANK(Table1[FECHA CIERRE]), NOW(), Table1[FECHA CIERRE])
VAR FechaCierreToUse = IF(FechaApertura > NOW(), FechaCierre, NOW())
VAR Duration = FechaCierreToUse - FechaApertura
VAR Days = ROUNDDOWN(Duration, 0)
VAR Hours = HOUR(Duration - (Days * 1))
VAR Minutes = MINUTE(Duration - (Days * 1) - (Hours / 24))
VAR Seconds = SECOND(Duration - (Days * 1) - (Hours / 24) - (Minutes / 1440))
RETURN
IF(
    FechaApertura > FechaCierreToUse,
    "0, 00:00:00",
    FORMAT(Days, "00") & ", " & FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")
)