Comment insérer automatiquement des deux-points entre les nombres pour les formater en heure dans Excel ?
Lorsque vous insérez un format d'heure dans les cellules d'une feuille de calcul, avez-vous déjà été agacé par la nécessité de taper les deux-points à chaque fois ? Dans cet article, je vais expliquer comment insérer automatiquement des deux-points lorsque vous saisissez des nombres pour les convertir en format d'heure dans Excel.
Insérer des deux-points entre les nombres pour les formater en heure avec des formules
Formater automatiquement les nombres en format d'heure lors de leur saisie avec un code VBA
Insérer des deux-points entre les nombres pour les formater en heure avec des formules
Supposons que vous ayez une liste de nombres et que vous souhaitiez insérer des deux-points pour les formater en heure. Les formules suivantes peuvent vous aider.
Si les nombres comportent toujours 3 ou 4 chiffres, vous pouvez utiliser cette formule :
=TIME(GAUCHE(A1;NBCAR(A1)-2);DROITE(A1;2);0), puis faites glisser la poignée de recopie vers le bas jusqu'aux cellules où vous souhaitez appliquer cette formule, et vous obtiendrez le résultat suivant selon vos besoins :
Si les nombres comportent 5 ou 6 chiffres, veuillez appliquer cette formule :
=(ENT(A1/10000)&":"&ENT(MOD(A1;10000)/100)&":"&MOD(A1;100))+0, et faites glisser la poignée de recopie vers le bas jusqu'aux cellules où vous souhaitez appliquer cette formule, et vous obtiendrez la séquence numérique comme illustré dans la capture d'écran ci-dessous :
Ensuite, vous devez formater les nombres en tant qu'heure en cliquant sur Heure dans la liste déroulante Général sous l'onglet Accueil, voir capture d'écran :

Découvrez la magie d'Excel avec Kutools AI
- Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
- Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
- Codage VBA : Écrivez et implémentez du code VBA sans effort.
- Interprétation des formules : Comprenez facilement des formules complexes.
- Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.
Formater automatiquement les nombres en format d'heure lors de leur saisie avec un code VBA
Le code VBA suivant peut également vous aider à convertir automatiquement les nombres en format d'heure lors de leur saisie. Veuillez procéder comme suit :
1. Allez dans la feuille de calcul où vous souhaitez insérer automatiquement des deux-points dans les nombres.
2. Cliquez avec le bouton droit sur l'onglet de la feuille, puis sélectionnez Affichage du code dans le menu contextuel. Dans la fenêtre Microsoft Visual Basic for Applications qui s'affiche, copiez et collez le code suivant dans la fenêtre Module vide, voir capture d'écran :
Code VBA : Formater automatiquement les nombres en format d'heure :
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
Remarque : Dans le code ci-dessus, A1:A20 correspond aux cellules où vous souhaitez insérer l'heure ultérieurement, et vous pouvez modifier la référence de cellule selon vos besoins.
3. Désormais, lorsque vous saisissez des nombres tels que 102319 dans la plage de cellules A1:A20, puis appuyez sur la touche Entrée, le nombre sera automatiquement formaté en heure au format 10:23:19 AM comme vous le souhaitez.
Meilleurs outils de productivité pour Office
Boostez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité sans précédent. Kutools pour Excel propose plus de300 fonctionnalités avancées pour augmenter la productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office, et facilite grandement votre travail
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 %, et réduit des centaines de clics de souris pour vous chaque jour !