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

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


"Christian" wrote:

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)))"

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

--
Message posted from http://www.ExcelForum.com

On Sat, 3 Jul 2004 16:45:02 +0300, "Christian"
wrote:

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

On Sat, 3 Jul 2004 09:27:07 -0500, MSP77079
wrote:

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

Hi KeepItCool,

Thanks for the solution this works great!
- Chr

"keepITcool" wrote in message
...
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


"Christian" wrote:

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)))"

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.


"MSP77079 " wrote in message
...
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 have
separated them here for clarity.

Good luck.


---
Message posted from http://www.ExcelForum.com/

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

"Ron Rosenfeld" wrote in message
...
On Sat, 3 Jul 2004 09:27:07 -0500, MSP77079
wrote:

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

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

"Ron Rosenfeld" wrote in message
...
On Sat, 3 Jul 2004 16:45:02 +0300, "Christian"

wrote:

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