Come inserire i due punti tra i numeri per renderli automaticamente in formato ora in Excel?

Quando inserisci il formato dell'ora nelle celle del foglio di lavoro, sei mai stato infastidito dal digitare ogni volta i due punti? In questo articolo, parlerò di come inserire i due punti quando si immettono i numeri per renderli automaticamente in formato ora in Excel.

Inserisci i due punti tra i numeri per renderli come formato ora con formule

Crea automaticamente i numeri in formato ora quando li inserisci con il codice VBA

Inserisci i due punti tra i numeri per renderli come formato ora con formule

Supponendo che tu abbia una lista di numeri, per inserire i due punti e renderli come formato ora, le seguenti formule possono farti un favore.

Se i numeri sono sempre 3 o 4 cifre puoi usare questa formula:

=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0), quindi trascina il quadratino di riempimento verso il basso sulle celle in cui desideri contenere questa formula e otterrai il seguente risultato di cui hai bisogno:

Se i numeri sono 5 o 6 cifre, applica questa formula:

=(INT(A1/10000)&":"&INT(MOD(A1,10000)/100)&":"&MOD(A1,100))+0e trascina il quadratino di riempimento verso il basso sulle celle in cui desideri contenere questa formula e otterrai la sequenza numerica come mostrato nella seguente schermata:

Quindi dovresti formattare i numeri come formato ora facendo clic su Ora dal Generale elenco a discesa sotto il file Casa scheda, vedi screenshot:

Crea automaticamente i numeri in formato ora quando li inserisci con il codice VBA

Il seguente codice VBA può anche aiutarti a convertire automaticamente i numeri in formato ora quando li inserisci, per favore fai come segue:

1. Vai al foglio di lavoro in cui desideri inserire automaticamente i due punti nei numeri.

2. Fare clic con il pulsante destro del mouse sulla scheda del foglio e selezionare Visualizza codice dal menu contestuale, nel saltato fuori Microsoft Visual Basic, Applications Edition finestra, copia e incolla il seguente codice nello spazio vuoto Moduli finestra, vedi screenshot:

Codice VBA: crea automaticamente i numeri in formato ora:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updateby Extendoffice 20160606
    Dim xStr As String
    Dim xVal As String
    On Error GoTo EndMacro
    If Application.Intersect(Target, Range("A1:A20")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    With Target
        If Not .HasFormula Then
            xVal = .Value
            Select Case Len(xVal)
                Case 1 ' e.g., 1 = 00:01 AM
                    xStr = "00:0" & xVal
                Case 2 ' e.g., 12 = 00:12 AM
                    xStr = "00:" & xVal
                Case 3 ' e.g., 735 = 7:35 AM
                    xStr = Left(xVal, 1) & ":" & Right(xVal, 2)
                Case 4 ' e.g., 1234 = 12:34
                    xStr = Left(xVal, 2) & ":" & Right(xVal, 2)
                Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
                    xStr = Left(xVal, 1) & ":" & Mid(xVal, 2, 2) & ":" & Right(xVal, 2)
                Case 6 ' e.g., 123456 = 12:34:56
                    xStr = Left(xVal, 2) & ":" & Mid(xVal, 3, 2) & ":" & Right(xVal, 2)
                Case Else
                    Err.Raise 0
            End Select
            .Value = TimeValue(xStr)
        End If
    End With
    Application.EnableEvents = True
    Exit Sub
EndMacro:
    MsgBox "You did not enter a valid time"
    Application.EnableEvents = True
End Sub

Nota: Nel codice sopra, A1: A20 sono le celle che vuoi inserire più tardi e puoi modificare il riferimento di cella secondo le tue necessità.

3. E ora, quando inserisci numeri come 102319 nell'intervallo di celle A1: A20, quindi premi entrare chiave, il numero verrà formattato automaticamente come formato orario 10:23:19 in base alle tue esigenze.