Handling dates in VBA

G

Guest

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
 
I

Incidental

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
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top