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.

 

[awoohaw]

@GreyFox8991 , can you use the xl2bb add in (link below) to post a mini workbook with some examples and (hand typed) expected results?
Images are not good for the forum users as we have recreate the data, which is detrimental to you two ways: it takes the forum member time to type all that into workbook, and they may make typos that affect the outcome. Help the forum help you!

If that is not possible, post the data in a table so the forum can at least copy and paste the values.

 

[GreyFox8991]

Hello Awoohaw,

Below is the code for the sheet:

Book1
	A	B
1	Current Incorrect State
2	ID	Date
3	123	2023-04-062023-04-072023-04-08
4
5
6
7	End State Correct Version 1
8	ID	Date
9	123	04/06/2023,04/07/2023,04/08/2023
10
11
12
13	End State Correct Version 2 (if possible)
14	123	4/6/2023
15	123	4/7/2023
16	123	4/8/2023
Sheet1

 

[GreyFox8991]

Current Incorrect State
ID	Date
123	2023-04-062023-04-072023-04-08
End State Correct Version 1
ID	Date
123	04/06/2023,04/07/2023,04/08/2023
End State Correct Version 2 (if possible)
123	4/6/2023
123	4/7/2023
123	4/8/2023

 

[fjns]

Hi, see the linked file (with 13 auxiliary columns) for a possible solution...

The formulas used in the table:
tech!A2: =IF(IncorrectState!A2="","",IncorrectState!A2)
tech!B2: =IF(A2="","",LEN(IncorrectState!B2))
tech!C2: =IF(A2="","",IF(MOD(B2,10)=0,INT(B2/10),0))
tech!D2: =IF($A2="","",IF(COLUMN()>$C2+3,"",IF(COLUMN()=4,"",",")&MID(IncorrectState!$B2,(COLUMN()-4)*10+6,2)&"/"&MID(IncorrectState!$B2,(COLUMN()-4)*10+9,2)&"/"&"/"&MID(IncorrectState!$B2,(COLUMN()-4)*10+1,4)))
EndStateV1!A2: =IF(tech!A2="","",tech!A2)
EndStateV1!B2: =CONCAT(tech!D2:M2)

Dates.xlsx

 

[Fluff]

@fjns the concat function does not exist in 2016, only 2019 & newer.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ID	Date
2	123	2023-04-062023-04-072023-04-08	123	06/04/2023	07/04/2023	08/04/2023
3	456	2021-01-112021-05-142021-09-242021-10-222021-12-15	123	11/01/2021	14/05/2021	24/09/2021	22/10/2021	15/12/2021
4
Main

Cell Formulas
Range	Formula
C2:C3	C2	=A$2
D2:I3	D2	=IFERROR(MID($B2,COLUMNS($D2:D2)*10-9,10)+0,"")

 

[fjns]

@Fluff
Is Microsoft Support showing incorrect information about the CONCAT function?

CONCAT function

 

[Fluff]

Unfortunately yes. I have found that site to be wrong with a number of functions. This site is more reliable Excel functions (alphabetical) - Microsoft Support

 

[fjns]

Hi, I created a new table without the CONCAT function.

DatesWithoutConcat.xlsx