Как вставить двоеточие между числами, чтобы автоматически использовать их в качестве формата времени в Excel?
Когда вы вставляете формат времени в ячейки рабочего листа, вас когда-нибудь раздражало вводить двоеточия каждый раз? В этой статье я расскажу о том, как вставлять двоеточие при вводе чисел, чтобы автоматически использовать их в качестве формата времени в Excel.
Вставьте двоеточие между числами, чтобы сделать их форматом времени с формулами
Автоматически преобразовывать числа в формат времени при вводе их с кодом VBA
Вставьте двоеточие между числами, чтобы сделать их форматом времени с формулами
Предположим, у вас есть список чисел, чтобы вставить двоеточия и сделать их форматом времени, следующие формулы могут оказать вам услугу.
Если числа всегда 3 или 4 цифры, вы можете использовать эту формулу:
=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0), затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите содержать эту формулу, и вы получите следующий результат, который вам нужен:
Если числа состоят из 5 или 6 цифр, примените эту формулу:
=(INT(A1/10000)&":"&INT(MOD(A1,10000)/100)&":"&MOD(A1,100))+0, и перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите содержать эту формулу, и вы получите числовую последовательность, как показано на следующем снимке экрана:
Затем вы должны отформатировать числа как формат времени, нажав Продолжительность из Общие раскрывающийся список под Главная вкладку, см. снимок экрана:
Автоматически преобразовывать числа в формат времени при вводе их с кодом VBA
Следующий код VBA также может помочь вам автоматически преобразовать числа в формат времени при их вводе, пожалуйста, сделайте следующее:
1. Перейдите к листу, на котором вы хотите автоматически вставлять двоеточия в числа.
2. Щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код из контекстного меню во всплывающем Microsoft Visual Basic для приложений окна, скопируйте и вставьте следующий код в пустое Модули окно, см. снимок экрана:
Код VBA: автоматическое преобразование чисел в формат времени:
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
Примечание: В приведенном выше коде A1: A20 - это ячейки, которые вы хотите вставить позже, и вы можете изменить ссылку на ячейку по своему усмотрению.
3. И теперь, когда вы вводите числа, такие как 102319, в диапазоне ячеек A1: A20, а затем нажимаете Enter , число будет автоматически отформатировано в формате времени 10:23:19 по мере необходимости.
Лучшие инструменты для офисной работы
Усовершенствуйте свои навыки работы с Excel с помощью Kutools for Excelи испытайте эффективность, как никогда раньше. Kutools for Excel Предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Office Tab Добавляет в Office интерфейс с вкладками и значительно упрощает вашу работу
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
