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