Forum Discussion
Solved
Convert date in text format into Days
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 ...
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 * 365please 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
