Quick date format problem...

[asgersax]

Hi All.
I have a problem. Actually I have found the solution to it but I don't understand why my original code wasn't working...
Here it is. It changes my inputdate "dd/mm/yyyy" to "mm/dd/yyyy"... When I use CDate then it works...
The code is to be used by different users. How do I avoid that the different language settings on each computer maintaine the date format "dd/mm/yyyy"?

Sub input_date()
Dim UserEntry_end As String
Dim dd As Date
Dim Msg_end As String
Msg_end = "Enter end date as dd/mm/yyyy"
  UserEntry_end = ""
        Do Until IsDate(UserEntry_end) And (Len(Trim(UserEntry_end)) = 10) And (Len(Trim(UserEntry_end)) >= 8)
        UserEntry_end = InputBox(Msg_end)
        If IsDate(UserEntry_end) And (Len(Trim(UserEntry_end)) <= 10) And (Len(Trim(UserEntry_end)) >= 8) Then
            ActiveSheet.Range("C4").Value = Format(UserEntry_end, "dd/mm/yyyy")
        '    ActiveSheet.Range("C4").Value = CDate(UserEntry_end)
        Exit Do
        ElseIf UserEntry_end = "" Then
        Exit Do
        Else
            Msg_start = "Please try again.  Enter date as dd/mm/yyyy"
        End If
        Loop
End Sub

Best regards

[OllieB]

You have to realise that a displayed date does not mean it is actually understood by Excel to be a date, as it could also be a text string.

When it is a text string you will notice that the displayed value does not change when you change the formatting of the cell (for example from dd/mm/yyyy to mm/dd/yyyy). In order for Excel to treat the value as a date, the Cdate() instruction actually converts the date into a date serial number, which can be displayed in any date format desired.

You can check this by changing the cell format to text for a date. Excel will in that case display a number.

Hope this explains it a bit.

[asgersax]

So if I use Cdate as above how do I then ensure that the format is "dd/mm/yyyy"?

[Norie]

CDate has nothing to do with formatting

It will convert a valid string to a date value.

The formatting is kind of up to you.

[OllieB]

Cdate generates a proper date value so the cell format becomes leading. In other words set your cell format to display the date in the desired format