Date Format

[Dossfm0q]

As shown below in Right Side Table how can to formate
As shown below in the right Table, I need to arrange the Date in Date columns into Date format as it is in the main table.
In order to be easy for editing , I separated the Formula into two parts, the second Blue Part,that which is need to be edited

Thank you

new.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Desc	Desc2	Refrenace No.	Desc	Rev0				Rev1				Rev2				Remarks
2					Date Sub	Date Rec	Days	Code	Date Sub	Date Rec	Days	Code	Date Sub	Date Rec	Days	Code
3	dsdfdsf	sfdsf	xx-xx-xx-00001	ar	01-Jan-20	05-Jan-19	4	C	01-Jan-19	05-Jan-19	3	C
4	hjhjhgj	vcvbcvb	xx-xx-xx-00002	ar	01-Jan-19	10-Jan-19	9	C	01-Jan-19	05-Jan-19	3	C	01-Jan-19	10-Jan-19	4	C
5	aaa	vcvbcvb	xx-xx-xx-00003	ar	01-Jan-19	10-Jan-19	100	C
6
7	Dynamically
8	Formul		dsdfdsf,sfdsf,xx-xx-xx-00001,ar,
9	Formula		\43831,43470,4,C*43466,43470,3,C
10	Desc	Desc2	Refrenace No.	Desc	Date Sub	Date Rec	Days	Code
11	dsdfdsf	sfdsf	xx-xx-xx-00001	ar	43831	43470	4	C
12	dsdfdsf	sfdsf	xx-xx-xx-00001	ar	43466	43470	3	C
13	hjhjhgj	vcvbcvb	xx-xx-xx-00002	ar	43466	43475	9	C
14	hjhjhgj	vcvbcvb	xx-xx-xx-00002	ar	43466	43470	3	C
15	hjhjhgj	vcvbcvb	xx-xx-xx-00002	ar	43466	43475	4	C
16	aaa	vcvbcvb	xx-xx-xx-00003	ar	43466	43475	100	C
Sheet5

