Có thể bạn quan tâm: Cách Mở Vba Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z
1. Giới thiệu tổng quan về thời gian trong Excel
Excel không chỉ là công cụ tính toán số học thông thường, mà còn hỗ trợ mạnh mẽ trong việc xử lý dữ liệu thời gian – ngày, giờ, phút, giây. Khi làm việc với bảng chấm công, lịch làm việc, dự án hay bất kỳ bảng tính nào liên quan đến thời gian, việc trừ giờ là một thao tác thường xuyên và quan trọng.
Trong Excel, ngày và giờ được lưu trữ dưới dạng số thập phân:
– 1 ngày = 1 (số nguyên).
– 1 giờ = 1/24 ≈ 0.0416667.
– 1 phút = 1/1440 ≈ 0.00069444.
– 1 giây = 1/86400 ≈ 0.00001157.
Hiểu được cách Excel biểu diễn thời gian sẽ giúp bạn thực hiện các phép toán trừ giờ một cách chính xác, tránh lỗi “#VALUE!” hoặc hiển thị sai định dạng.
Có thể bạn quan tâm: Cách Khóa Hàm Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
2. Các phương pháp trừ giờ cơ bản
2.1. Dùng phép trừ trực tiếp giữa hai ô chứa thời gian
Giả sử ô A2 chứa thời gian bắt đầu (08:30) và ô B2 chứa thời gian kết thúc (17:45). Để tính thời gian làm việc, bạn chỉ cần:
=B2 - A2
Kết quả sẽ là 09:15 (9 giờ 15 phút) nếu định dạng ô là hh:mm. Để hiển thị dưới dạng số thập phân giờ, bạn có thể:
=(B2 - A2) 24
2.2. Trừ giờ với ngày (khi thời gian vượt qua ngày hôm sau)
Nếu công việc bắt đầu vào ngày hôm trước và kết thúc sau nửa đêm, ví dụ: A2 = 22:00 (ngày 1) và B2 = 02:00 (ngày 2). Khi trừ trực tiếp, Excel sẽ cho ra giá trị âm (-20:00). Để khắc phục:
=IF(B2 < A2, B2 + 1 - A2, B2 - A2)
Ở đây, +1 đại diện cho một ngày (24 giờ). Kết quả sẽ là 04:00 (4 giờ).
2.3. Trừ giờ với phần ngày (khi có cả ngày và giờ)
Nếu ô A2 chứa ngày và giờ (2026-04-01 09:00) và B2 chứa ngày và giờ (2026-04-03 15:30), công thức:
=B2 - A2
Sẽ trả về 2.270833 (2 ngày + 6,5 giờ). Để hiển thị dạng “2 ngày 6 giờ 30 phút”:
=INT(B2-A2) & " ngày " & TEXT(B2-A2,"hh"" giờ ""mm"" phút""")
Có thể bạn quan tâm: Cách Cố Định Hình Ảnh Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
3. Định dạng ô để hiển thị kết quả đúng
3.1. Định dạng thời gian chuẩn
- hh:mm – hiển thị giờ và phút.
- hh:mm – hiển thị tổng số giờ vượt quá 24 (đặc biệt hữu ích khi tổng thời gian làm việc > 24 giờ).
- hh:mm:ss – hiển thị giờ, phút, giây, cho phép tổng giờ > 24.
Lưu ý: Khi dùng dấu ngoặc vuông
hh, Excel sẽ không “quy đổi” 24 giờ thành 0, mà giữ nguyên giá trị giờ thực tế.
3.2. Định dạng số thập phân giờ
Nếu muốn kết quả ở dạng số thập phân (ví dụ: 9,25 giờ thay vì 09:15), áp dụng định dạng General hoặc Number và nhân kết quả với 24:
=(B2 - A2) 24
Bạn có thể tùy chỉnh số chữ số thập phân bằng cách vào Home → Number → Increase Decimal.
4. Các công thức nâng cao để trừ giờ
4.1. Trừ giờ có tính đến ngày nghỉ (holiday)
Giả sử bạn có danh sách ngày nghỉ trong phạm vi H2:H10. Để tính thời gian làm việc thực tế giữa A2 và B2, bỏ qua ngày nghỉ, bạn có thể dùng hàm NETWORKDAYS (hoặc NETWORKDAYS.INTL cho lịch làm việc tùy chỉnh).
=NETWORKDAYS.INTL(A2, B2, "0000011", $H$2:$H$10) - 1
Giải thích:
– "0000011" chỉ định ngày làm việc từ Thứ Hai đến Thứ Sáu (0 = làm việc, 1 = nghỉ).
– Trừ 1 để loại bỏ ngày bắt đầu nếu không muốn tính toàn bộ ngày.
Sau khi có số ngày làm việc, nhân với số giờ làm việc mỗi ngày (ví dụ 8 giờ):
=NETWORKDAYS.INTL(A2, B2, "0000011", $H$2:$H$10) 8
4.2. Trừ giờ có tính đến giờ làm việc trong ngày (shift)
Nếu công ty có ca làm việc 08:00‑12:00 và 13:00‑17:00, và bạn muốn tính thời gian làm việc thực tế giữa 2 thời điểm, cần loại bỏ thời gian nghỉ trưa và ngoài ca. Một cách tiếp cận là dùng hàm MAX và MIN để cắt các khoảng thời gian ra.
Có thể bạn quan tâm: Cách Thu Gọn Dòng Trong Excel: Hướng Dẫn Chi Tiết, Mẹo Thủ Và Ứng Dụng Thực Tiễn
=SUM( MAX(0, MIN(B2, TIME(12,0,0)) - MAX(A2, TIME(8,0,0))), MAX(0, MIN(B2, TIME(17,0,0)) - MAX(A2, TIME(13,0,0)))
)
Kết quả trả về dạng ngày (số thập phân). Để chuyển sang giờ:
=SUM( MAX(0, MIN(B2, TIME(12,0,0)) - MAX(A2, TIME(8,0,0))), MAX(0, MIN(B2, TIME(17,0,0)) - MAX(A2, TIME(13,0,0)))
) 24
4.3. Trừ giờ bằng hàm DATEDIF (đối với ngày)
Mặc dù DATEDIF không hỗ trợ giờ, bạn có thể kết hợp với hàm INT để lấy phần ngày:
=DATEDIF(A2, B2, "d") & " ngày " & TEXT(B2-A2, "hh"" giờ ""mm"" phút")
4.4. Sử dụng TEXT để hiển thị kết quả tùy chỉnh
Nếu muốn kết quả dạng “9 giờ 15 phút”:
=INT((B2-A2)24) & " giờ " & TEXT((B2-A2),"mm") & " phút"
Hoặc nếu muốn hiển thị “9.25 giờ” (giờ thập phân):
=ROUND((B2-A2)24,2) & " giờ"
5. Thực hành: Xây dựng bảng chấm công tự động
5.1. Bố cục bảng
| Nhân viên | Ngày | Giờ vào | Giờ ra | Tổng giờ làm | Ghi chú |
|---|---|---|---|---|---|
| A | 01/05/2026 | 08:15 | 17:05 | ||
| B | 01/05/2026 | 09:00 | 18:30 |
5.2. Công thức tính tổng giờ làm
Giả sử cột C là Giờ vào, D là Giờ ra, E là Tổng giờ làm.
E2 = IF(D2 < C2, D2 + 1 - C2, D2 - C2)
Sau đó, định dạng cột E là hh:mm để hiển thị tổng giờ vượt qua 24 nếu có.
55.3. Tính tiền lương dựa trên tổng giờ
Giả sử mức lương giờ là 150,000 VND (ô G1). Công thức tính tiền lương:
=E2 24 $G$1
Định dạng cột F là Currency (đơn vị VND).
5.4. Xử lý trường hợp nhân viên làm qua đêm
Nếu một nhân viên bắt đầu vào lúc 22:00 và kết thúc vào 06:00 ngày hôm sau, công thức trong E2 đã xử lý bằng cách cộng 1 ngày. Đảm bảo rằng Giờ vào và Giờ ra luôn là kiểu Time, không có ngày đi kèm, để công thức hoạt động đúng.
6. Các lỗi thường gặp khi trừ giờ và cách khắc phục
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
| #VALUE! | Ô chứa dữ liệu không phải dạng thời gian (ví dụ: chuỗi “08:30am” không được nhận dạng) | Đảm bảo ô được định dạng là Time hoặc dùng hàm TIMEVALUE để chuyển chuỗi thành thời gian. |
| -#NUM! (giá trị âm) | Giờ ra nhỏ hơn giờ vào mà không cộng thêm ngày | Sử dụng công thức IF(B2 < A2, B2 + 1 - A2, B2 - A2) hoặc kiểm tra lại dữ liệu nhập. |
| Hiển thị 00:00 thay vì 24:00 | Định dạng hh:mm tự động quy đổi 24 giờ thành 0. | Dùng định dạng hh:mm để hiển thị 24 giờ đúng. |
| Kết quả 0.99999 thay vì 1 | Lỗi làm tròn do số thập phân không chính xác. | Dùng hàm ROUND, ROUNDUP hoặc ROUNDDOWN tùy nhu cầu. |
| Không tính được thời gian làm việc khi có ngày nghỉ | Công thức chỉ trừ giờ, không xét ngày. | Sử dụng NETWORKDAYS hoặc WORKDAY.INTL để loại trừ ngày nghỉ. |
7. Mẹo và thủ thuật nâng cao
7.1. Sử dụng Power Query để xử lý khối lượng lớn
Khi dữ liệu thời gian nằm trong hàng nghìn, việc dùng công thức trên mỗi hàng có thể làm file chậm. Power Query cho phép:
- Nhập dữ liệu vào Power Query.
- Chuyển đổi cột thời gian thành kiểu Duration.
- Tạo cột tùy chỉnh:
Duration.From(Giờ ra - Giờ vào)(công thức M). - Tải lại kết quả về Excel.
7.2. Tự động cộng ngày khi trừ giờ qua đêm bằng Custom Format
Nếu bạn luôn có trường hợp làm việc qua đêm, bạn có thể tạo định dạng tùy chỉnh cho ô Giờ ra:
h:mm;Red-h:mm
Khi giá trị âm xuất hiện, Excel sẽ hiển thị màu đỏ và giá trị âm, giúp bạn nhanh chóng nhận ra lỗi nhập liệu.
7.3. Sử dụng VBA để trừ giờ phức tạp
Đối với các quy tắc tính giờ phức tạp (ví dụ: tính giờ làm thêm, tính giờ làm việc theo ca), bạn có thể viết macro:
Function CalcWorkHours(StartTime As Date, EndTime As Date) As Double Dim diff As Double If EndTime < StartTime Then diff = (EndTime + 1) - StartTime Else diff = EndTime - StartTime End If CalcWorkHours = diff 24 ' trả về số giờ thập phân
End Function
Sau khi lưu macro, bạn gọi trong ô:
=CalcWorkHours(C2, D2)
8. Tổng kết
Việc trừ giờ trong Excel không chỉ là một phép tính đơn giản mà còn mở ra nhiều khả năng xử lý dữ liệu thời gian phức tạp. Từ các công thức cơ bản =B2-A2 đến các hàm nâng cao như NETWORKDAYS, WORKDAY.INTL, hay thậm chí VBA, bạn có thể tùy biến để đáp ứng mọi yêu cầu thực tiễn: chấm công, tính lương, quản lý dự án, hay lập kế hoạch sản xuất.
Các bước quan trọng cần nhớ
- Hiểu cách Excel lưu trữ ngày và giờ (1 ngày = 1, 1 giờ = 1/24).
- Định dạng ô đúng (
hh:mm,hh:mm, hoặc số thập phân). - Xử lý trường hợp qua đêm bằng cách cộng 1 ngày khi giờ ra < giờ vào.
- Sử dụng hàm hỗ trợ (
NETWORKDAYS,WORKDAY.INTL) để tính ngày làm việc và loại trừ ngày nghỉ. - Kiểm tra lỗi và áp dụng các biện pháp khắc phục (định dạng, hàm IF, ROUND).
- Áp dụng Power Query hoặc VBA khi dữ liệu lớn hoặc quy tắc tính phức tạp.
Bằng cách nắm vững các kiến thức trên, bạn sẽ tự tin thực hiện bất kỳ phép trừ giờ nào trong Excel, đồng thời tối ưu hoá quy trình làm việc, giảm thiểu sai sót và nâng cao hiệu suất. Chúc bạn thành công trong việc quản lý thời gian và dữ liệu!
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 hì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ẻ.