In Excel, I need to custom format a cell to show time as hh:mm, but it returns with 0:00

I would like to be able to enter "1830" into a cell and have Excel automatically convert it to the format 18:30.

I've tried to format cells using custom format and time and I could not get it to work. Please try it yo开发者_如何学运维urself before posting all I need to do is enter hh:mm.

Im guessing I will need to use VBA, which I am not very familiar with, but would like to learn. I understand I can right-click on "Sheet1", goto "View Source", and make sure "Worksheet" and not "General" is selected at the top, and code away. If someone can paste the code it would be greatly appreciated!

Thank you!

Create this Custom Format and apply to selected cells: 0\:00

If you wanted the current time in excel you can press Ctrl + Shift + ; in a cell and get the time put in the cell

To do this, you must first come up with some rules for for converting integer values into time values. Some questions:

If someone types 7, do you want to leave that as 7, or do you want to convert it to 00:07?

If someone types 1861, do you want to convert it to 19:01?

If someone types 1830.5, do you want to convert it to 18:30:30?

This will only affect A1, but you can change rAffected to be whatever range you want.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rAffected As Range

    'You may want to limit which cells change
    Set rAffected = Me.Range("A1")

    If Not Intersect(Target, rAffected) Is Nothing Then
        Application.EnableEvents = False
            Target.Value = TimeSerial(Left$(Target.Value2, Len(Target.Value2) - 2), Right$(Target.Value2, 2), 0)
        Application.EnableEvents = True
    End If

End Sub

This is very simple. You just type 18:30 into a cell and Excel does the rest for you.

I had a similar need. User inputs 24hr time using the numeric keypad, and colon entry was a pain in the butt...

This is kind of overkill but it works for my situation. All you need to do is format the cells you want using one of the 24 hour time formats:

  Public Sub Handle24HourTimeEntry(ByVal Target As Range)
   'Converts up to 4 digits to 24 hour time
   Dim sTime24 As String

   Const sTIME_FORMATS As String = "|h:mm;@|h:mm|hh:mm|"
   Const sERROR_VALUE As String = "XXXX"
   'Valid 24hr time values: 1, 59, 135, 2035, 2359
   'Invalid values: 2400, 90, 9999, 12345

     If Target.Cells.Count = 1 Then ' ensure that only single cell edits get processed
        If IsNumeric(Target.Cells.Value2) Then ' skips empty cells, labels and error conditions
           If Not Target.Cells.HasFormula Then ' skip formulas
              If InStr(1, sTIME_FORMATS, "|" & Target.Cells.NumberFormat & "|") > 0 Then
                 If Target.Value2 >= 1 Then ' it's not a standard time value
                    sTime24 = Format$(Target.Value2, "0000")
                    If Len(sTime24) = 4 Then
                       sTime24 = Left$(sTime24, 2) & ":" & Mid$(sTime24, 3, 2)
                       If IsDate(sTime24) Then
                          Target.Value2 = CDate(sTime24)
                       Else
                          Target.Value = sERROR_VALUE
                       End If
                    Else
                       Target.Value = sERROR_VALUE
                    End If
                 End If
              End If
           End If
        End If
     End If

  End Sub

I call it from the workbook sheet change event:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

   Handle24HourTimeEntry Target

End Sub