A macro for time cells in Excel documents that converts inputs to time values, so that you can, for example, enter "9,5" and it would become "9:30"

.vb

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    
    ' Prevent infinite loops by disabling events temporarily
    On Error Resume Next
    Application.EnableEvents = False

    ' Iterate through each cell in the range that was changed
    For Each Cell In Target
        ' Strictly check if the cell's format is exactly "h:mm"
        If Cell.NumberFormat = "h:mm" Then
            ' Ensure the cell contains a numeric value and is not empty
            If IsNumeric(Cell.Value) And Cell.Value <> "" Then
                ' Check if the value is already a valid time (less than 1 means it's a fraction of a day)
                If Cell.Value >= 1 Then
                    ' Dividing by 24 essentially maps the entered value (0 to 24) to a time value (0 to 1)
                    Cell.Value = Cell.Value / 24
                End If
            End If
        End If
    Next Cell

    ' Re-enable events after processing
    Application.EnableEvents = True
End Sub

To use this, press Alt+F11 to open the Macros window, and add this function to the sheet where you want it to work. Do not add this as a new module, but add this to the sheet itself. This will only work for cells that have the h:mm format.

Since this macro changes the value of the cells, Excel cannot undo this conversion.