excel - VBA strange behavior converting string to date, some are ok, some are not -

like title says, cells end correct value, , month , day switched, if constant, fix it, messes values, , can't understand why.

here code:

for v2temp = 0 4 form1(v2temp) = trim(form1(v2temp)) & ";" tmpp = left(form1(v2temp), len(form1(v2temp)) - 1)  arr = split(tmpp, "-") activeworkbook.worksheets("temp2").range("i2").numberformat = "dd-mm-yyyy"  if tmpp <> vbnullstring     dt = dateserial(arr(2), arr(1), arr(0))     activeworkbook.worksheets("temp2").range("i2").value = dt     form1(v2temp) = activeworkbook.worksheets("temp2").range("i2").value else     form1(v2temp) = "" end if next  v2temp = 0 4     activeworkbook.worksheets("temp2").range("c7").offset(v2temp, 0).value = form1(v2temp) next 

so, should write dates cells c7 c8 c9 c10 , c11, , does, problem described.

i've added debug.print in code write step step value received , value transformed, see both correct, however, value ends on cell wrong.

this debug print:

---------mark-------- var tmpp 10-08-2013 var dt   10-08-2013 ---------mark-------- var tmpp 03-08-2013 var dt   03-08-2013 ---------mark-------- var tmpp 28-07-2013 var dt   28-07-2013 ---------mark-------- var tmpp 14-07-2013 var dt   14-07-2013 ---------mark-------- var tmpp 26-06-2013 var dt   26-06-2013 ---------mark-------- 

you can see passes correctly 5 times, , 5 in , out dates ok, ends on cells, values supposedly sorted soonest oldest:

28-07-2013 26-06-2013 14-07-2013 > should before cell this, it's wrong formated 08-10-13   > should 10-08-2013 08-03-13   > should 03-08-2013 

can please me? i've tried searching , searching , can't find solution.

thanks

---

> edit1

i've added:

for v2temp = 0 99     activeworkbook.worksheets("temp2").range("c2").offset(v2temp, 0).numberformat = "dd-mm-yyyy" next activeworkbook.worksheets("temp2").range("i2").numberformat = "dd-mm-yyyy" 

the results are: 28-07-2013 26-06-2013 14-07-2013 08-10-2013 08-03-2013

they still seam arrive string, because sort options don't show sorts dates, strings.

i sort them later code:

activeworkbook.worksheets("temp2").visible = true sheets("temp2").select range("b1:d1").select sheets("temp2").autofiltermode = false selection.autofilter activeworkbook.worksheets("temp2").autofilter.sort.sortfields.clear activeworkbook.worksheets("temp2").autofilter.sort.sortfields.add key:=range("c1:c11"), sorton:=xlsortonvalues, order:=xldescending, dataoption:=xlsortnormal activeworkbook.worksheets("temp2").autofilter.sort     .header = xlyes     .matchcase = false     .orientation = xltoptobottom     .sortmethod = xlpinyin     .apply end 

you can use function converting string value date:

' convert string date public function stringtodate(byref datestring string, byref dateformat string) date     stringtodate = cdate(format(datestring, dateformat)) end function 

where dateformat can "mm/dd/yyyy" or "dd-mm-yyyy" example.