none
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 (dd.mm.yyyy). 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)  }")
            Next
        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
    
                    Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
    
                Next
                If proceed Then
                    Try
                        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
                            Next
                        Next
                        Marshal.FinalReleaseComObject(xlValuesRange)
                        xlValuesRange = Nothing
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                    End Try
    
                Else
                    MessageBox.Show(pSheetName & " not found.")
                End If
    
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseComObject(xlCells)
                ReleaseComObject(xlWorkSheets)
                ReleaseComObject(xlWorkSheet)
                ReleaseComObject(xlWorkBook)
                ReleaseComObject(xlWorkBooks)
                ReleaseComObject(xlApp)
            Else
                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)
        Try
            If obj IsNot Nothing Then
                Marshal.ReleaseComObject(obj)
                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
    Moderator
  • 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,

    Alex


    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 MSDNFSF@microsoft.com.

    Friday, June 29, 2018 5:08 AM