dd/mm converted to mm/dd

[systemx]

Hi all,

I have a problem with Excel switching dates from dd/mm to mm/dd. I a
using Excel 2003.

- Dates are stored on the worksheet 'REF' in dd/mm/yy format
- Those same dates are the rowsource for a combobox in a userform
- When the user clicks 'Submit' on the userform, the date is written t
the next blank cell on the worksheet 'DATA'. This cell is also formatte
as dd/mm/yy.

For some reason, Excel swaps some date formats around when written
This only occurs where it is possible to logically do so.

i.e. 01/08/06 (dd/mm/yy) becomes 08/01/06 (mm/dd/yy)
17/01/06 (dd/mm/yy) remains 17/01/06 as 01/17/06 is not a valid date.

With the example above, the serial value is 38930. When it comes out
reversed, the serial value actually changes 38725. So in actual fact
not just a change of formatting but a complete change in the date!

I have pasted my relevant code below -

Private Sub cboDate_Change()

With Me.cboDate
.Value = Format(.Value, \"dd/mm/yyyy\")
End With

End Sub

Private Sub submitbutton_Click()
Dim nextR As String
Dim newR As String

Sheets(\"Data\").Select
Range(\"A1\").Select

Lastrow = Cells(Rows.Count, \"A\").End(xlUp).Row
nextR = Lastrow + 1

newR = \"A\" & nextR
Range(newR).Select

ActiveCell.Value = cboDate.Value

ActiveCell.Offset(0, 3).Select
ActiveCell.Value = cboConsultant.Value

ActiveCell.Offset(0, 3).Select
ActiveCell.Value = cboStart.Value

ActiveCell.Offset(0, 1).Select
ActiveCell.Value = cboFinish.Value

ActiveCell.Offset(0, 2).Select
ActiveCell.Value = cboTask1.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = txtTask1.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = cboTask2.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = txtTask2.Value

End Sub

Could anyone help me out with this one? It has me baffled! System dat
formats check out, and Excel standard date is Australian.

Thanks

Ro

 

[systemx]

Note: I have fixed the issue by leaving the dates in text format all the
way through.

Still interested in the root cause though if anyone can advise...

Cheers

 

[apndas]

It would seem it does not matter if you leave in text format as it doe
the same thing, I thought I might be able to overcome the problem b
have 3 seperate text boxes for dd mm yy and concatenate the cells bu
it seems to have a mind of it's own and just resorts to mm dd yy.
aaaggghhh???

I to would dearly like a solution to this problem.

Regards
Darre

 

[Harald Staff]

What is this ? :

With Me.cboDate
Value = Format(.Value, \"dd/mm/yyyy\")
End With

Remember that a combo value is just text, it is not ever a date. The above
will then make no sense. VB may be helping you with some liberal date
conversions, but wrong it is. You can not assign a numberformat to text, and
I guess this is where your troubles begin.

You should always use Datevalue to ensure that dates are read in the local
fashion:

ActiveCell.Value = Datevalue("08/01/06")

will insert january 8th in dd-mm countries, and august 1st in mm-dd
countries.

HTH. Best wishes Harald

 

[apndas]

I have a just found a solution also, upon reading into it a bit mor
that mm/dd/yy is simply the default vba setting irregardless of you
computer regional settings. i have just allowed it to put the value i
the designated cell and then just reformat the cell to the date forma
you require.

.Cells(nextRow, 2).Value = txtDay.Value & "/" & txtMonth.Value & "/"
txtYear.Value
.Cells(nextRow, 2).Value = Format(.Cells(nextRow, 2), "dd/mm/yy")

it works so I'm not going to complain
an alternative solution "The best approach is to specify a date b
using VBA's DateSerial function and let Excel take care of th
formatting details (it will use the user's short date format)" - Joh
Walkenbach, Excel 2003 Power Programming with VBA

Regards
Darre