12:00PM to number format, back to h:mm format gives 12:05AM?

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Found this weird interaction, where a cell value is 12:00PM, and when a userform pulls the value into a combobox it is read as 0.5, and when the format is changed to h:mm it gives 12:05AM in the combo box.

I have found that 6:00AM also translates to 12:25AM, I have checked every time increment of 15 minutes and only those two times seem to return anomolies.

I have used if statements to convert the values back to their desired time, but I am extremely curious as to what is going on inside of excel for these values to act like this.

Any insight is appreciated.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows
Try something like this:
VBA Code:
ComboBox1.AddItem Format(Cells(1, 1).Value, "HH MM SS")
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,510
Office Version
  1. 365
Platform
  1. Windows
It is also important to understand how Excel stores dates and times. Dates/times are stored as the number of days since 1/0/1900.
You can easily see this by entering any date in any cell, and then change the format of the cell to "General". Then you will see the date as Excel does, as a large integer.

Time is stored of the the fraction of one day.
12:00 PM is exactly 12 hours into a 24 hour day, so it is literally 12/24, or 0.5
6:00 AM is exactly 6 hours into a 24 hour day, so it is literally 6/24 or 0.25

So dates/times in Excel are just numbers with special (date/time) formats.
 

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
119
Office Version
  1. 365
Platform
  1. Windows
It is also important to understand how Excel stores dates and times. Dates/times are stored as the number of days since 1/0/1900.
You can easily see this by entering any date in any cell, and then change the format of the cell to "General". Then you will see the date as Excel does, as a large integer.

Time is stored of the the fraction of one day.
12:00 PM is exactly 12 hours into a 24 hour day, so it is literally 12/24, or 0.5
6:00 AM is exactly 6 hours into a 24 hour day, so it is literally 6/24 or 0.25

So dates/times in Excel are just numbers with special (date/time) formats.

Yes, exactly. I am interested why converting 0.5 to h:mm gives 12:05AM when you would expect 12:00PM for instance.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,510
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

We would have to see your code so we can see how you are pulling it.
 

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
119
Office Version
  1. 365
Platform
  1. Windows
We would have to see your code so we can see how you are pulling it.
VBA Code:
Me.ShipTime.Value = Me.ListBox1.Column(7, i)

where the value of Me.ListBox1.Column(7, i) is 12:00PM and the returned value is 0.5

afterwards there is the following code to change formatting:
VBA Code:
Me.EntryDate = Format(Me.EntryDate, "[$-en-CA]d-mmm-yy;@")
    Me.ShipTime = Format(Me.ShipTime, "h:mm AM/PM")
    Me.ShipDate = Format(Me.ShipDate, "[$-en-CA]d-mmm-yy;@")
   
    If Me.ShipTime = "12:05 AM" Then
        Me.ShipTime = "12:00 PM"
    End If
   
    If Me.ShipTime = "12:25 AM" Then
        Me.ShipTime = "6:00 AM"
    End If

some pics:
Capture.PNG

05.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,510
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Please post ALL your code, including the List Box code.
 

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Please post ALL your code, including the List Box code.
The initialize

VBA Code:
Private Sub UserForm_Initialize()
DatePickerX_Ini

    Dim Ord As Worksheet
    Dim SourceTable As Worksheet
    Dim Fields As Worksheet
    Dim ctl As Control
    Dim i As Integer

    Set Ord = Sheet8
    Set SourceTable = Sheet2
    Set Fields = Sheet5
    
    'Application.ScreenUpdating = False
    
    For Each ctl In OrderLookup.Controls
        Select Case TypeName(ctl)
            Case "TextBox"
                ctl.Enabled = False
             Case "ComboBox"
                ctl.Enabled = False
        End Select
    Next ctl
        
        OrderLookup.cboHeader.Enabled = True
        OrderLookup.ComboBox1.Enabled = True
    
    Me.cboHeader.Value = "All Columns"
    Me.CalendarIcon2.Visible = False
    
    Me.CustomerBox.Clear
    Me.CustomerBox.List = SourceTable.Range("CustomerTable").Columns(1).Value
    
    Me.UserField.Clear
    For i = 2 To Fields.Range("A" & Application.Rows.Count).End(xlUp).Row
        Me.UserField.AddItem Fields.Range("A" & i).Value
    Next i
    
    Me.ShipTime.Clear
    For i = 2 To Fields.Range("C" & Application.Rows.Count).End(xlUp).Row
        Me.ShipTime.AddItem Fields.Range("C" & i).Text
    Next i
    
    Me.ShipMethod.Clear
    For i = 2 To Fields.Range("D" & Application.Rows.Count).End(xlUp).Row
        Me.ShipMethod.AddItem Fields.Range("D" & i).Value
    Next i
    
    Ord.Range("S2").Value = "ID#"
    Ord.Range("S3").Value = ">0"
    
    Ord.Range("A3:L1048576").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    Ord.Range("$S$2:$S$3"), CopyToRange:=Ord.Range("$U$3:$AF$3"), Unique:=False
    
    Ord.Select
    With Ord
        .Range("U4:AF1048576").Sort Key1:=Range("V4"), Order1:=xlDescending, Header:=xlGuess
    End With
        
    With Me.ListBox1
        .ColumnCount = 12
        .ColumnHeads = True
        .ColumnWidths = "40,55,80,80,55,60,110,60,70,90,90,60,"
    End With
    
    Me.ListBox1.RowSource = Sheet8.Range("outdata").Address(external:=True)
    
End Sub
The code to populate the fields in the userform

VBA Code:
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    'dim the variables
    Dim i As Integer
    On Error Resume Next
    
    Me.ShipDate.Enabled = True
    Me.CustomerBox.Enabled = True
    Me.CusPO.Enabled = True
    Me.UserField.Enabled = True
    Me.ShipTime.Enabled = True
    Me.ShipMethod.Enabled = True
    Me.OrderNumber.Enabled = True
    Me.AGNumber.Enabled = True
    Me.CalendarIcon2.Visible = True
    
    'find the selected list item
    i = Me.ListBox1.ListIndex
    'add the values to the text boxes
    Me.IDnumber.Value = Me.ListBox1.Column(0, i)
    Me.ShipDate.Value = Me.ListBox1.Column(1, i)
    Me.CustomerBox.Value = Me.ListBox1.Column(2, i)
    Me.CusPO.Value = Me.ListBox1.Column(3, i)
    Me.EntryDate.Value = Me.ListBox1.Column(4, i)
    Me.UserField.Value = Me.ListBox1.Column(5, i)
    Me.ShipTo.Value = Me.ListBox1.Column(6, i)
    Me.ShipTime.Value = Me.ListBox1.Column(7, i)
    Me.ShipMethod.Value = Me.ListBox1.Column(8, i)
    Me.OrderNumber.Value = Me.ListBox1.Column(9, i)
    Me.AGNumber.Value = Me.ListBox1.Column(10, i)
    Me.CasesTotal.Value = Me.ListBox1.Column(11, i)
    
    Me.EntryDate = Format(Me.EntryDate, "[$-en-CA]d-mmm-yy;@")
    Me.ShipTime = Format(Me.ShipTime, "h:mm AM/PM")
    Me.ShipDate = Format(Me.ShipDate, "[$-en-CA]d-mmm-yy;@")
    
    If Me.ShipTime = "12:05 AM" Then
        Me.ShipTime = "12:00 PM"
    End If
    
    If Me.ShipTime = "12:25 AM" Then
        Me.ShipTime = "6:00 AM"
    End If
    
    On Error GoTo 0
    
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,155
Office Version
  1. 365
Platform
  1. Windows
Try it like
VBA Code:
Me.ShipTime.Value = Format(Me.ListBox1.Column(7, i), "h:mm AM/PM")
and remove
VBA Code:
Me.ShipTime = Format(Me.ShipTime, "h:mm AM/PM")
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,132,701
Messages
5,654,818
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top