Có thể bạn quan tâm: Hàm Trích Xuất Dữ Liệu Trong Excel: Tổng Quan, Cách Sử Dụng Và Ứng Dụng Thực Tiễn

Mục lục bài viết

Mở Đầu

Excel là một trong những công cụ mạnh mẽ nhất trong việc xử lý dữ liệu, và thời gian là một trong những yếu tố quan trọng mà chúng ta thường xuyên phải làm việc. Việc lấy giờ, phút, giây từ một ô chứa dữ liệu thời gian không chỉ giúp chúng ta phân tích chi tiết hơn mà còn hỗ trợ trong việc tạo báo cáo, tính toán thời gian làm việc, tính lương, hay thậm chí là lập lịch tự động. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, đầy đủ và thực tiễn về cách sử dụng các hàm lấy giờ phút giây trong Excel, kèm theo các ví dụ thực tế, mẹo tối ưu và những lỗi thường gặp mà người dùng nên tránh.

Có thể bạn quan tâm: Cách Lập Bảng Tính Excel Hiệu Quả: Hướng Dẫn Từng Bước Cho Người Mới Bắt Đầu

1. Kiến Thức Cơ Bản Về Định Dạng Thời Gian Trong Excel

1.1. Cách Excel Lưu Trữ Thời Gian

Giá trị số thực: Excel lưu trữ ngày và giờ dưới dạng một số thực, trong đó phần nguyên đại diện cho ngày (số ngày tính từ 1/1/1900) và phần thập phân đại diện cho thời gian trong ngày. Ví dụ, 0.5 tương đương với 12:00:00 PM (nửa ngày).
Định dạng hiển thị: Mặc dù giá trị thực tế là một số, chúng ta có thể thay đổi cách hiển thị bằng cách áp dụng các định dạng ngày/giờ (Format Cells → Number → Date/Time).

1.2. Các Định Dạng Thời Gian Phổ Biến

Định dạng	Mô tả	Ví dụ
`hh:mm:ss`	Giờ:phút:giây (24h)	14:35:20
`hh:mm AM/PM`	Giờ:phút (12h)	02:35 PM
`mm:ss`	Phút:giây	05:30
`hh:mm:ss.000`	Giờ:phút:giây.millisecond	09:15:45.123

Khi làm việc với hàm lấy giờ, phút, giây, việc hiểu đúng cách Excel lưu trữ và hiển thị thời gian sẽ giúp tránh những sai sót không đáng có.

Có thể bạn quan tâm: Các Chức Năng Của Excel: Từ Cơ Bản Đến Nâng Cao Giúp Tối Ưu Công Việc

2. Các Hàm Cơ Bản Để Lấy Giờ, Phút, Giây

2.1. Hàm `HOUR`

Cú pháp: =HOUR(serial_number)
Mô tả: Trả về phần giờ (0‑23) của một giá trị thời gian.
Ví dụ: Nếu ô A2 chứa 14:35:20, công thức =HOUR(A2) sẽ trả về 14.

2.2. Hàm `MINUTE`

Cú pháp: =MINUTE(serial_number)
Mô tả: Trả về phần phút (0‑59) của một giá trị thời gian.
Ví dụ: =MINUTE(A2) → 35.

2.3. Hàm `SECOND`

Cú pháp: =SECOND(serial_number)
Mô tả: Trả về phần giây (0‑59) của một giá trị thời gian.
Ví dụ: =SECOND(A2) → 20.

Lưu ý: Các hàm này chỉ hoạt động với giá trị thời gian thực (được Excel nhận dạng). Nếu ô chứa dữ liệu dạng văn bản (text), bạn cần chuyển đổi sang kiểu thời gian trước khi sử dụng.

3. Chuyển Đổi Văn Bản Sang Thời Gian

Trong thực tế, nhiều khi dữ liệu thời gian được nhập dưới dạng văn bản (ví dụ: “14:35:20” được lưu dưới dạng text). Đối với trường hợp này, chúng ta có thể:

3.1. Sử dụng Hàm `TIMEVALUE`

