Date Formatting in VBA code not working properly

[ArleneJo]

Hi,

I've written my first code that will require user's input of a date. I want to format the date to be shown in a cell as "dd/mm/yyyy". Everything works but the output is showing as mm/dd/yyyy. Can anybody help me please?

 Sub inputSettlementDate()

Dim varInputDate As Variant
Dim lngERow As Long




varInputDate = InputBox("Please enter the Settlement Date", "Settlement Date", "dd/mm/yyyy")




If IsDate(varInputDate) Then
  varInputDate = Format(varInputDate, "dd/mm/yyyy")
   lngERow = Range("B" & Rows.Count).End(xlUp).Row + 1
   Range("B" & lngERow).Value = varInputDate


   Else


   MsgBox "Please enter a valid date format dd/mm/yyyy"
   varInputDate = InputBox("Please enter the Settlement Date using this format dd/mm/yyyy.", "Settlement Date")
End If




End Sub

 

[Michael M]

It seems the cell you are putting the date into......Range("B" & lngERow).Value is formatted as mm/dd/yyyy
It's not the code that's causing the difference, so add this line...in red

Sub inputSettlementDate()
Dim varInputDate As Variant
Dim lngERow As Long
varInputDate = InputBox("Please enter the Settlement Date", "Settlement Date", "dd/mm/yyyy")
If IsDate(varInputDate) Then
varInputDate = Format(varInputDate, "dd/mm/yyyy")
lngERow = Range("B" & Rows.Count).End(xlUp).Row + 1
Range("B" & lngERow).Value = varInputDate
Range("B" & lngERow).NumberFormat = "dd/mm/yyyy" 
Else
MsgBox "Please enter a valid date format dd/mm/yyyy"
varInputDate = InputBox("Please enter the Settlement Date using this format dd/mm/yyyy.", "Settlement Date")
End If
End Sub

 

[Norie]

Try this.

If IsDate(varInputDate) Then

lngERow = Range("B" & Rows.Count).End(xlUp).Row + 1
With Range("B" & lngERow)
    .Value = DateValue(varInputDate)
    .NumberFormat ="dd/mm/yyyy"
End With


Else

 

[dmt32]

Hi,
see if this update to your code helps:

Sub inputSettlementDate()


    Dim varInputDate As Variant
    Dim lngERow As Long
    
    Do
    varInputDate = InputBox("Please enter the Settlement Date", "Settlement Date", "dd/mm/yyyy")
    'cancel pressed
    If StrPtr(varInputDate) = 0 Then Exit Sub
    
    If Not IsDate(varInputDate) Then MsgBox "Please enter a valid date format dd/mm/yyyy", 16, "Invalid Date"
    
    Loop Until IsDate(varInputDate)
    
    lngERow = Range("B" & Rows.Count).End(xlUp).Row + 1
    
    With Range("B" & lngERow)
        .Value = DateValue(varInputDate)
        .NumberFormat = "dd/mm/yyyy"
    End With


End Sub

Dave

 

[ArleneJo]

thanks guys for looking into my query. I thought IsDate is enough to qualify both the response from the input box and the property of its cell destination.

Hi,
see if this update to your code helps:

Sub inputSettlementDate()


    Dim varInputDate As Variant
    Dim lngERow As Long
    
    Do
    varInputDate = InputBox("Please enter the Settlement Date", "Settlement Date", "dd/mm/yyyy")
    'cancel pressed
    If StrPtr(varInputDate) = 0 Then Exit Sub
    
    If Not IsDate(varInputDate) Then MsgBox "Please enter a valid date format dd/mm/yyyy", 16, "Invalid Date"
    
    Loop Until IsDate(varInputDate)
    
    lngERow = Range("B" & Rows.Count).End(xlUp).Row + 1
    
    With Range("B" & lngERow)
        .Value = DateValue(varInputDate)
        .NumberFormat = "dd/mm/yyyy"
    End With


End Sub

Dave

 

[dmt32]

thanks guys for looking into my query. I thought IsDate is enough to qualify both the response from the input box and the property of its cell destination.

Hi,

Your Variant variable varInputDate is checked using IsDate to ensure user has entered something that is recognisable as a valid date.
IsDate Function Returns a Boolean value - True if the value is recognizable as a valid date or time; otherwise, it returns False.

What comes out of the InputBox Function is a string containing the contents of users input.
To change a string date from your inputbox to a date data type you would use DateValue Function.

DateValue recognizes the order for month, day, and year according to the Short Date format you specified for your system & hopefully updated solution, has now resolved your problem?

Hope Helpful

Dave

 

[ArleneJo]

Hi Dave,

Thanks for the insight. Will keep this in mind.

Cheers,
Arlene