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
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:
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:
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:
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
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.
Công cụ năng suất văn phòng tốt nhất
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...
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!