Cú pháp: =TIMEVALUE(text)
Mô tả: Chuyển đổi một chuỗi văn bản mô tả thời gian thành giá trị thời gian mà Excel hiểu.
Ví dụ: Nếu ô B2 chứa "14:35:20" (văn bản), =TIMEVALUE(B2) sẽ trả về giá trị số thực tương đương (0.607372685). Khi định dạng lại ô dưới dạng hh:mm:ss, ta sẽ thấy 14:35:20.

3.2. Kết hợp `TIMEVALUE` với `HOUR`, `MINUTE`, `SECOND`

=HOUR(TIMEVALUE(B2)) // trả về 14
=MINUTE(TIMEVALUE(B2)) // trả về 35
=SECOND(TIMEVALUE(B2)) // trả về 20

3.3. Sử dụng `VALUE` Khi Định Dạng Chuẩn

Nếu chuỗi thời gian đã có định dạng chuẩn (ví dụ “14:35:20”), hàm VALUE cũng có thể chuyển đổi:

=HOUR(VALUE(B2))

4. Tách Giờ, Phút, Giây Thành Các Cột Riêng Biệt

Trong một số báo cáo, chúng ta muốn tách giờ, phút và giây ra các cột riêng để dễ dàng phân tích hoặc tính toán. Các bước thực hiện:

Giả sử dữ liệu thời gian nằm ở cột A, bắt đầu từ A2.
Cột B – Giờ: =HOUR(A2)
Cột C – Phút: =MINUTE(A2)
Cột D – Giây: =SECOND(A2)

Sau khi kéo công thức xuống, bạn sẽ có ba cột chứa các giá trị số nguyên, sẵn sàng cho việc điểm cộng, lọc, hoặc pivot.

4.1. Tạo Bảng Tổng Hợp (Pivot Table) Với Giờ, Phút, Giây

Bước 1: Chọn toàn bộ dải dữ liệu (bao gồm các cột giờ, phút, giây).
Bước 2: Vào Insert → PivotTable.
Bước 3: Kéo trường “Giờ” vào Rows, “Phút” vào Columns, và một trường giá trị (ví dụ “Số lượng”) vào Values.
Kết quả: Bạn sẽ có một ma trận hiển thị số lần xuất hiện của mỗi kết hợp giờ‑phút, rất hữu ích cho phân tích tần suất.

5. Tính Khoảng Thời Gian Giữa Hai Thời Điểm

5.1. Cách Cơ Bản

Giả sử ô A2 chứa thời gian bắt đầu, ô B2 chứa thời gian kết thúc:

= B2 - A2

Kết quả sẽ là một giá trị thời gian (phần thập phân) đại diện cho khoảng thời gian. Định dạng ô kết quả dưới dạng hh:mm:ss để hiển thị đúng.

5.2. Lấy Giờ, Phút, Giây Của Khoảng Thời Gian

Giờ: =INT((B2-A2)24)
Phút: =INT(MOD((B2-A2)2460,60))
Giây: =ROUND(MOD((B2-A2)246060,60),0)

Giải thích:
– 24 chuyển đổi ngày thành giờ.
– MOD(...,60) lấy phần dư 60 để tách phút và giây.
– INT lấy phần nguyên, ROUND làm tròn giây.

5.3. Định Dạng Thời Gian Năng Động Với `TEXT`

Nếu muốn hiển thị khoảng thời gian dưới dạng “hh:mm:ss” ngay trong công thức:

=TEXT(B2-A2, "hh:mm:ss")

Lưu ý: Khi thời gian kết thúc nhỏ hơn thời gian bắt đầu (đi qua ngày), cần cộng thêm 1 ngày: =TEXT(B2-A2+1, "hh:mm:ss").

6. Các Hàm Nâng Cao và Mẹo Thủ

6.1. Hàm `TIME` – Tạo Thời Gian Từ Các Thành Phần

Cú pháp: =TIME(hour, minute, second)
Sử dụng: Khi bạn có các giá trị giờ, phút, giây ở các cột riêng biệt và muốn ghép lại thành một thời gian hợp lệ.
Ví dụ: Giả sử C2=14, D2=35, E2=20 → =TIME(C2,D2,E2) trả về 14:35:20.

