Hàm HOUR trong Excel thường trả về kết quả không như mong đợi ngay cả khi cú pháp đúng. Tôi từng tính sai 20 giờ làm thêm của nhân viên vì một trong ba lỗi này. Công thức hiển thị số 0 hoặc lỗi VALUE, nhưng không rõ nguyên nhân ở đâu.
Vấn đề thực sự là gì
Ba lỗi này xuất hiện ngay cả với người dùng có kinh nghiệm. Lỗi đầu tiên khiến toàn bộ cột giờ hiển thị 12:00 thay vì giá trị thực. Lỗi thứ hai làm công thức trả về VALUE ngay lập tức. Lỗi cuối cùng tính thiếu 8 giờ khi ca làm việc qua nửa đêm. Mỗi lỗi có thể gây sai số hàng trăm giờ trong báo cáo tháng.
Định dạng ô hiển thị số thay vì giờ
Lỗi phổ biến nhất xảy ra khi ô chứa kết quả được định dạng là số thập phân hoặc số nguyên. Hàm HOUR trả về giá trị từ 0 đến 23, nhưng Excel hiển thị dưới dạng 0.708333 hoặc 17 thay vì 17:00.
Nguyên nhân: Excel lưu thời gian dưới dạng số thập phân. Giá trị 0.5 tương đương 12:00 trưa vì là nửa ngày. Khi ô kết quả không được định dạng đúng, số thô hiển thị thay vì giờ phút.
Cách khắc phục:
- Chọn cột chứa kết quả hàm HOUR
- Nhấn Ctrl + 1 để mở hộp thoại Format Cells
- Chọn Category là Number
- Đặt Decimal places về 0
- Nhấn OK
Kết quả sẽ hiển thị 17 thay vì 0.708333. Nếu cần định dạng giờ đầy đủ, kết hợp với hàm TIME hoặc TEXT.
Kiểm tra nhanh: Nếu thấy số thập phân trong cột giờ, đây chính là lỗi định dạng. Thay đổi format sang Number với 0 decimal places để sửa ngay lập tức.
Dữ liệu đầu vào là văn bản không phải thời gian
Hàm HOUR trả về lỗi VALUE khi ô tham chiếu chứa văn bản trông giống thời gian. Điều này xảy ra khi import dữ liệu từ file CSV, copy từ web, hoặc nhập thủ công với dấu cách thừa.
Ví dụ: Ô A2 chứa “14:30” dưới dạng text. Công thức =HOUR(A2) trả về VALUE thay vì 14.
Phát hiện lỗi:
- Thời gian căn trái trong ô thay vì căn phải
- Có dấu cách ở đầu hoặc cuối
- Công thức ISNUMBER trả về FALSE
Giải pháp 1 – Chuyển đổi thủ công:
- Chọn cột chứa dữ liệu thời gian dạng text
- Vào Data > Text to Columns
- Chọn Delimited và nhấn Next hai lần
- Ở bước Column data format, chọn Date
- Chọn định dạng MDY hoặc DMY phù hợp
- Nhấn Finish
Giải pháp 2 – Dùng hàm TIMEVALUE: Thay công thức =HOUR(A2) bằng =HOUR(TIMEVALUE(A2))
Hàm TIMEVALUE chuyển đổi text thành số thời gian trước khi HOUR xử lý. Phương pháp này hoạt động với định dạng 24 giờ và 12 giờ kèm AM/PM.
Lưu ý: TIMEVALUE không chuyển đổi được nếu text chứa ngày tháng. Sử dụng DATEVALUE trước rồi mới dùng TIMEVALUE, hoặc kết hợp VALUE với điều chỉnh định dạng ô.
Tôi gặp lỗi này khi import 500 dòng dữ liệu chấm công từ hệ thống cũ. Sau khi áp dụng Text to Columns cho cột thời gian, toàn bộ công thức HOUR hoạt động chính xác.
Tính chênh lệch giờ qua nửa đêm trả về số âm
Lỗi phức tạp nhất xuất hiện khi tính số giờ làm việc từ 22:00 đến 06:00 sáng hôm sau. Công thức =HOUR(B2-A2) trả về lỗi NUM hoặc giá trị sai hoàn toàn.
Nguyên nhân: Excel tính thời gian kết thúc trừ thời gian bắt đầu. Khi thời gian kết thúc nhỏ hơn (06:00 < 22:00), kết quả âm. Hàm HOUR không xử lý được số âm.
Ví dụ cụ thể:
- A2: 22:00 (giờ vào ca)
- B2: 06:00 (giờ tan ca)
- Công thức =HOUR(B2-A2) trả về NUM
- Công thức =(B2-A2)*24 trả về -16 thay vì 8 giờ
Giải pháp đúng: Dùng công thức =MOD(B2-A2,1)*24 để xử lý ca qua đêm.
Phân tích công thức:
- B2-A2 trả về -0.666667 (âm vì 06:00 < 22:00)
- MOD(số,-1) lấy phần dư khi chia cho 1
- MOD(-0.666667,1) = 0.333333
- Nhân 24 để chuyển thành giờ: 0.333333 * 24 = 8 giờ
Áp dụng cho nhiều ca:
- Tại cột Số giờ làm việc, nhập =MOD(B2-A2,1)*24
- Kéo công thức xuống các dòng tiếp theo
- Định dạng cột kết quả là Number với 1 decimal place
Công thức này xử lý cả ca ngày (08:00 đến 17:00) và ca đêm (22:00 đến 06:00) mà không cần điều kiện IF phức tạp.
Trường hợp nâng cao: Nếu ca làm việc kéo dài qua 24 giờ (ví dụ 22:00 hôm nay đến 14:00 ngày mai = 16 giờ), cần thêm ngày tháng vào dữ liệu. Công thức trở thành =MOD(B2+C2-A2-D2,1)*24 với C2 và D2 là ngày tương ứng.
Kiểm tra nhanh để tránh lỗi
Ba bước kiểm tra này giúp phát hiện lỗi trước khi công thức lan rộng sang hàng trăm dòng.
Bước 1 – Kiểm tra định dạng: Nhấn Ctrl + 1 trên ô đầu tiên chứa thời gian. Nếu Category là Text hoặc General thay vì Time, chuyển sang Time và chọn định dạng 13:30 hoặc 1:30 PM.
Bước 2 – Test với dữ liệu đơn giản: Nhập 14:30 vào ô mới, dùng =HOUR(ô_vừa_nhập). Nếu trả về 14, công thức đúng. Nếu trả về VALUE hoặc 0, dữ liệu gốc có vấn đề.
Bước 3 – Xem công thức đầy đủ: Chọn ô chứa công thức và nhấn F2. Kiểm tra tham chiếu có đúng ô chứa thời gian không. Tìm dấu cách thừa hoặc ký tự đặc biệt trong công thức.
Ba lỗi này chiếm 90 phần trăm trường hợp hàm HOUR hoạt động không đúng. Lỗi định dạng xảy ra nhiều nhất với người mới, lỗi text data xuất hiện khi import file, và lỗi tính qua nửa đêm gặp trong quản lý ca làm việc. Kiểm tra định dạng ô và loại dữ liệu trước khi áp dụng công thức cho toàn bộ bảng tính. Hàm HOUR hoạt động ổn định trên Excel 2016 trở lên khi dữ liệu đầu vào đúng format.
