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?

 

[jdellasala]

No need for VBA:

Book1
	A	B
1	Product	Date
2	20221229 Rotator Test Protocol FLM2314	44924
3	20221229 Rotator Test Protocol FLM2314	12/29/2022
Sheet3

Cell Formulas
Range	Formula
B2:B3	B2	=DATEVALUE(MID(A2,5,2)&"/"&MID(A2,7,2)&"/"&LEFT(A2,4))

Row 3 is a copy of row 2 with cell B3 formatted as a date. Wanted to include that so you could see what you might get if the cell isn't formatted as a date.

 

[wpryan]

Hello, thanks for your response. In fact, the destination cell will be the next unpopulated row in the worksheet, so I do need VBA to find the last row. Also, somehow when I copy / paste your formula into the worksheet it comes up with a #Value error. ...not sure why that is.... I'm using Excel 365...

 

[jdellasala]

Hello, thanks for your response. In fact, the destination cell will be the next unpopulated row in the worksheet, so I do need VBA to find the last row. Also, somehow when I copy / paste your formula into the worksheet it comes up with a #Value error. ...not sure why that is.... I'm using Excel 365...
View attachment 82366

Of course you can reference another worksheet in a formula. Using 365 is irrelevant. I literally cut this from the table I posted and pasted it to a different worksheet.

Book1
	A
1	Date
2	44924
3	12/29/2022
Sheet5

Cell Formulas
Range	Formula
A2:A3	A2	=DATEVALUE(MID(Sheet4!A2,5,2)&"/"&MID(Sheet4!A2,7,2)&"/"&LEFT(Sheet4!A2,4))

Turning to VBA as the first solution is a big mistake!

 

[Fluff]

That will still give the OP a #value! error.

 

[wpryan]

Of course you can reference another worksheet in a formula. Using 365 is irrelevant. I literally cut this from the table I posted and pasted it to a different worksheet.

Book1
	A
1	Date
2	44924
3	12/29/2022
Sheet5

Cell Formulas
Range	Formula
A2:A3	A2	=DATEVALUE(MID(Sheet4!A2,5,2)&"/"&MID(Sheet4!A2,7,2)&"/"&LEFT(Sheet4!A2,4))

Turning to VBA as the first solution is a big mistake!

Yes I understand that turning to VBA for the first solution is not always a good idea, however, in my case the destination cell will always be different, hence the "Range("A" & lRow) line of code. And yes, the person will still get a #Value error. That's what I'm trying to find a solution to... I'm curious if it has something to do with the fact that my computer is set to Swiss German formatting...

 

[Fluff]

What is shNew in your code?

 

[wpryan]

What is shNew in your code?

shNew is a worksheet. I have a lot of other data going to that worksheet, and all the rest of the data is being correctly transferred. Just the formatting of this particular cell is giving me grief.

 

[jdellasala]

That will still give the OP a #value! error.

I hate to challenge you (it usually doesn't end well for me!), but that's directly from Excel. No #Value! I'm not getting that error. There was no mention of it being from another workbook (been there, ate that foot!). That said, I am having a hard time finding the bottom of the column. I'd be surprised if you couldn't work this one out!
Data:

shitstorm.xlsx
	A
1	Product
2	20221229 Rotator Test Protocol FLM2314
3	20221229 Rotator Test Protocol FLM2314
4	20210111 Rotator Test Protocol FLM2314
5	20211222 Rotator Test Protocol FLM2314
6	20211002 Rotator Test Protocol FLM2314
7	20211021 Rotator Test Protocol FLM2314
8	20210510 Rotator Test Protocol FLM2314
9	20210413 Rotator Test Protocol FLM2314
Sheet 4

Cell Formulas
Range	Formula
A4:A9	A4	="2021"&TEXT(RANDBETWEEN(1,12),"0#")&TEXT(RANDBETWEEN(1,28),"0#")&" Rotator Test Protocol FLM2314"

Still no #Value error:

shitstorm.xlsx
	A
1	Date
2	44299
3	04/13/2021
Sheet5

Cell Formulas
Range	Formula
A2	A2	=DATEVALUE(MID('Sheet 4'!A9,5,2)&"/"&MID('Sheet 4'!A9,7,2)&"/"&LEFT('Sheet 4'!A9,4))
A3	A3	=DATEVALUE(MID('Sheet 4'!A9,5,2)&"/"&MID('Sheet 4'!A9,7,2)&"/"&LEFT('Sheet 4'!A9,4))

I'm SURE there's an easy way to determine the last row of Column A! It just escapes me at the moment, and I have a lot of other distractions.

 

[Fluff]

shNew is a worksheet.

Yes but what is the name of it?