Handling dates in VBA
 

Hi

I have a userform where two dates are entered into some textboxes. In the
code these are processed a bit and should be returned to a sheet as
parameters in a query. The problem is, that they are returned as mm/dd/yyyy
and not dd/mm/yyyy as I need them. The code, that returns the values is:
....

Dim LowerDate, UpperDate As Date

intSpm = valSpm
....

With Sheets("DataAnswers")
With Range("UpperDate")
.Value = UpperDate
.NumberFormat = "d/m/yyyy"
End With
With Range("LowerDate")
.Value = LowerDate
.NumberFormat = "d/m/yyyy"
End With
.Range("Question").Value = intSpm
End With

The date 10-01-2007 (10th Jan, 2007) is returned as 01-10-2007 (1st Oct,
2007) whereas 26-06-2007 (26th Jun, 2007) is returned correctly.

What to do in Excel 2003, UK?!?

Thanks,

/Sune

Handling dates in VBA
 

Hi Sune

Your problem is due to excel using american dates, you can fix this by
using the CDate function which is demonstrated below, if you need any
more info try searching for post on CDate by Tom Ogilvy he must have
around a million posts on this by now...

example code

Option Explicit

Dim UpperDate, LowerDate As Date

Private Sub CommandButton1_Click()

LowerDate = [a1].Value
UpperDate = [a2].Value

LowerDate = CDate(LowerDate)
UpperDate = CDate(UpperDate)

MsgBox LowerDate & vbNewLine & UpperDate

End Sub

hope this helps

Handling dates in VBA
 

Well, that was easy! I figured it woud be the American dates acting up, but
couldn't find the function to handle it.

Thank you very much for your help!

/Sune


"Incidental" wrote:

Hi Sune

Your problem is due to excel using american dates, you can fix this by
using the CDate function which is demonstrated below, if you need any
more info try searching for post on CDate by Tom Ogilvy he must have
around a million posts on this by now...

example code

Option Explicit

Dim UpperDate, LowerDate As Date

Private Sub CommandButton1_Click()

LowerDate = [a1].Value
UpperDate = [a2].Value

LowerDate = CDate(LowerDate)
UpperDate = CDate(UpperDate)

MsgBox LowerDate & vbNewLine & UpperDate

End Sub

hope this helps