Excel - Textbox issue: Time displayed in decimal format

October 2016


  • I have an EXCEL sheet with a list containing various data including hours (in hh: mm format).
  • I have also created a form that allows me to add or edit lines, but it seems that hours are being transformed into the decimal format upon import , (example 0.5625 for 13:30).
  • While in contrast, the exporting the hours from the TextBox to the list is done in the hh:mm format.
  • Here is an example of my workbook:here


Try this:

Private Sub NNI_Change()
    Dim Lg As Long

    Lg = NNI.ListIndex + 2
    For Each ctl In Me.Controls
        If ctl.Tag > "A" Then
            ctl.Value = Feuil2.Range(ctl.Tag & Lg)
            If ctl.Tag > "K" Then
                ctl.Value = Format(Feuil2.Range(ctl.Tag & Lg), "hh:mm")
            End If
        End If

End Sub

Thanks to Mytå for this tip.

Related :

This document entitled « Excel - Textbox issue: Time displayed in decimal format » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.