Как автоматически вставлять двоеточие между цифрами, чтобы преобразовать их в формат времени в 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, и перетащите маркер заполнения вниз до ячеек, куда вы хотите скопировать эту формулу, и вы получите последовательность чисел, как показано на следующем скриншоте:
Затем вам следует отформатировать числа в формат времени, выбрав Время из выпадающего списка Общий на вкладке Главная, см. скриншот:
Раскройте магию Excel с Kutools AI
- Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
- Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
- Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
- Интерпретация формул: Легко разбирайтесь в сложных формулах.
- Перевод текста: Преодолейте языковые барьеры в ваших таблицах.
Автоматическое преобразование чисел в формат времени при их вводе с помощью кода VBA
Следующий код VBA также может помочь вам автоматически преобразовать числа в формат времени при их вводе, выполните следующие действия:
1. Перейдите на рабочий лист, где вы хотите автоматически вставлять двоеточия в числа.
2. Щелкните правой кнопкой мыши по ярлыку листа и выберите Просмотр кода в контекстном меню. В открывшемся окне Microsoft Visual Basic for Applications скопируйте и вставьте следующий код в пустое окно Модуль, см. скриншот:
Код 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 AM, как вам нужно.
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек