Convert Days, Hours, Minutes and seconds in to Seconds

[NavyaMounika]

Hi, 

 I have followed the solution based on the attached article

Solved: Re: Sum of duration of task with Days, Hours, Minu... - Page 2 - Microsoft Fabric Community

However, I'm getting an error.. for the same I have attached snapshot.

Please help me with the possible resolution on the below mentioned elapsed time.

Here i have to convert the below elapsed time in to seconds.

Business Elapsed Time

10 Minutes

0 Seconds

2 Hours 30 Minutes

1 Hour 32 Minutes

4 Hours 1 Minute

2 Hours

1 Hour

1 Hour 1 Minute

1 Hour 16 Minutes

4 Hours 21 Minutes

1 Hour 46 Minutes

5 Days 12 Hours 16 Minutes

1 Day 10 Hours 23 Minutes

1 Day 1 Hour 46 Minutes

5 Days

1 Day 13 Minutes

1 Day 8 Hours

295 Days 22 Hours 2 Minutes

1 Day 27 Minutes

1 Day 1 Hour 15 Minutes

1 Day 4 Hours 1 Minute

6 Days 42 Minutes

1 Day 17 Hours 42 Minutes

1 Day 5 Minutes

6 Days 10 Hours 59 Minutes

10 Days 21 Hours 40 Minutes

1 Day 2 Hours 18 Minutes

2 Days 12 Hours 28 Minutes

1 Day 2 Hours 12 Minutes

2 Days 21 Hours 9 Minutes

22 Days 12 Hours

1 Day 37 Minutes

2 Days 11 Hours 5 Minutes

15 Days 1 Hour

2 Days 7 Hours 52 Minutes

2 Days 9 Hours 40 Minutes

9 Days 15 Hours 50 Minutes

7 Days 21 Hours

2 Days 27 Minutes

162 Days 15 Hours 51 Minutes

1 Day 9 Hours 19 Minutes

1 Day 5 Hours 47 Minutes

26 Days 3 Hours 10 Minutes

2 Days 9 Hours 44 Minutes

1 Day 12 Hours 23 Minutes

5 Days 23 Minutes

155 Days 1 Hour 46 Minutes

1 Day 3 Hours 14 Minutes

5 Days 14 Hours 40 Minutes

2 Days 1 Hour 5 Minutes

2 Days 1 Hour 47 Minutes

6 Days 22 Hours 6 Minutes

13 Days 3 Hours 15 Minutes

12 Days 9 Hours

1 Day 15 Hours 36 Minutes

1 Day 11 Hours 46 Minutes

1 Day 6 Hours 41 Minutes

9 Days 18 Hours 24 Minutes

8 Days 20 Hours 7 Minutes

1 Day 16 Hours 9 Minutes

3 Days 17 Hours 8 Minutes

1 Day 14 Hours 32 Minutes

6 Days 18 Hours

4 Days 6 Hours 9 Minutes

2 Days 6 Hours 48 Minutes

27 Days 22 Hours 30 Minutes

5 Days 19 Hours 2 Minutes

1 Day 3 Hours 34 Minutes

4 Days 7 Hours 11 Minutes

1 Day 6 Hours 39 Minutes

2 Days 5 Hours 34 Minutes

1 Day 6 Hours 26 Minutes

1 Day 8 Hours 8 Minutes

9 Days 18 Hours 23 Minutes

1 Day 6 Hours 1 Minute

1 Day 1 Hour 10 Minutes

1 Day 1 Hour 1 Minute

2 Days 2 Hours

1 Day 1 Hour

6 Days 43 Minutes

1 Day 1 Hour 37 Minutes

1 Day 3 Hours

1 Day 8 Hours 28 Minutes

2 Days 17 Hours 1 Minute

1 Day 59 Minutes

[v-zhengdxu-msft · Accepted solution]

Hi @NavyaMounika 

Pleasr try this:
Here I create a calculated column:

Column =
VAR _ifday =
    FIND ( "Day", 'Table'[Business Elapsed Time],, BLANK () )
VAR _ifhour =
    FIND ( "Hour", 'Table'[Business Elapsed Time],, BLANK () )
VAR _ifminute =
    FIND ( "Minute", 'Table'[Business Elapsed Time],, BLANK () )
VAR _ifsecond =
    FIND ( "Second", 'Table'[Business Elapsed Time],, BLANK () )
VAR _HourALt =
    IF ( _ifhour - 3 <= 1, 1, _ifhour - 3 )
VAR _minuteALt =
    IF ( _ifminute - 3 <= 1, 1, _ifminute - 3 )
VAR _secondAlt =
    IF ( _ifsecond - 3 <= 1, 1, _ifsecond - 3 )
RETURN
    IF (
        _ifday <> BLANK (),
        VALUE ( MID ( 'Table'[Business Elapsed Time], 1, _ifday - 1 ) )
    ) * 86400
        + IF (
            _ifhour <> BLANK (),
            VALUE (
                IFERROR ( MID ( 'Table'[Business Elapsed Time], _HourALt, 2 ), BLANK () )
            )
        ) * 3600
        + IF (
            _ifminute <> BLANK (),
            VALUE (
                IFERROR ( MID ( 'Table'[Business Elapsed Time], _minuteALt, 2 ), BLANK () )
            )
        ) * 60
        + IF (
            _ifsecond <> BLANK (),
            VALUE (
                IFERROR ( MID ( 'Table'[Business Elapsed Time], _secondAlt, 2 ), BLANK () )
            )
        )

 The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NavyaMounika]

Thank You So Much... Really appreciated of solution............

[Ashish_Mathur]

Hi,

If at all possibl to solve, it will have to be solved in Power Query.  Would you be OK with that?

[Syndicate_Admin]

we can create calculated column to get it resolved.... I need a help for the same

[NavyaMounika]

we can create calculated column to get it resolved.... I need a help for the same

[Ashish_Mathur]

Hi,

I have solved this using Power Query.  Please find attached the Excel file.

[Syndicate_Admin]

Exactly alot of inconsistenct format like day/days, minute/minutes, hour/hours... I dont have other option because this data is coming from tool itself. Please help me with the resolution

[Syndicate_Admin]

I Have followed the below process .

You will need to create 4 new Columns in your Dataview.

Do this:

Column1:

TIME_Days = (IF(FORMAT(LEFT(TestData[Actual_Elp_Duration],FIND(" Days",TestData[Actual_Elp_Duration],1,0)),"General Number")="","0",FORMAT(LEFT(TestData[Actual_Elp_Duration],FIND(" Days",TestData[Actual_Elp_Duration],1,0)),"General Number"))) * 1440

Column2:

TIME_Hours = SUBSTITUTE(IF(SEARCH("Hours",TestData[Actual_Elp_Duration],1,"0")=0,"0",IF(SEARCH("Days",TestData[Actual_Elp_Duration],1,0)<>0,RIGHT(LEFT(SUBSTITUTE(TestData[Actual_Elp_Duration]," Days",""),5),3),LEFT(TestData[Actual_Elp_Duration],2)))," ","")*60

Column3:

TIME_Minutes = (IF(SEARCH("Minutes",TestData[Actual_Elp_Duration],1,0)=0,"0",RIGHT(SUBSTITUTE(TestData[Actual_Elp_Duration]," Minutes",""),2))*1)

Column4:

TIME_Total_Minutes = TestData[TIME_Days]+TestData[TIME_Hours]+TestData[TIME_Minutes]

but i was getting an error of can't convert value of '5 D' of text to type number

[NavyaMounika]

I Have followed the below process .

You will need to create 4 new Columns in your Dataview.

Do this:

Column1:

TIME_Days = (IF(FORMAT(LEFT(TestData[Actual_Elp_Duration],FIND(" Days",TestData[Actual_Elp_Duration],1,0)),"General Number")="","0",FORMAT(LEFT(TestData[Actual_Elp_Duration],FIND(" Days",TestData[Actual_Elp_Duration],1,0)),"General Number"))) * 1440

Column2:

TIME_Hours = SUBSTITUTE(IF(SEARCH("Hours",TestData[Actual_Elp_Duration],1,"0")=0,"0",IF(SEARCH("Days",TestData[Actual_Elp_Duration],1,0)<>0,RIGHT(LEFT(SUBSTITUTE(TestData[Actual_Elp_Duration]," Days",""),5),3),LEFT(TestData[Actual_Elp_Duration],2)))," ","")*60

Column3:

TIME_Minutes = (IF(SEARCH("Minutes",TestData[Actual_Elp_Duration],1,0)=0,"0",RIGHT(SUBSTITUTE(TestData[Actual_Elp_Duration]," Minutes",""),2))*1)

Column4:

TIME_Total_Minutes = TestData[TIME_Days]+TestData[TIME_Hours]+TestData[TIME_Minutes]

but i was getting an error of can't convert value of '5 D' of text to type number

[Anonymous]

I've not ran the code to look but you've got inconsistancy in your days format, from Day and Days, that is likely playing a part in this. 

[Anonymous]

@NavyaMounika - Can you paste the code, screenshot isn't viewable due to how far its zoomed out. 

Thanks ,