Convert date in text format into Days

[ssk_1984]

Hi All, 

help me create measure to convert the below format into days.

Format	Measure required  to convert days
11Y 5M 2D	4032
5Y 2M 11D	1896
11M 10D	340
10D	10
1M 5D	35
2D	2

[ssk_1984]

you are awsome... Thank you very much for help and invested some to bring this possible...😍

[Ritaf1983]

Glad to help 🙂

[Ritaf1983 · Accepted solution]

Hi @ssk_1984 
Try to add a calculated column with the DAX :

TotalDaysMonthsYears =

VAR FormatText = [Format]

VAR SpaceCount = LEN(FormatText) - LEN(SUBSTITUTE(FormatText, " ", ""))

VAR DaysPart =

    IF(

        CONTAINSSTRING(FormatText, "D"),

        IF(

            SpaceCount = 0,

            VALUE(LEFT(FormatText, FIND("D", FormatText) - 1)),

            IF(

                SpaceCount = 1,

                VALUE(MID(FormatText, FIND(" ", FormatText) + 1, FIND("D", FormatText) - FIND(" ", FormatText) - 1)),

                IF(

                    SpaceCount = 2,

                    VALUE(MID(FormatText, FIND(" ", FormatText, FIND(" ", FormatText) + 1) + 1, FIND("D", FormatText) - FIND(" ", FormatText, FIND(" ", FormatText) + 1) - 1)),

                    0

                )

            )

        ),

        0

    )

VAR MonthsPart =

    IF(

        CONTAINSSTRING(FormatText, "M"),

        IF(

            SpaceCount = 1,

            VALUE(LEFT(FormatText, FIND("M", FormatText) - 1)),

            IF(

                SpaceCount = 2,

                VALUE(MID(FormatText, FIND(" ", FormatText) + 1, FIND("M", FormatText) - FIND(" ", FormatText) - 1)),

                0

            )

        ),

        0

    )

VAR YearsPart =

    IF(

        CONTAINSSTRING(FormatText, "Y"),

        IF(

            SpaceCount = 2,

            VALUE(LEFT(FormatText, FIND("Y", FormatText) - 1)),

            0

        ),

        0

    )

RETURN DaysPart + MonthsPart * 30 + YearsPart * 365

please note: the result of the first row is 4167 (11*365+5*30+2) 

If you need the sample file, can download it from the Link 
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly