Forum Discussion

Abdellatif1995's avatar
Abdellatif1995
Copper Contributor
May 18, 2022
Solved

Convert text to date

Hi All, I need help with this issue please , I have this column with ages written in format like for example  ( 2 years , 1 year , 13 month , 8 weeks ) I want to convert all these data to reflect ...
BI & Data Analysis
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    May 18, 2022

    Hi Abdellatif1995 

     

    in B2:

     

    =LET(
    delimP, FIND(" ",A2),
    number, --LEFT(A2,delimP-1),
    period, SUBSTITUTE(MID(A2,delimP+1,LEN(A2)-delimP),"s",""),
    SWITCH(period,
        "year",  number*12,
        "month", number,
        "week",  number/4,
        "Unknown"
    )
)

    If you don't run Excel 2021 or 365:

    =IFERROR(
    VALUE(LEFT(A2,FIND(" ",A2)-1)) *
    LOOKUP(
        SUBSTITUTE(MID(A2,FIND(" ",A2)+1,LEN(A2)-FIND(" ",A2)),"s",""),
        {"month","week","year"},
        {1,0.25,12}
    ),
    "Unknown"
)

     

Resources