6.2. Hàm `TEXT` – Định Dạng Thời Gian Thành Chuỗi

Có thể bạn quan tâm: Công Thức Excel Nâng Cao: Bí Kíp Tối Ưu Hóa Dữ Liệu Và Tăng Hiệu Suất Làm Việc

Cú pháp: =TEXT(value, format_text)
Ví dụ: =TEXT(A2, "hh"" giờ ""mm"" phút ""ss"" giây") → “14 giờ 35 phút 20 giây”.

6.3. Hàm `DATEDIF` – Khoảng Cách Ngày Giữa Hai Ngày

Mặc dù không trực tiếp lấy giờ/phút/giây, nhưng khi làm việc với độ trễ qua nhiều ngày, DATEDIF giúp tính số ngày, sau đó cộng thời gian.

=DATEDIF(A2, B2, "d") & " ngày " & TEXT(B2-A2, "hh:mm:ss")

6.4. Hàm `NETWORKDAYS` và `WORKDAY` – Tính Thời Gian Làm Việc

Khi tính giờ làm việc thực tế, ta thường cần loại bỏ ngày nghỉ. NETWORKDAYS trả về số ngày làm việc, sau đó nhân với số giờ làm việc trong ngày (ví dụ 8h) và cộng thời gian thực tế:

= (NETWORKDAYS(A2, B2)-1)8/24 + (TIME(HOUR(B2), MINUTE(B2), SECOND(B2)) - TIME(HOUR(A2), MINUTE(A2), SECOND(A2)))

Kết quả là thời gian làm việc thực tế (định dạng hh:mm:ss).

7. Xử Lý Các Trường Hợp Đặc Biệt

7.1. Thời Gian Định Dạng “hhmmss” (Không Dấu Hai Chấm)

Nếu dữ liệu được nhập dưới dạng “143520” (không dấu “:”), bạn có thể:

=TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

Giải thích:

LEFT(A2,2) → 14 (giờ)
MID(A2,3,2) → 35 (phút)
RIGHT(A2,2) → 20 (giây)

7.2. Thời Gian Có Dấu “.” Để Bảo Lưu Millisecond

Ví dụ “14:35:20.125”. Excel không hỗ trợ millisecond trong định dạng chuẩn, nhưng ta có thể tách và giữ dưới dạng text hoặc số thập phân:

=TIMEVALUE(LEFT(A2,8)) + VALUE("0." & MID(A2,10,3))/86400

TIMEVALUE(LEFT(A2,8)) → 14:35:20
VALUE("0." & MID(A2,10,3))/86400 → chuyển millisecond thành ngày.

7.3. Đối Phó Với Dữ Liệu Không Đồng Nhất

Khi dữ liệu trong một cột chứa cả dạng thời gian chuẩn và dạng văn bản, bạn có thể dùng công thức mảng (hoặc IFERROR) để tự động chuyển đổi:

=IFERROR(HOUR(A2), HOUR(TIMEVALUE(A2)))

Tương tự cho MINUTE và SECOND.

8. Ứng Dụng Thực Tế

8.1. Tính Lương Dựa Trên Thời Gian Làm Việc

Giả sử:

Mức lương mỗi giờ: ô D1 = 150,000 VND.
Thời gian vào: cột A (hh:mm:ss).
Thời gian ra: cột B (hh:mm:ss).

Công thức tính lương trong ô C2:

= ((B2 - A2) 24) $D$1

(B2 - A2) 24 → số giờ làm việc.
Nhân với mức lương.

8.2. Tính Thời Gian Giao Hàng

Trong quản lý logistics, chúng ta thường cần tính thời gian giao hàng trung bình:

=AVERAGE(C2:C100) // C là cột thời gian giao hàng (định dạng hh:mm:ss)

Sau khi tính trung bình, dùng TEXT để hiển thị:

=TEXT(AVERAGE(C2:C100), "hh:mm:ss")

8.3. Đếm Số Lần Xuất Hiện Một Khoảng Thời Gian Cụ Thể

