Sam có rất nhiều trang tính chứa thời gian. Vấn đề là thời gian ở định dạng “1300” thay vì định dạng “13:00.” Do đó, Excel coi chúng là các giá trị số thông thường thay vì nhận ra chúng là thời gian. Sam muốn chúng được chuyển đổi thành giá trị thời gian thực tế.

Có một số cách bạn có thể tiếp cận nhiệm vụ này. Một cách là sử dụng hàm TIME để chuyển đổi giá trị thành thời gian, như được hiển thị ở đây:

=TIME(LEFT(A1,2),RIGHT(A1,2),)

Công thức này giả định rằng thời gian trong ô A1 sẽ luôn chứa bốn chữ số. Nếu không (ví dụ: nó có thể là 427 thay vì 0427), thì công thức cần được sửa đổi một chút:

=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),)

Công thức về cơ bản kéo chữ số ngoài cùng bên trái (hoặc các chữ số) và sử dụng chúng cho đối số giờ của hàm TIME, sau đó sử dụng hai chữ số ngoài cùng bên phải cho đối số phút. TIME trả về một giá trị thời gian thực, được định dạng như vậy trong ô.

Một cách tiếp cận công thức tương tự có thể được thực hiện bằng cách sử dụng hàm TIMEVALUE:

=TIMEVALUE(REPLACE(A1,LEN(A1)-1,0,":"))

Công thức này sử dụng REPLACE để chèn dấu hai chấm vào vị trí thích hợp, sau đó TIMEVALUE chuyển đổi kết quả thành giá trị thời gian. Bạn sẽ cần phải định dạng ô kết quả để nó hiển thị thời gian như bạn muốn.

Một biến thể khác của cách tiếp cận công thức là sử dụng hàm TEXT, theo cách này:

=--TEXT(A1,"00\:00")

Điều này trả về một giá trị thời gian thực tế, sau đó bạn sẽ cần phải định dạng đúng để được hiển thị dưới dạng thời gian.

Một cách tiếp cận khác là chỉ cần thực hiện phép toán trên thời gian gốc để chuyển nó thành giá trị thời gian được Excel sử dụng. Điều này rất dễ dàng khi bạn nhận ra rằng giá trị thời gian chỉ là một phần nhỏ của một ngày. Do đó, giá trị thời gian là một số từ 0 đến 1, được tính bằng cách chia giờ cho 24 (giờ trong ngày) và phút cho 1440 (phút trong ngày). Đây là một công thức thực hiện điều đó:

=INT(A1/100)/24+MOD(A1,100)/1440

Điều này xác định phần giờ của giá trị ban đầu, sau đó được chia cho 24. Phần phút (phần còn lại từ giá trị ban đầu) sau đó được chia cho 1440 và được cộng vào phần đầu tiên. Sau đó, bạn có thể định dạng kết quả theo thời gian và nó hoạt động hoàn hảo.

Tất cả các công thức được mô tả cho đến nay đều sử dụng một cột mới để thực hiện chuyển đổi. Điều này rất tiện lợi, nhưng bạn có thể muốn thực sự chuyển đổi giá trị tại chỗ mà không cần công thức. Đây là nơi mà một macro có thể hữu ích. Macro sau sẽ chuyển đổi bất kỳ ô nào bạn đã chọn thành các giá trị thời gian và định dạng các ô một cách thích hợp:

Sub NumberToTime()

Dim rCell As Range     Dim iHours As Integer     Dim iMins As Integer

For Each rCell In Selection         If IsNumeric(rCell.Value) And Len(rCell.Value) > 0 Then             iHours = rCell.Value \ 100             iMins = rCell.Value Mod 100             rCell.Value = (iHours + iMins / 60) / 24             rCell.NumberFormat = "h:mm AM/PM"

End If     Next End Sub

Macro sử dụng phép chia số nguyên để xác định số giờ (iHours) và đưa phần còn lại vào iMins. Sau đó, giá trị này được điều chỉnh thành giá trị thời gian và được đặt trở lại ô, sau đó được định dạng thành thời gian. Bạn có thể thay đổi định dạng ô, nếu muốn, thành bất kỳ định dạng thời gian nào khác được Excel hỗ trợ.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (10101) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.

Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: