Excel Date format

[bulevardi]

I have an input form that adds data to a table with some calculations.
One of the things my VBA script does is adding a date to that table for each entry aswel.

Now, the date format I used in the column is "dd/mm/yyy"
So today on 1st of september, it should write:
01/09/2011

But it always keeps me showing up:
09/01/2011

The function I have in the VBA script to create the date is:

Function DateAndTime()
 DateAndTime = Now
 DateAndTime = Format(DateAndTime, "dd/mm/yyyy")
  With Selection
   .Copy
   .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  End With
 Application.CutCopyMode = False
End Function

Can someone help me with this?
I assume it's a commen problem?... I'm quite new to this.

Thanks!

 

[pgc01]

Hi

Try the following:

Select a cell and run:

Function DateAndTime()
Dim dtDateAndTime As Date
 
 dtDateAndTime = Now
 Selection.NumberFormat = "dd/mm/yyyy"
 Selection.Value = dtDateAndTime
End Function

 

[bulevardi]

Not really no, there's no selection made.

With Sheets("Another Sheet")
    nextrow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    .Cells(nextrow, 1) = DateAndTime()
End With

With the following code, he puts the date in cell on a wrong sheet:

Function DateAndTime() Dim dtDateAndTime As Date    dtDateAndTime = Now  Selection.NumberFormat = "dd/mm/yyyy"  Selection.Value = dtDateAndTime End Function</pre>

 

[bulevardi]

Why is it the 9th of september today...
Have to wait until tomorrow for further testings !