Ví dụ: Đếm số lần một cuộc gọi kéo dài hơn 5 phút:

=COUNTIF(D2:D500, ">" & TIME(0,5,0))

8.4. Tự Động Đánh Dấu Thời Gian Ngoài Giờ Làm Việc

Giả sử giờ làm việc là 08:00‑17:00. Đánh dấu các ô thời gian ra ngoài khoảng này bằng định dạng có điều kiện:

Chọn cột thời gian (ví dụ C2:C500).
Conditional Formatting → New Rule → Use a formula:
excel =OR(C2<TIME(8,0,0), C2>TIME(17,0,0))
Đặt màu nền cảnh báo (đỏ).

9. Tối Ưu Hóa Công Thức Và Tránh Lỗi Thông Thường

9.1. Tránh Sử Dụng `TEXT` Khi Cần Giá Trị Số

TEXT chuyển đổi thành chuỗi, làm mất khả năng tính toán. Chỉ dùng TEXT để hiển thị, không để làm dữ liệu gốc.

9.2. Kiểm Tra Định Dạng Ô

Đôi khi ô trông giống thời gian nhưng thực tế là text. Kiểm tra bằng cách:
excel =ISTEXT(A2) // trả về TRUE nếu là văn bản =ISNUMBER(A2) // trả về TRUE nếu là giá trị thời gian thực

9.3. Sử Dụng `$` Để Cố Định Tham Chiếu Khi Sao Chép Công Thức

Khi công thức tham chiếu tới ô chứa mức lương, ngày làm việc, hoặc thời gian chuẩn, hãy dùng $ để cố định:

= ((B2 - A2) 24) $D$1

9.4. Xử Lý Thời Gian Quá Đêm (Cross Midnight)

Nếu thời gian bắt đầu là 22:30 và kết thúc là 01:15 (ngày hôm sau), công thức =B2-A2 sẽ trả về giá trị âm. Giải pháp:

=IF(B2<A2, B2+1-A2, B2-A2)

Sau đó nhân 24 để có số giờ.

10. Tổng Kết

Việc lấy giờ, phút, giây trong Excel không chỉ là một thao tác cơ bản mà còn là nền tảng cho rất nhiều quy trình kinh doanh, phân tích dữ liệu và tự động hoá. Từ các hàm đơn giản như HOUR, MINUTE, SECOND cho đến các công thức phức tạp tính khoảng thời gian qua ngày, xử lý dữ liệu không đồng nhất, hay kết hợp với TIME, TEXT, NETWORKDAYS, Excel cung cấp một bộ công cụ đa dạng, mạnh mẽ.

Những điểm quan trọng cần nhớ:

Hiểu cách Excel lưu trữ thời gian (số thực ngày + thập phân thời gian) để tránh nhầm lẫn.
Chuyển đổi dữ liệu văn bản sang thời gian trước khi dùng các hàm lấy thành phần.
Sử dụng TIME để ghép lại các thành phần giờ, phút, giây khi cần tạo thời gian mới.
Xử lý các trường hợp đặc biệt như thời gian không có dấu “:”, millisecond, qua đêm, hoặc dữ liệu không đồng nhất.
Áp dụng Conditional Formatting và PivotTable để trực quan hoá và phân tích dữ liệu thời gian.
Luôn kiểm tra định dạng ô và sử dụng ISTEXT, ISNUMBER để bảo đảm công thức hoạt động chính xác.
Tối ưu công thức bằng cách cố định tham chiếu, tránh TEXT trong tính toán, và dùng IFERROR để xử lý lỗi.

Với những kiến thức và kỹ năng này, bạn sẽ có thể xử lý mọi tình huống liên quan đến thời gian trong Excel, từ việc tính lương, đo hiệu suất, lên lịch làm việc, đến phân tích dữ liệu log. Hãy thử áp dụng ngay vào dự án của mình và cảm nhận sự khác biệt mà việc nắm vững các hàm lấy giờ phút giây mang lại!

Chúc bạn thành công trong việc khai thác và phân tích thời gian trên Excel!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.