Forum Discussion

ssk_1984's avatar
ssk_1984
Helper II
2 years ago
Solved

Convert date in text format into Days

Hi All, 

 

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

FormatMeasure required  to convert days
11Y 5M 2D4032
5Y 2M 11D1896
11M 10D340
10D10
1M 5D35
2D2

 

 

 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    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

3 Replies

  • ssk_1984's avatar
    ssk_1984
    Helper II
    2 years ago

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

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    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