Another Date format oddity

[John]

I have a macro to input a date as follows :

ActiveCell.Value = InputBox("Enter date (DD/MM/YYYY)")

The active cell is formatted as custom dd/mm/yyyy

However if I enter 12/08/2004 it stores it as 08/12/2004 whereas if I
enter 13/08/2004 it enters it correctly in both the cell and the
formulae bar.

I don't know if its relevant but I have my regional settings set to UK
but the options for date settings in excel are all US, which is why I
had to use custom. I expected these to provide UK options or is this a
red herring.

System is XP Pro using O2K.

Thanks John

 

[Andy Wiggins]

Try:

ActiveCell.Value = CDate(InputBox("Enter date (DD/MM/YYYY)"))

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[John]

Try:

ActiveCell.Value = CDate(InputBox("Enter date (DD/MM/YYYY)"))

Works a treat

Thank you Andy

 

[Dave Peterson]

I think you're getting hit by VBA's USA centric date handling. VBA likes
mm/dd/yyyy order.

But one way to verify it:

Option Explicit
Sub testme()

Dim MyMonth As String
Dim myDay As String
Dim myYear As String
Dim myInput As String
Dim ValidDate As Boolean

Do
ValidDate = False
myInput = InputBox("Enter date (DD/MM/YYYY)", _
Default:=Format(Date, "dd/mm/yyyy"))
If myInput = "" Then
Exit Sub 'user hit cancel
End If
If Len(myInput) = 10 Then
myDay = Left(myInput, 2)
MyMonth = Mid(myInput, 4, 2)
myYear = Right(myInput, 4)
If IsNumeric(myDay & MyMonth & myYear) Then
If Format(DateSerial(myYear, MyMonth, myDay), "dd/mm/yyyy") _
= myInput Then
ValidDate = True
End If
End If
End If

If ValidDate Then
Exit Do
End If
Loop

With ActiveCell
.NumberFormat = "dd/mm/yyyy"
.Value = DateSerial(myYear, MyMonth, myDay)
End With

End Sub

You may want to use a freeware version of a calendar control.

From Ron de Bruin's site:
http://www.rondebruin.nl/calendar.htm

You can also Download the control on this page.
A Pop-up Calendar for Excel
http://www.fontstuff.com/vba/vbatut07.htm

 

[John]

Thanks Dave I will try this and the popup