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

Question

Like the title says, some cells end up with the correct value, and some get the month and day switched, if this was a constant, i could fix it, but it just messes some values, and i can't understand why.

Here is the code:

For v2temp = 0 To 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 Then
    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

For v2temp = 0 To 4
    ActiveWorkbook.Worksheets("TEMP2").Range("C7").Offset(v2temp, 0).Value = form1(v2temp)
Next

So, this should write some dates into cells C7 C8 C9 C10 and C11, and it does, but with the problem already described.

I've added some debug.print in the code to write step by step the value received and the value transformed, you will see that both are correct, however, the value that ends up on the cell is wrong.

This is the debug print:

---------MARK--------
var TMPP is 10-08-2013
var DT is   10-08-2013
---------MARK--------
var TMPP is 03-08-2013
var DT is   03-08-2013
---------MARK--------
var TMPP is 28-07-2013
var DT is   28-07-2013
---------MARK--------
var TMPP is 14-07-2013
var DT is   14-07-2013
---------MARK--------
var TMPP is 26-06-2013
var DT is   26-06-2013
---------MARK--------

you can see it passes correctly 5 times, and all 5 the in and out dates are ok, but this is what ends up on the cells, values are supposedly sorted from soonest to oldest:

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

Can anyone please help me? i've tried searching and searching and can't find any solution.

THANKS

> EDIT1

i've added:

For v2temp = 0 To 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 now are: 28-07-2013 26-06-2013 14-07-2013 08-10-2013 08-03-2013

they still seam to arrive as string, because the sort options don't even show the sorts for dates, but for strings.

i sort them later with this 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
With ActiveWorkbook.Worksheets("TEMP2").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

the sort order is what would be expected with a string sort (vs a date sort). How are you sorting them? Also, why are you assigning to a temp cell only to read it back in immediately? I see you set the correct number format for cell I2 but not C7:C11 - have you already set the format for them? — Cor_Blimey, Aug 12 '13 at 18:20
Note that months you must mark with upper case "MM" in your date format. lower case format "mm" is designed for minutes. — Eldar Agalarov, Aug 12 '13 at 18:34
made changes on mm to MM and the format of the other cells, has both suggestions, but still no effect. see my edit1. thanks — Cachirro, Aug 12 '13 at 19:55

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

Please take a look at the -mm- in the middle of your format string. You probably meant -MM-.

See Excel Macro : How can I get the timestamp in "yyyy-MM-dd hh:mm:ss" format? the use of both :mm: for minutes and -MM- for month.

edited May 23 '17 at 12:20

Community

1
1

answered Aug 12 '13 at 18:09

rajah9

11,645
5
44
57

score 0 · Accepted Answer · answered Aug 12 '13 at 18:20

0

You can use this function for converting string value to date:

' Convert String to Date
Public Function stringToDate(ByRef dateString As String, ByRef dateFormat As String) As Date
    stringToDate = CDate(Format(dateString, dateFormat))
End Function

where dateFormat can be "MM/dd/yyyy" or "dd-MM-yyyy" for example.

answered Aug 12 '13 at 18:20

Eldar Agalarov

4,849
4
30
38

i've tried your suggestion, i've added `form1(v2temp) = stringToDate(tmpp, "dd-MM-yyyy")` but without success, i end up with 28-07-2013, 26-06-2013, 14-07-2013, 08-10-2013, 08-03-2013; cell3 is not an date, cell4 & cell5 have month and day switched – Cachirro Aug 12 '13 at 20:02
1

SOLVED IT, thanks to your tip, i've changed the code that writes the value on the cells to `ActiveWorkbook.Worksheets("TEMP2").Range("C7").Offset(v2temp, 0).Value = stringToDate(form1(v2temp), "dd-MM-yyyy")` and now it works FINE!!! Thank you – Cachirro Aug 12 '13 at 20:20

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

2 Answers2