Cell Formulas
Range	Formula
C8	C8	=MID(""&TEXTJOIN(",",1,$A$3:$D$5)&",",TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",1,$A$3:$D$5)&",",",","|",(ROW(INDIRECT("1:"&ROWS($A$3:$D$5)))-1)*COLUMNS($A$3:$D$5)+1),(ROW(INDIRECT("1:"&ROWS($A$3:$D$5)))-1)*COLUMNS($A$3:$D$5)+1)),TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",1,$A$3:$D$5)&",",",","|",(ROW(INDIRECT("2:"&ROWS($A$3:$D$5)+1))-1)*COLUMNS($A$3:$D$5)+1),(ROW(INDIRECT("2:"&ROWS($A$3:$D$5)+1))-1)*COLUMNS($A$3:$D$5)+1))-TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",1,$A$3:$D$5)&",",",","|",(ROW(INDIRECT("1:"&ROWS($A$3:$D$5)))-1)*COLUMNS($A$3:$D$5)+1),(ROW(INDIRECT("1:"&ROWS($A$3:$D$5)))-1)*COLUMNS($A$3:$D$5)+1)))
C9	C9	=MID("\"&TEXTJOIN({",",",",",","*"},2,$E$3:$P$5)&";",TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",2,$E$3:$P$5)&",",",","|",COUNTIFS(OFFSET(INDIRECT(ADDRESS(ROW($E$3),COLUMN($E$3))),0,0,ROW(INDIRECT("1:"&ROWS($E$3:$P$5))),COLUMNS($E$3:$P$5)),"<>")-COUNTIF(OFFSET(INDIRECT(ADDRESS(ROW($E$3)-1+ROW(INDIRECT("1:"&ROWS($E$3:$P$5))),COLUMN($E$3))),0,0,1,COLUMNS($E$3:$P$5)),"<>")+1))),TRANSPOSE(MMULT(LEN($E$3:$P$5),ROW(INDIRECT("1:"&COLUMNS($E$3:$P$5)))^0)+COUNTIF(OFFSET(INDIRECT(ADDRESS(ROW($E$3)-1+ROW(INDIRECT("1:"&ROWS($E$3:$P$5))),COLUMN($E$3))),0,0,1,COLUMNS($E$3:$P$5)),"<>")))
A11:I16	A11	=TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(CONCAT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID("\"&TEXTJOIN({",",",",",","*"},2,$E$3:$P$5)&";",TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",2,$E$3:$P$5)&",",",","|",COUNTIFS(OFFSET(INDIRECT(ADDRESS(ROW($E$3),COLUMN($E$3))),0,0,ROW(INDIRECT("1:"&ROWS($E$3:$P$5))),COLUMNS($E$3:$P$5)),"<>")-COUNTIF(OFFSET(INDIRECT(ADDRESS(ROW($E$3)-1+ROW(INDIRECT("1:"&ROWS($E$3:$P$5))),COLUMN($E$3))),0,0,1,COLUMNS($E$3:$P$5)),"<>")+1))),TRANSPOSE(MMULT(LEN($E$3:$P$5),ROW(INDIRECT("1:"&COLUMNS($E$3:$P$5)))^0)+COUNTIF(OFFSET(INDIRECT(ADDRESS(ROW($E$3)-1+ROW(INDIRECT("1:"&ROWS($E$3:$P$5))),COLUMN($E$3))),0,0,1,COLUMNS($E$3:$P$5)),"<>"))),"*",";*"),"\","*"),"*",MID(""&TEXTJOIN(",",1,$A$3:$D$5)&",",TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",1,$A$3:$D$5)&",",",","|",(ROW(INDIRECT("1:"&ROWS($A$3:$D$5)))-1)*COLUMNS($A$3:$D$5)+1),(ROW(INDIRECT("1:"&ROWS($A$3:$D$5)))-1)*COLUMNS($A$3:$D$5)+1)),TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",1,$A$3:$D$5)&",",",","|",(ROW(INDIRECT("2:"&ROWS($A$3:$D$5)+1))-1)*COLUMNS($A$3:$D$5)+1),(ROW(INDIRECT("2:"&ROWS($A$3:$D$5)+1))-1)*COLUMNS($A$3:$D$5)+1))-TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",1,$A$3:$D$5)&",",",","|",(ROW(INDIRECT("1:"&ROWS($A$3:$D$5)))-1)*COLUMNS($A$3:$D$5)+1),(ROW(INDIRECT("1:"&ROWS($A$3:$D$5)))-1)*COLUMNS($A$3:$D$5)+1))))),";",REPT(" ",999)),(ROW()-ROW(A$11)+1)*999-998,999)),",",REPT(" ",999)),(COLUMN()-COLUMN(A$11)+1)*999-998,999))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

Is this something that you can use? This is just a quick test idea, there is probably some room for improvement.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Desc	Desc2	Refrenace No.	Desc	Rev0				Rev1				Rev2				Remarks
2					Date Sub	Date Rec	Days	Code	Date Sub	Date Rec	Days	Code	Date Sub	Date Rec	Days	Code		Index
3	dsdfdsf	sfdsf	xx-xx-xx-00001	ar	01/01/2020	05/01/2019	4	C	01/01/2019	05/01/2019	3	C						2
4	hjhjhgj	vcvbcvb	xx-xx-xx-00002	ar	01/01/2019	10/01/2019	9	C	01/01/2019	05/01/2019	3	C	01/01/2019	10/01/2019	4	C		5
5	aaa	vcvbcvb	xx-xx-xx-00003	ar	01/01/2019	10/01/2019	100	C										6
6
7	Dynamically
8	Desc	Desc2	Refrenace No.	Desc	Date Sub	Date Rec	Days	Code
9	dsdfdsf	sfdsf	xx-xx-xx-00001	ar	01/01/2020	05/01/2019	4	C
10	dsdfdsf	sfdsf	xx-xx-xx-00001	ar	01/01/2019	05/01/2019	3	C
11	hjhjhgj	vcvbcvb	xx-xx-xx-00002	ar	01/01/2019	10/01/2019	9	C
12	hjhjhgj	vcvbcvb	xx-xx-xx-00002	ar	01/01/2019	05/01/2019	3	C
13	hjhjhgj	vcvbcvb	xx-xx-xx-00002	ar	01/01/2019	10/01/2019	4	C
14	aaa	vcvbcvb	xx-xx-xx-00003	ar	01/01/2019	10/01/2019	100	C
Sheet1

