# Getting date from string



## wpryan (Monday at 11:48 AM)

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 (Monday at 12:00 PM)

No need for VBA:
Book1AB1ProductDate220221229 Rotator Test Protocol FLM231444924320221229 Rotator Test Protocol FLM231412/29/2022Sheet3Cell FormulasRangeFormulaB2:B3B2=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 (Monday at 2:17 PM)

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 (Monday at 2:23 PM)

wpryan said:


> 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.
Book1A1Date244924312/29/2022Sheet5Cell FormulasRangeFormulaA2:A3A2=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 (Monday at 2:42 PM)

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


----------



## wpryan (Monday at 3:05 PM)

jdellasala said:


> 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.
> Book1A1Date244924312/29/2022Sheet5Cell FormulasRangeFormulaA2:A3A2=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 (Monday at 3:08 PM)

What is shNew in your code?


----------



## wpryan (Monday at 3:11 PM)

Fluff said:


> 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 (Monday at 3:11 PM)

Fluff said:


> 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.xlsxA1Product220221229 Rotator Test Protocol FLM2314320221229 Rotator Test Protocol FLM2314420210111 Rotator Test Protocol FLM2314520211222 Rotator Test Protocol FLM2314620211002 Rotator Test Protocol FLM2314720211021 Rotator Test Protocol FLM2314820210510 Rotator Test Protocol FLM2314920210413 Rotator Test Protocol FLM2314Sheet 4Cell FormulasRangeFormulaA4:A9A4="2021"&TEXT(RANDBETWEEN(1,12),"0#")&TEXT(RANDBETWEEN(1,28),"0#")&" Rotator Test Protocol FLM2314"
Still no #Value error:
shitstorm.xlsxA1Date244299304/13/2021Sheet5Cell FormulasRangeFormulaA2A2=DATEVALUE(MID('Sheet 4'!A9,5,2)&"/"&MID('Sheet 4'!A9,7,2)&"/"&LEFT('Sheet 4'!A9,4))A3A3=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 (Monday at 3:20 PM)

wpryan said:


> shNew is a worksheet.


Yes but what is the name of it?


----------



## wpryan (Monday at 11:48 AM)

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 (Monday at 3:21 PM)

jdellasala said:


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


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


----------



## wpryan (Monday at 3:24 PM)

Fluff said:


> 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 (Monday at 3:45 PM)

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


----------



## Dave Patton (Monday at 4:15 PM)

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.xlsmABC1D:\Excel16\[20221229 Rotator Test Protocol FLM2314.xlsm]1a29-Dec-2022refers to Cell A12329-Dec-2022refers to file's name41aCell FormulasRangeFormulaA1A1=CELL("filename",A1)B1B1=DATE(MID(A1,FIND("[",A1)+1,4),MID(A1,FIND("[",A1)+5,2),MID(A1,FIND("[",A1)+7,2))B3B3=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 (Tuesday at 10:20 AM)

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
```


----------

