Problem in Date format

Hi,

I got a problem with the date format for that I need work around.

I have a date in dd-mm-yyyy hh:mm:ss format. I simply want to convert into mm/dd/yyyy hh:mm format.

I used format function. but it is not coverting the date properly. For example,

VB Code:

Debug.print Format("02-05-2006 20:53:56", "mm/dd/yyyy HH:mm")
 
02/05/2006 20:53 'This should be 05/02/2006 20:53 
 
Debug.print Format("18-05-2006 20:53:56", "mm/dd/yyyy HH:mm")
 
05/18/2006 20:53 'This is correct. no issues.

Somebody help me on this please.

Thanks,

CS.

You'll have to manually convert them. When you try to cast a string to a date, it will use your regional settings to determine the date and month. The reason your second example works is because VB is smart enough to figure out that there is no month "18", and assumes it's the day. This should do the trick:

VB Code:

Private Sub TryIt()
 
    Dim sTest As String
 
    sTest = DateConvert("02-05-2006 20:53:56")
    Debug.Print sTest
    Debug.Print Format$(sTest, "General Date")
    Debug.Print Format$(sTest, "mm/dd/yyyy HH:mm")
 
End Sub
 
Private Function DateConvert(sInput As String) As Date
 
    Dim sDateTime() As String, sDate() As String
 
    sDateTime = Split(sInput, " ")
    sDate = Split(sDateTime(0), "-")
    DateConvert = DateValue(sDate(1) & "/" & sDate(0) & "/" & sDate(2))
    DateConvert = DateConvert + CDate(sDateTime(1))
 
End Function