Date Format

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQ
1DescDesc2Refrenace No.DescRev0Rev1Rev2Remarks
2Date SubDate RecDaysCodeDate SubDate RecDaysCodeDate SubDate RecDaysCode
3dsdfdsfsfdsfxx-xx-xx-00001ar01-Jan-2005-Jan-194C01-Jan-1905-Jan-193C
4hjhjhgjvcvbcvbxx-xx-xx-00002ar01-Jan-1910-Jan-199C01-Jan-1905-Jan-193C01-Jan-1910-Jan-194C
5aaavcvbcvbxx-xx-xx-00003ar01-Jan-1910-Jan-19100C
6
7Dynamically
8Formuldsdfdsf,sfdsf,xx-xx-xx-00001,ar,
9Formula\43831,43470,4,C*43466,43470,3,C
10DescDesc2Refrenace No.DescDate SubDate RecDaysCode
11dsdfdsfsfdsfxx-xx-xx-00001ar43831434704C 
12dsdfdsfsfdsfxx-xx-xx-00001ar43466434703C 
13hjhjhgjvcvbcvbxx-xx-xx-00002ar43466434759C 
14hjhjhgjvcvbcvbxx-xx-xx-00002ar43466434703C 
15hjhjhgjvcvbcvbxx-xx-xx-00002ar43466434754C 
16aaavcvbcvbxx-xx-xx-00003ar4346643475100C 
Sheet5
Cell Formulas
RangeFormula
C8C8=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)))
C9C9=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:I16A11=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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
Is this something that you can use? This is just a quick test idea, there is probably some room for improvement.
Book1
ABCDEFGHIJKLMNOPQR
1DescDesc2Refrenace No.DescRev0Rev1Rev2Remarks
2Date SubDate RecDaysCodeDate SubDate RecDaysCodeDate SubDate RecDaysCodeIndex
3dsdfdsfsfdsfxx-xx-xx-00001ar01/01/202005/01/20194C01/01/201905/01/20193C2
4hjhjhgjvcvbcvbxx-xx-xx-00002ar01/01/201910/01/20199C01/01/201905/01/20193C01/01/201910/01/20194C5
5aaavcvbcvbxx-xx-xx-00003ar01/01/201910/01/2019100C6
6
7Dynamically
8DescDesc2Refrenace No.DescDate SubDate RecDaysCode
9dsdfdsfsfdsfxx-xx-xx-00001ar01/01/202005/01/20194C 
10dsdfdsfsfdsfxx-xx-xx-00001ar01/01/201905/01/20193C 
11hjhjhgjvcvbcvbxx-xx-xx-00002ar01/01/201910/01/20199C 
12hjhjhgjvcvbcvbxx-xx-xx-00002ar01/01/201905/01/20193C 
13hjhjhgjvcvbcvbxx-xx-xx-00002ar01/01/201910/01/20194C 
14aaavcvbcvbxx-xx-xx-00003ar01/01/201910/01/2019100C 
Sheet1
Cell Formulas
RangeFormula
R3:R5R3=COUNTIFS($E$2:$P$2,"Date Sub",E3:P3,">0")+N(R2)
E9:H14E9=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:I14I9=T(XLOOKUP(ROWS(I$9:I9),$R$3:$R$5,$Q$3:$Q$5,"",1))
A9:D14A9=XLOOKUP(ROWS(A$9:A9),$R$3:$R$5,$A$3:$D$5,"",1)
Dynamic array formulas.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQR
1DescDesc2Refrenace No.DescRev0Rev1Rev2Remarks
2Date SubDate RecDaysCodeDate SubDate RecDaysCodeDate SubDate RecDaysCodeIndex
3dsdfdsfsfdsfxx-xx-xx-00001ar01/01/202005/01/20194C01/01/201905/01/20193C6
4hjhjhgjvcvbcvbxx-xx-xx-00002ar01/01/201910/01/20199C01/01/201905/01/20193C01/01/201910/01/20194C4
5aaavcvbcvbxx-xx-xx-00003ar01/01/201910/01/2019100C1
6
7Dynamically
8DescDesc2Refrenace No.DescDate SubDate RecDaysCode
9dsdfdsfsfdsfxx-xx-xx-00001ar01/01/202005/01/20194C 
10dsdfdsfsfdsfxx-xx-xx-00001ar01/01/201905/01/20193C 
11hjhjhgjvcvbcvbxx-xx-xx-00002ar01/01/201910/01/20199C 
12hjhjhgjvcvbcvbxx-xx-xx-00002ar01/01/201905/01/20193C 
13hjhjhgjvcvbcvbxx-xx-xx-00002ar01/01/201910/01/20194C 
14aaavcvbcvbxx-xx-xx-00003ar01/01/201910/01/2019100C 
15        
Sheet1 (2)
Cell Formulas
RangeFormula
R3:R5R3=COUNTIFS($E$2:$P$2,"Date Sub",E3:P3,">0")+R4
A9:D15A9=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:H15E9=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:I14I9=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))))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,575
Members
416,925
Latest member
malamutus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top