Maintaining Date Format in Excel

Question

I have a form with three combo boxes, one for the (possible) 31 days of the month, the second for the 12 numerals representing months and the third with year values corresponding to the next five years.

I concatenate these together to form a date

TheDay = CBDay.Value
TheMonth = CBMonth.Value
TheYear = CBYear.Value

thedate = TheDay + "/" + TheMonth + "/" + TheYear

So, here in Australia today's date will show as 10/12/2015 and this works fine. However when I write the data to the worksheet the date persistently converts to 12/10/2015 even though the column into which the data is written is formatted as 'date' in 'dd/mm/yy' style

The variables are declared as integers

Is there a way to ensure the value of the date doesn't change?

Answer 1

Excel thinks it's clever with formatting strings that look like dates.

Whenever possible, use date serial numbers and impose your own formats

eg

Dim theDate As Date
theDate = DateSerial(TheYear, TheMonth, TheDay)

ActiveCell = theDate
ActiveCell.NumberFormat = "dd/mm/yyyy"
' or better yet, use an unambiguous date format
ActiveCell.NumberFormat = "dd mmm, yyyy"