Textbox date format does not match region or cell format RRS feed

  • Question

  • I am creating a form in Excel to show and update a cell formatted as a date ( The region setting on my Windows 10 is also set to dd.MM.yyyy (short) and dd MMMM yyyy (long). Yet when the value is loaded into a Textbox it displays as mm/dd/yyyy.

    How to set/control the date format used in the textbox? Why is it not using the region setting on the computer?

    Thursday, June 28, 2018 7:43 PM

All replies

  •  I would assume that the dates are Date types and not just Strings that you are dealing with.  If so,  my guess would be that you need to format them when you assign the date to a TextBox as a String.  For example...

            Dim SomeDateFromExcellCell As Date = Now
            TextBox1.Text = SomeDateFromExcellCell.ToString("dd/MM/yyyy")

    If you say it can`t be done then i`ll try it

    • Edited by IronRazerz Thursday, June 28, 2018 7:58 PM
    Thursday, June 28, 2018 7:56 PM
  • Hello,

    In regards to "I am creating a form in Excel" leads me to think this is not a VB.NET project, if this is the case I can move the post to the proper forum.

    If this is VB.NET, dates are tricky sometimes while other times not. Either way, formatting is a string representation of the date and does not have upon reading them.

    Here is a simple example using VB.NET and Excel automation (there are many ways ranging from automation, OpenXml, OleDb and third party libraries such as GemBox). Note this code sample is just that, a code sample, stripped down from what would be done in the wild (production).

    Read a file, read a sheet, read specific cells. Format them via culture.

        Public Sub DoingDates()
            Dim fileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "DoingDates.xlsx")
            Dim dates As List(Of Tuple(Of String, Date)) = OpenExcel_Example(fileName, "Sheet1", "A1", "C2")
            Dim culture As CultureInfo = CultureInfo.CreateSpecificCulture("en-US")
            Dim dtfi As DateTimeFormatInfo = culture.DateTimeFormat
            dtfi.ShortDatePattern = "dd.MM.yyyy"
            For Each d In dates
                Console.WriteLine($"string: {d.Item1} date: {d.Item2.ToString("d", dtfi)  }")
        End Sub
        Public Function OpenExcel_Example(pFileName As String,
                                            pSheetName As String,
                                            pStartOfRange As String,
                                            pEndOfRange As String) As List(Of Tuple(Of String, Date))
            Dim results As New List(Of Tuple(Of String, Date))
            If IO.File.Exists(pFileName) Then
                Dim proceed As Boolean = False
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim xlCells As Excel.Range = Nothing
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(pFileName)
                xlApp.Visible = False
                xlWorkSheets = xlWorkBook.Sheets
                ' For/Next finds our sheet
                For x As Integer = 1 To xlWorkSheets.Count
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
                    If xlWorkSheet.Name = pSheetName Then
                        proceed = True
                        Exit For
                    End If
                    xlWorkSheet = Nothing
                If proceed Then
                        Dim currentDate As Date
                        Dim currentValue As String
                        Dim xlValuesRange As Excel.Range = xlWorkSheet.Range(pStartOfRange, pEndOfRange)
                        Dim Values(,) As Object = CType(xlValuesRange.Value, Object(,))
                        For i As Int32 = 1 To Values.GetUpperBound(0)
                            For j As Integer = 1 To Values.GetUpperBound(1)
                                currentValue = CStr(Values(i, j))
                                If DateTime.TryParse(currentValue, currentDate) Then
                                    results.Add(New Tuple(Of String, Date)(currentValue, currentDate))
                                End If
                        xlValuesRange = Nothing
                    Catch ex As Exception
                    End Try
                    MessageBox.Show(pSheetName & " not found.")
                End If
                xlApp.UserControl = True
                MessageBox.Show("'" & pFileName & "' not located. Try one of the write examples first.")
            End If
            Return results
        End Function

    Dispose method

    Private Sub ReleaseComObject(obj As Object)
            If obj IsNot Nothing Then
                obj = Nothing
            End If
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

    Results from above

    string: 1/12/2016 date: 12.01.2016
    string: 2/12/2016 date: 12.02.2016
    string: 2-12-2016 date: 12.02.2016
    string: 2/3/2015 date: 03.02.2015
    string: 3/12/2016 date: 12.03.2016
    string: 3-12-2016 date: 12.03.2016

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, June 28, 2018 10:52 PM
  • Hi

    Please check your format in Excel, and then check if you have set the format to mm/dd/yyyy in the code

    Best Regards,


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Friday, June 29, 2018 5:08 AM