Getting date from string

[wpryan]

Hi All,
I have a data import that includes the date as part of a string in one cell (example: 20221229 Rotator Test Protocol FLM2314, where 2022 is ""yyyy", 12 is "mm" and 29 is "dd"). I want to extract the date from the string and format it as expected for my Swiss company (yyyy.mm.dd). It must be done in VBA, as this string is part of a data importation, and should follow the rest of the script. I tried various approaches, including:

Range("A" & lrow) = Format(Left(shNew.Range("H1"), 8), "yyyy.mm.dd") 'sets date

This is being interpreted as 1906.05.02, but the expected date is 2022.12.18.

The next approach was to try a formula, where I tried to extract the date using the Concatenate, Left and Mid functions (which, by the way, is working in the Formula bar - with the direct cell references of course):

Range("A" & lrow).Formula = "=CONCATENATE(LEFT(shNew.Range("H1");4);".";MID(shNew.Range("H1");5;2);".";MID(shNew.Range("H1");7;2))"

...but in this case VBA doesn't like the "." in the equation - I get a "expected - end of statement" error.
can anyone think of another way?

 

[Fluff]

No #Value! I'm not getting that error.

That's because (unlike most of the world) you use mm/dd/yyyy format.

 

[wpryan]

Yes but what is the name of it?

The name is 20221229 Rotator Test Protocol FLM2314, the same as the filename that was imported previously in the code...

 

[Fluff]

That cannot be the sheet name as it is too long.

 

[Dave Patton]

Not sure of the information but the following calculates the date from cell A1 or the file's name.
It is not sensitive to Regional Settings.
Format to your preference,

20221229 Rotator Test Protocol FLM2314.xlsm
	A	B	C
1	D:\Excel16\[20221229 Rotator Test Protocol FLM2314.xlsm]1a	29-Dec-2022	refers to Cell A1
2
3		29-Dec-2022	refers to file's name
4
1a

Cell Formulas
Range	Formula
A1	A1	=CELL("filename",A1)
B1	B1	=DATE(MID(A1,FIND("[",A1)+1,4),MID(A1,FIND("[",A1)+5,2),MID(A1,FIND("[",A1)+7,2))
B3	B3	=LET(s,LET(n,CELL("filename",A1),MID(n,FIND("[",n)+1,8)),DATE(LEFT(s,4),MID(s,5,2),MID(s,7,2)))

 

[wpryan]

OK here's the solution... The mistake I made was when using the .formula approach, one must wrap the period (.) around double quotes:

.Range("A" & lRow).Formula = "=LEFT(Temp!B1,4)&"".""&MID(Temp!B1,5,2)&"".""&MID(Temp!B1,7,2)" 'sets date