Parse dates into mm/dd/yyyy

[GreyFox8991]

Hello Excel Community,

I have a weird request which I am hoping someone might be able to provide a solution. I have rows where dates are provided in the following format yyyy-mm-dd and contain multiple dates in that format (Example: 2023-04-062023-04-072023-04-08). What I am trying to accomplish is to reformat the dates into mm/dd/yyyy and comma delimit them into the same row. The end result would be: (04/06/2023,04/07/2023,04/08/2023).

Another caveat would be there are ID's associated with these dates and I would then need to associate one ID with one Date. For example: ID:123456 has dates (2023-04-062023-04-072023-04-08). Instead of them being on the same row we split them so there would be three rows with the same ID and the dates in the mm/dd/yyyy (or m/dd/yyyy) I referenced above. So the end result would be:
123456: 04/06/2023
123456: 04/07/2023
123456: 04/07/2023

I am attaching a screenshot to show current state and possible end states. Any insight would be greatly appreciated. Thank you to all who assist.

 

[Fluff]

Can you please post the formulae you used. Thanks

 

[fjns]

The formulas used in the new table:
tech!A2: =IF(IncorrectState!A2="","",IncorrectState!A2) (Range:A2:A10)
tech!B2: =IF(A2="","",LEN(IncorrectState!B2)) (Range:B2:B10)
tech!C2: =IF(A2="","",IF(MOD(B2,10)=0,INT(B2/10),0)) (Range:C2:C10)
tech!D2: =IF($A2="","",IF(COLUMN()>$C2+3,"",MID(IncorrectState!$B2,(COLUMN()-4)*10+6,2)&"/"&MID(IncorrectState!$B2,(COLUMN)-4)*10+9,2)&"/"&MID(IncorrectState!$B2,(COLUMN()-4)*10+1,4))) (Range:D2:D10)
tech!E2: =IF($A2="","",IF(COLUMN()>$C2+3,"",D2&","&MID(IncorrectState!$B2,(COLUMN()-4)*10+6,2)&"/"&MID(IncorrectState!$B2,(COLUMN()-4)*10+9,2)&"/"&MID(IncorrectState!$B2,(COLUMN()-4)*10+1,4))) (Range:E2:M10)
EndStateV1!A2: =IF(tech!A2="","",tech!A2) (Range:A2:A10)
EndStateV1!B2: =IF(A2="","",IF(tech!C2=0,"",INDEX(tech!2:2,,tech!C2+3))) (Range:B2:B10)

 

[fjns]

Hi, a possible solution for the Version2 is also ready.

The formulas used in the newest table:
tech!A2: =IF(IncorrectState!A2="","",IncorrectState!A2) (Range:A2:A10)
tech!B2: =IF(A2="","",LEN(IncorrectState!B2)) (Range:B2:B10)
tech!C2: =IF(A2="","",IF(MOD(B2,10)=0,INT(B2/10),0)) (Range:C2:C10)
tech!D2: =SUM(C$1:C1)+2 (Range:D2:D10)
tech!E2: =SUM(C:C)+1
tech!F2: =IF($A2="","",IF(COLUMN()>$C2+5,"",IFERROR(DATE(MID(IncorrectState!$B2,(COLUMN()-6)*10+1,4),MID(IncorrectState!$B2,(COLUMN()-6)*10+6,2),MID(IncorrectState!$B2,(COLUMN()-6)*10+9,2)),""))) (Range:F2:O10)
EndStateV1!A2: =IF(ROW()>tech!E$2,"",INDEX(tech!A:A,MATCH(ROW(),tech!D:D,1))) (Range:A2:A50)
EndStateV1!B2: =IF(ROW()>tech!E$2,"",INDEX(INDIRECT("tech!"&MATCH(ROW(),tech!D:D,1)&":"&MATCH(ROW(),tech!D:D,1)),ROW()-INDEX(tech!D:D,MATCH(ROW(),tech!D:D,1))+6)) (Range:B2:B50)

DatesV2.xlsx

 

[fjns]

Sorry, an unnecessary character (period) was added to the format of column B of EndStateV2 in #13 message. I uploaded the corrected table again.

DatesV2c.xlsx