Reputation: 49
Evening
I Have a userform with some text boxes that are dates. In the spreadsheet the date format is the UK date format (dd/mm/yyyy), When the textbox calls the information from the spreadsheet it converts it to International date format (mm/dd/yyyy).
Everything I'm trying is failing, any help would be greatly received.
Many thanks in advance.
Upvotes: 0
Views: 1783
Reputation: 1061
Likely the text box is formatting the date with American mm/dd/yyyy style because that's the format defined in your computer's regional settings. One simple way to change this would be to change your regional setting for Short Date format.
However, you may want to share this UserForm with others. And there's no guarantee that their regional settings would be configured in the same way.
So if your form should always use UK format, regardless of the user's settings, then you could write a formatting function like this:
Public Function FormatDate(TheValue As Date) As String
Dim dateSeparator As String
dateSeparator = "/"
FormatDate = Format(Day(TheValue), "00") & dateSeparator & _
Format(Month(TheValue), "00") & dateSeparator & _
Year(TheValue)
End Function
And use it something like this:
MyUserForm.MyTextBox.Value = FormatDate(inputDateValue)
If you need to parse this format back into a date, you can create a function like this:
Public Function UKTextToDate(DateText As String) As Date
Dim yearPart, monthPart, dayPart As String
' Remember that in VBA, String indexes are 1-based, not 0-based!
' UK format: dd/mm/yyyy - year starts at position 7 and runs for 4 characters
yearPart = Mid(DateText, 7, 4)
' month starts at position 4 and runs for 2 characters
monthPart = Mid(DateText, 4, 2)
' day starts at position 1 and runs for 2 characters
dayPart = Left(DateText, 2)
' We should convert the text values back to integers, in order to use them
' in the DateSerial function.
UKTextToDate = DateSerial(CInt(yearPart), CInt(monthPart), CInt(dayPart))
End Function
However, beware that the above does not check for invalid input. If users can just type in anything, you'll need to write some code to guard against that.
Hope this helps!
Upvotes: 1