Forum Discussion
text to date
Need to transform the string which contain start and end date to seperat cell
- MAngostoIron Contributor
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.
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.