Cell Formulas
Range	Formula
R3:R5	R3	=COUNTIFS($E$2:$P$2,"Date Sub",E3:P3,">0")+N(R2)
E9:H14	E9	=XLOOKUP(ROWS(A$9:A9),$R$3:$R$5,INDEX(FILTER($E$3:$P$5,$E$2:$P$2=E$8),0,COUNTIF($A$9:$A9,$A9)),"",1)
I9:I14	I9	=T(XLOOKUP(ROWS(I$9:I9),$R$3:$R$5,$Q$3:$Q$5,"",1))
A9:D14	A9	=XLOOKUP(ROWS(A$9:A9),$R$3:$R$5,$A$3:$D$5,"",1)
Dynamic array formulas.

 

[jasonb75]

It would be a good idea to update your profile with the relevant version of excel so that people don't have to guess what you can use. I just noticed mention of excel 2019 in one of your other threads so the suggestion above will not work for you.

This one will work with versions back to 2007, note that I've used a helper column in the upper table to make the formulas much shorter and more efficient.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Desc	Desc2	Refrenace No.	Desc	Rev0				Rev1				Rev2				Remarks
2					Date Sub	Date Rec	Days	Code	Date Sub	Date Rec	Days	Code	Date Sub	Date Rec	Days	Code		Index
3	dsdfdsf	sfdsf	xx-xx-xx-00001	ar	01/01/2020	05/01/2019	4	C	01/01/2019	05/01/2019	3	C						6
4	hjhjhgj	vcvbcvb	xx-xx-xx-00002	ar	01/01/2019	10/01/2019	9	C	01/01/2019	05/01/2019	3	C	01/01/2019	10/01/2019	4	C		4
5	aaa	vcvbcvb	xx-xx-xx-00003	ar	01/01/2019	10/01/2019	100	C										1
6
7	Dynamically
8	Desc	Desc2	Refrenace No.	Desc	Date Sub	Date Rec	Days	Code
9	dsdfdsf	sfdsf	xx-xx-xx-00001	ar	01/01/2020	05/01/2019	4	C
10	dsdfdsf	sfdsf	xx-xx-xx-00001	ar	01/01/2019	05/01/2019	3	C
11	hjhjhgj	vcvbcvb	xx-xx-xx-00002	ar	01/01/2019	10/01/2019	9	C
12	hjhjhgj	vcvbcvb	xx-xx-xx-00002	ar	01/01/2019	05/01/2019	3	C
13	hjhjhgj	vcvbcvb	xx-xx-xx-00002	ar	01/01/2019	10/01/2019	4	C
14	aaa	vcvbcvb	xx-xx-xx-00003	ar	01/01/2019	10/01/2019	100	C
15
Sheet1 (2)

Cell Formulas
Range	Formula
R3:R5	R3	=COUNTIFS($E$2:$P$2,"Date Sub",E3:P3,">0")+R4
A9:D15	A9	=IF(ROWS(A$9:A9)>$R$3,"",INDEX(A$3:A$5,MATCH($R$3-ROWS(A$9:A9)+1,$R$3:$R$5,-1)))
E9:H15	E9	=IF(ROWS(E$9:E9)>$R$3,"",INDEX(E$3:M$5,MATCH($R$3-ROWS(E$9:E9)+1,$R$3:$R$5,-1),(COUNTIFS($A$9:$A9,$A9,$B$9:$B9,$B9,$C$9:$C9,$C9)-1)*4+1))
I9:I14	I9	=IF(ROWS(I$9:I9)>$R$3,"",T(INDEX(Q$3:Q$5,MATCH($R$3-ROWS(I$9:I9)+1,$R$3:$R$5,-1))))