Forum Discussion

swabeeh's avatar
swabeeh
Copper Contributor
Apr 23, 2024

text to date

 

Need to transform the string which contain start and end date to seperat cell

 

 

 

 

 

 

 

  • MAngosto's avatar
    MAngosto
    Iron Contributor

    swabeeh 

     

    Assuming your base dates are in form of three letters and 2 numbers, serparated by "-" (e.g. Jun 06 - Jun 21, 24), you can use:

     

    =TEXTJOIN(", ",,LEFT(E4,6),RIGHT(E4,2))

    In your start date cell.

     

    And,

     

    =RIGHT(E4,LEN(E4)-FIND("-",E4)-1)

    In your end date cell.

     

    That would achieve your desired goal to transform the string into two separated cells. You would then apply the corresponding formatting to look as desired.

     

  • swabeeh 

    Or:

     

    =LET(md, TEXTBEFORE(B2, " -"), d, TEXTAFTER(md, " "), m, TEXTBEFORE(md, " "), y, TEXTAFTER(B2, ", "), DATEVALUE(d&"-"&m&"-"&y))

     

    and

     

    =LET(md, TEXTAFTER(TEXTBEFORE(B2, ","), "- "), d, TEXTAFTER(md, " "), m, TEXTBEFORE(md, " "), y, TEXTAFTER(B2, ", "), DATEVALUE(d&"-"&m&"-"&y))

     

    Format the cells with the formulas as date, then fill down.

Resources