Bỏ qua nội dung chính

Làm cách nào để chèn dấu hai chấm giữa các số để biến chúng thành định dạng thời gian tự động trong Excel?

Khi bạn chèn định dạng thời gian trong các ô của trang tính, bạn có bao giờ khó chịu với việc gõ dấu hai chấm mỗi lần không? Trong bài viết này, tôi sẽ nói về cách chèn dấu hai chấm khi bạn nhập số để biến chúng thành định dạng thời gian tự động trong Excel.

Chèn dấu hai chấm giữa các số để tạo định dạng thời gian với các công thức

Tự động tạo các số theo định dạng thời gian khi bạn nhập chúng bằng mã VBA


mũi tên màu xanh bong bóng bên phải Chèn dấu hai chấm giữa các số để tạo định dạng thời gian với các công thức

Giả sử, bạn có một danh sách các số, để chèn dấu hai chấm và đặt chúng dưới dạng định dạng thời gian, các công thức sau đây có thể giúp bạn.

Nếu các số luôn có 3 hoặc 4 chữ số, bạn có thể sử dụng công thức này:

=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0), sau đó kéo chốt điền xuống các ô bạn muốn chứa công thức này và bạn sẽ nhận được kết quả sau khi cần:

doc chèn dấu hai chấm vào số 1

Nếu các số có 5 hoặc 6 chữ số, vui lòng áp dụng công thức này:

=(INT(A1/10000)&":"&INT(MOD(A1,10000)/100)&":"&MOD(A1,100))+0và kéo chốt điền xuống các ô mà bạn muốn chứa công thức này và bạn sẽ nhận được dãy số như hình minh họa sau:

doc chèn dấu hai chấm vào số 2

Sau đó, bạn nên định dạng các số dưới dạng định dạng thời gian bằng cách nhấp vào Thời gian từ Tổng Quát danh sách thả xuống dưới Trang Chủ tab, xem ảnh chụp màn hình:

doc chèn dấu hai chấm vào số 3


mũi tên màu xanh bong bóng bên phải Tự động tạo các số theo định dạng thời gian khi bạn nhập chúng bằng mã VBA

Mã VBA sau cũng có thể giúp bạn tự động chuyển đổi các số sang định dạng thời gian khi bạn nhập chúng, vui lòng làm như sau:

1. Chuyển đến trang tính mà bạn muốn tự động chèn dấu hai chấm vào số.

2. Nhấp chuột phải vào tab trang tính và chọn Mã Chế độ xem từ menu ngữ cảnh, trong cửa sổ bật ra Microsoft Visual Basic cho các ứng dụng cửa sổ, vui lòng sao chép và dán đoạn mã sau vào chỗ trống Mô-đun cửa sổ, xem ảnh chụp màn hình:

Mã VBA: Tự động tạo các số theo định dạng thời gian:

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

doc chèn dấu hai chấm vào số 4

Lưu ý: Trong đoạn mã trên, A1: A20 là các ô mà bạn muốn chèn thời gian sau đó và bạn có thể thay đổi tham chiếu ô theo nhu cầu của mình.

3. Và bây giờ, khi bạn nhập các số chẳng hạn như 102319 trong phạm vi ô A1: A20, rồi nhấn đăng ký hạng mục thi chìa khóa, số sẽ được định dạng thành định dạng thời gian 10:23:19 AM tự động khi bạn cần.

doc chèn dấu hai chấm vào số 5

Công cụ năng suất văn phòng tốt nhất

🤖 Trợ lý AI của Kutools: Cách mạng hóa việc phân tích dữ liệu dựa trên: Thực thi thông minh   |  Tạo mã  |  Tạo công thức tùy chỉnh  |  Phân tích dữ liệu và tạo biểu đồ  |  Gọi các hàm Kutools...
Các tính năng phổ biến: Tìm, đánh dấu hoặc xác định các bản sao   |  Xóa hàng trống   |  Kết hợp các cột hoặc ô mà không làm mất dữ liệu   |   Vòng không có công thức hữu ích. Cảm ơn !
Siêu tra cứu: Nhiều tiêu chí VLookup    VLookup Nhiều Giá Trị  |   VLookup trên nhiều trang tính   |   Tra cứu mờ ....
Danh sách thả xuống nâng cao: Tạo nhanh danh sách thả xuống   |  Danh sách thả xuống phụ thuộc   |  Danh sách thả xuống nhiều lựa chọn ....
Trình quản lý cột: Thêm một số cột cụ thể  |  Di chuyển cột  |  Chuyển đổi trạng thái hiển thị của các cột ẩn  |  So sánh dãy và cột hữu ích. Cảm ơn !
Các tính năng nổi bật: Tiêu điểm lưới   |  Chế độ xem thiết kế   |   Thanh công thức lớn    Trình quản lý sổ làm việc & trang tính   |  Thư viện tài nguyên (Văn bản tự động)   |  Bảng chọn ngày   |  Kết hợp các bảng tính   |  Mã hóa/Giải mã ô    Gửi email theo danh sách   |  Siêu lọc   |   Bộ lọc đặc biệt (lọc in đậm/nghiêng/gạch ngang...) ...
15 bộ công cụ hàng đầu12 bản văn CÔNG CỤ (thêm văn bản, Xóa ký tự,...)   |   50 + Biểu đồ Các loại (Biểu đồ Gantt,...)   |   40+ Thực tế Công thức (Tính tuổi dựa trên ngày sinh,...)   |   19 chèn CÔNG CỤ (Chèn mã QR, Chèn ảnh từ đường dẫn,...)   |   12 Chuyển đổi CÔNG CỤ (Số thành từ, Chuyển đổi tiền tệ,...)   |   7 Hợp nhất & Tách CÔNG CỤ (Các hàng kết hợp nâng cao, Chia ô,...)   |   ... và nhiều hơn nữa

Nâng cao kỹ năng Excel của bạn với Kutools for Excel và trải nghiệm hiệu quả hơn bao giờ hết. Kutools for Excel cung cấp hơn 300 tính năng nâng cao để tăng năng suất và tiết kiệm thời gian.  Bấm vào đây để có được tính năng bạn cần nhất...

Mô tả


Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
Comments (7)
Rated 4.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
I know this is a very old post, but I am experiencing an error when I try to enter time between 00001 and 005959. It's either converting it to 1AM or throwing an error. Any ideas?
This comment was minimized by the moderator on the site
DZIEKUJE ZA WYJAŚNIENIE, CZY KTOŚ MOŻE PODPOWIEDZIEC CZY DA SIE TO PRZEROBIC TAK ŻEBY DZIŁAŁO NA 2 LUB WIECEJ KOLUMNACH? CHODZI O WPROWADZENIE STARTU PRACY W KOLUMNIE C A ZAKONCZENIA W KOLUMNIE D, WYNIK WYSWIETLI SIE W KOLUMNIE E
Rated 4.5 out of 5
This comment was minimized by the moderator on the site
does anyone know how to make this macro run on google sheets please?
This comment was minimized by the moderator on the site
In an earlier version of windows I was able to change the colon time delimiter to another character (usually an asterisk) to make it easier and faster to enter lots of time data. Is there still a way to do this or has it been "new & improved" into oblivion?
This comment was minimized by the moderator on the site
如果只要時跟分以及24小時制,請問可以怎麼改?
This comment was minimized by the moderator on the site
Hi, kyra,
你可以嘗試使用下面的vba來實現: (只顯示時和分)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
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 = Format(TimeValue(xStr), "Short Time")
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub
This comment was minimized by the moderator on the site
Thank you. Very well illustrated.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations