Convert String that represents duration to a Numeric Value (number of seconds) in DAX

[makarama]

Assuming that I have a string that contains any of the following words:

Day, Days, Hour, Hours, Minute, Minutes, Second, Seconds

I wanna grab the number in front of every appearing word and depending on the type of measure multiply it with the right number to get the total number of seconds.

For example if I have "2 Days 3 Minutes" then the code will calculate: 2 x 86400 + 3 x 60 = 172980

Example Input	Desired Outcome
2 Days 3 Minutes 1 Second	172981
1 Hour	3600
2 Hours 15 Minutes	8100
1 Day 3 Hours 1 Minute 10 Seconds	97270

There are two challenges:

1) the string might missing one of the key words. It could be a full string like the last example in the table, but also only one of the words or a combination of those. 

2) Both singular and plural forms of the words appear (Day, Days etc) depending on if it is 1 or 2 or more days.

[v-jingzhan-msft · Accepted solution]

Hi @makarama 

You can try this DAX formula:

TotalSeconds = 
VAR dd = SEARCH ( " Day", [TimeString],, 0 )
VAR Days = IF ( dd = 0, 0, VALUE ( MID ( [TimeString], 1, dd - 1 ) ) )
VAR hh = SEARCH ( " Hour", [TimeString],, 0 )
VAR Hours = IF ( hh = 0, 0, VALUE ( MID ( [TimeString], MAX ( hh - 2, 1 ), 2 ) ) )
VAR mm = SEARCH ( " Minute", [TimeString],, 0 )
VAR Minutes = IF ( mm = 0, 0, VALUE ( MID ( [TimeString], MAX ( mm - 2, 1 ), 2 ) ) )
VAR ss = SEARCH ( " Second", [TimeString],, 0 )
VAR Seconds = IF ( ss = 0, 0, VALUE ( MID ( [TimeString], MAX ( ss - 2, 1 ), 2 ) ) )
RETURN
    Days * 86400 + Hours * 3600 + Minutes * 60 + Seconds
    

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[DataNinja777]

Hi @makarama ,

The best approach to convert a duration string into total seconds in DAX is to systematically extract numeric values preceding time unit words (Day, Hour, Minute, Second) and multiply them by their corresponding values in seconds. The solution must account for both singular and plural forms of the time units while handling missing values gracefully. The optimal DAX formula for this is:

TotalSeconds =
VAR DurationString = 'Table'[Duration]

VAR Days = 
    IFERROR(
        LOOKUPVALUE( 
            VALUE(LEFT(DurationString, FIND(" Day", DurationString & " Day") - 1)), 
            TRUE, 
            TRUE
        ) * 86400, 
        0
    )

VAR Hours = 
    IFERROR(
        LOOKUPVALUE( 
            VALUE(LEFT(DurationString, FIND(" Hour", DurationString & " Hour") - 1)), 
            TRUE, 
            TRUE
        ) * 3600, 
        0
    )

VAR Minutes = 
    IFERROR(
        LOOKUPVALUE( 
            VALUE(LEFT(DurationString, FIND(" Minute", DurationString & " Minute") - 1)), 
            TRUE, 
            TRUE
        ) * 60, 
        0
    )

VAR Seconds = 
    IFERROR(
        LOOKUPVALUE( 
            VALUE(LEFT(DurationString, FIND(" Second", DurationString & " Second") - 1)), 
            TRUE, 
            TRUE
        ) * 1, 
        0
    )

RETURN
    Days + Hours + Minutes + Seconds

This formula ensures that each time unit is correctly identified, the number in front of it is extracted, and the result is multiplied by the appropriate conversion factor. The IFERROR function prevents calculation errors when a particular time unit is missing. This method efficiently handles different input variations, such as "2 Days 3 Minutes 1 Second" producing 172981, "1 Hour" returning 3600, and "1 Day 3 Hours 1 Minute 10 Seconds" calculating to 97270. This approach is concise, robust, and flexible for various input formats.

Best regards,