Strip date from string and transfer datetext to number

[Christian]

Hi NG,

I have a cell with date values in this string format
04Oct2004000000

I need to strip the date to another cell and made a macro that
uses:

ActiveCell.Formula =
"=VALUE(CONCATENATE(LEFT(RC[-4],2),""-"",MID(RC[-4],3,3),""-"",MID(RC[-4],6,
4)))"

to insert this formula
=VALUE(CONCATENATE(LEFT(M2;2);"-";MID(M2;3;3);"-";MID(M2;6;4)))
The macro then proceeds by copying the formula downwards for all rows.

The problem I have is that certain dates are not found. These strings will
generate the right date:
28Jun2004000000
05Jul2004000000
02Aug2004000000
06Sep2004000000

this one fails
04Oct2004000000
the error message in the cell in Excel is #VALUE!

Now I suspect that this is because I have danish language keyboard setup and
Excel is using danish month lists because of that.

Alternative to above approach would be to make a loop that inserts the date
for each row by reading the string value in column M and then via VBA return
the date to the cell in column Q, ie read 04Oct2004 and return 38264 as the
date value for 4-Oct-2004.

Something like

For each cell in InputRange
Read string value from cell
Transform string to date value using
Case
1: Jan
2: Feb
etc
Store value to variable

For each cell in OutputRange
Enter value of variable to cell
Next cell (in outputrange)

Next cell (in inputrange)

should do the trick. Any suggestion how I should do this is warmly welcome
as this a bit beyond my current skills.
Other solutions or suggestions are also welcome.

- Chr

 

[keepITcool]

Tried to do it in worksheetfunction only..

=DATEVALUE(LEFT(SUBSTITUTE(A10;MID(A10;3;3);
TEXT(DATE(0;
MATCH(MID(A10;3;3);
{"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"};
0);1);"mmm"));9)
)

works in Dutch.. and should work in Danish too

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[MSP77079]

Yes, you are making this more difficult than necessary.

Try using the VBA commands:
myDate = Datevalue(Left("04Oct20040000", 9))
myDate = Format(myDate, "d-mmm-yyyy")

You can combine the two into one statement if you like. I hav
separated them here for clarity.

Good luck

 

[Ron Rosenfeld]

Other solutions or suggestions are also welcome.

Since the date is in English, and your regional settings are Danish, the month
needs to be translated into something unambiguous. This worksheet function
should work:

=DATE(MID(A1;6;4);MATCH(MID(A1;3;3);
{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0);
LEFT(A1;2))


--ron

 

[Ron Rosenfeld]

myDate = Datevalue(Left("04Oct20040000", 9))

Did you test that? I can't seem to get it to work on my machine.

On my computer, using VBA 6.3, Datevalue does not seem to work in the absence
of some kind of separator between the components. I get Type Mismatch error
with your function.

But even with the spaces, I still get a Type Mismatch error if regional
settings are set to Danish:

?Datevalue(Left("04 Oct 20040000", 11)) gives Type Mismatch error.


--ron

 

[Christian]

Hi KeepItCool,

Thanks for the solution this works great!
- Chr

Tried to do it in worksheetfunction only..

=DATEVALUE(LEFT(SUBSTITUTE(A10;MID(A10;3;3);
TEXT(DATE(0;
MATCH(MID(A10;3;3);
{"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"};
0);1);"mmm"));9)
)

works in Dutch.. and should work in Danish too

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

Hi NG,

I have a cell with date values in this string format
04Oct2004000000

I need to strip the date to another cell and made a macro that
uses:

ActiveCell.Formula =
"=VALUE(CONCATENATE(LEFT(RC[-4],2),""-"",MID(RC[-4],3,3),""-"",MID(RC[-
4],6, 4)))"

 

[Christian]

Hi,

Thanks for the proposal. This still gives me problems with dates in October.
In danish the short name is Okt and in english it's Oct.

The other solution provided by KeepItCool works fine.

 

[Christian]

Hi,
Yes I tested this and could not get it to work either.
I get the same result as with my formula:

"=VALUE(CONCATENATE(LEFT(RC[-4],2),""-"",MID(RC[-4],3,3),""-"",MID(RC[-4],6,
4)))"

- Chr

 

[Christian]

Thanks Ron,

I ended up using the solution from KeepItCool

=DATEVALUE(LEFT(SUBSTITUTE(A10;MID(A10;3;3);
TEXT(DATE(0;
MATCH(MID(A10;3;3);
{"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"};
0);1);"mmm"));9)
)

Which works fine. Appears to be a similar solution as the one you suggest.

Thanks for the help!
- Chr