Output Error

[iggydarsa]

Hi everyone,

I have a code looks like this:

Sub DateOutput()

    Dim startDate As String
    Dim stopDate As String

    startDate = InputBox("Start Date: (dd/mm/yy)")
    startDate = Format(startDate, "dd/mm/yy")

    stopDate = InputBox("Stop Date: (dd/mm/yy)")
    stopDate = Format(stopDate, "dd/mm/yy")

    MsgBox Format(startDate, "dd-mmm-yy")
    MsgBox Format(stopDate, "dd-mmm-yy")

End Sub

For startDate I entered 2/1/05 and msgbox outputted "2-Jan-05" which is fine but after that for stopDate I entered 19/2/05 and msgbox outputted "2-May-19" which is wrong, it should be "19-Feb-05".

What am I doing wrong?
Any suggestions?

Thanks

 

[GlennUK]

Did you paste your code into here? With that code I can't reproduce your problem. It's almost as though you've actually got

MsgBox Format(stopDate, "yy-mmm-dd")

in your code.

 

[iggydarsa]

I copy/pasted, the code is exactly how it looks in macro...

 

[Andrew Poulsom]

Your code worked for me, but VBA can be flaky with dates in UK format. Try:

Sub DateOutput()

    Dim startDate As String
    Dim stopDate As String

    startDate = InputBox("Start Date: (dd/mm/yy)")
    startDate = Format(DateValue(startDate), "dd/mm/yy")

    stopDate = InputBox("Stop Date: (dd/mm/yy)")
    stopDate = Format(DateValue(stopDate), "dd/mm/yy")

    MsgBox Format(startDate, "dd-mmm-yy")
    MsgBox Format(stopDate, "dd-mmm-yy")

End Sub

The DateValue function uses your Control Panel|Regional Options Date setting. What do you have there?

 

[iggydarsa]

DateValue() thing didnt work, it gave me the same error.

Right now I am in USA (they use mm/dd/yy) but I am writing this code for someone who is in Turkey (they use dd/mm/yy)

Also funny thing is, how come startDate works perfectly but stopDate doesn't?

Any suggestions?

 

[Andrew Poulsom]

While I agree that it's strange that startDate works perfectly but stopDate doesn't, you can't mix and match US and European date formats. Try:

Sub DateOutput()
    Dim startDate As String
    Dim stopDate As String
    Dim D As Integer
    Dim M As Integer
    Dim Y As Integer
    startDate = InputBox("Start Date: (dd/mm/yy)")
    startDate = WorksheetFunction.Substitute(startDate, "/", "@", 2)
    D = Left(startDate, InStr(1, startDate, "/") - 1)
    M = Mid(startDate, InStr(1, startDate, "/") + 1, InStr(1, startDate, "@") - InStr(1, startDate, "/") - 1)
    Y = WorksheetFunction.Substitute(startDate, D & "/" & M & "@", "")
    startDate = Format(DateSerial(Y, M, D), "dd-mmm-yy")
    stopDate = InputBox("Stop Date: (dd/mm/yy)")
    stopDate = WorksheetFunction.Substitute(stopDate, "/", "@", 2)
    D = Left(stopDate, InStr(1, stopDate, "/") - 1)
    M = Mid(stopDate, InStr(1, stopDate, "/") + 1, InStr(1, stopDate, "@") - InStr(1, stopDate, "/") - 1)
    Y = WorksheetFunction.Substitute(stopDate, D & "/" & M & "@", "")
    stopDate = Format(DateSerial(Y, M, D), "dd-mmm-yy")
    MsgBox startDate
    MsgBox stopDate
End Sub

 

[iggydarsa]

Thats great Andrew,

It worked

Thank you very very very very very very much ray: