Convert string date format to actual date range

Something like =TEXT(VALUE(LEFT(cell,5)),"mm/dd/yyyy")&" - "&TEXT(VALUE(RIGHT(cell,5)),"mm/dd/yyyy"). Replace "mm/dd/yyyy" with your favorite date format code.

You would have to split the column using Text to Columns using the delimiter "-" . Then multiply each cell by 1 to convert to number. Then format as a date. Next you will need to concatenate the two cells of Date information.

EDIT: Or simply apply the formula supplied by MrShorty

Originally Posted by helam

Hi again,

Sorry, now I ran my data and found out there are some with more than one Date string within one cell such as 44624 - 44635, 44683 - 44697, 44702 - 44706. Is there a way to convert it to actual date ranges within one cell or I have to split it to 3 cells before convert it? I would like to have it converted within one cell. Thanks.

Ros

=TEXTJOIN(CHAR(10),TRUE,TEXT(LEFT(FILTERXML("<x><y>"&SUBSTITUTE(A11,", ","</y><y>")&"</y></x>","//y"),FIND(" - ",FILTERXML("<x><y>"&SUBSTITUTE(A11,", ","</y><y>")&"</y></x>","//y"),1)),"dd mmm yyyy")&" -"&TEXT(RIGHT(FILTERXML("<x><y>"&SUBSTITUTE(A11,", ","</y><y>")&"</y></x>","//y"),LEN(FILTERXML("<x><y>"&SUBSTITUTE(A11,", ","</y><y>")&"</y></x>","//y"))-FIND(" - ",FILTERXML("<x><y>"&SUBSTITUTE(A11,", ","</y><y>")&"</y></x>","//y"),1)-2),"dd mmm yyyy"))

Replace A11 with your date range cell.
Result will be generated in ONE cell by TEXTJOIN formula, delimited by char(10) / newline. Make sure you activated Wrap Text
Too bad TEXTJOIN provided in Excel 2019 and later. Didn't knew your Excel version is update or not.
And this formula designed to work for any serial date not only =5 characters but including <>5

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.