Với các File excel về thời gian chấm công, thời gian chạy máy,… ta thường phải thực hiện các phép tính cộng/trừ thời gian giờ/phút/giây. Nếu không hiểu rõ cách Excel xử lý dữ liệu thời gian, thì rất có thể kết quả tính toán sẽ bị sai hoặc hiển thị không đúng. Dưới đây Kế Toán VN sẽ hướng dẫn chi tiết cách cộng giờ, phút, giây trong Excel từ A – Z và kèm file thực hành:
1. Cách Excel tính thời gian
Excel không lưu thời gian dưới dạng hiển thị “giờ phút giây” (ví dụ như: 15:20:02) như ta thấy mà lưu dưới dạng số thập phân của một ngày. Theo đó:
- 1 Ngày = 1 đơn vị
- 1 Giờ = 1/24
- 1 Phút = 1/(24×60)
- 1 Giây = 1/(24x60x60)
Ví dụ nếu ta nhập “12:00” vào trong ô Excel, thì giá trị thực tế của ô là 0.5 (ví 12 giờ là nửa ngày).
Do đó, khi cộng thời gian giờ, phút, giây trong Excel ta cần phải định dạng ô kết quả để hiển thị đúng.
2. Cách định dạng hiển thị giờ, phút, giây trong Excel
Trước khi thực hiện cộng giờ, ta cần phải định dạng các ô theo định dạng thời gian phù hợp thì kết quả tính và hiển thị mới chính xác.
Cách thực hiện như sau:
- Chọn vùng dữ liệu hoặc ô kết quả.
- Nhấn Ctrl + 1 (hoặc chuột phải → Format Cells).
- Chọn Number → Custom.
- Chọn một trong các định dạng sau:
- h:mm:ss → hiển thị giờ phút giây (tối đa 24h).
- [h]:mm:ss → hiển thị tổng số giờ vượt quá 24h (ví dụ: 30:45:12).
Mẹo: Ta nên lựa chọn định dạng [h]:mm:ss khi tính tổng giờ, phút, giây vì khi cộng thời gian rất dễ vượt qua 24 giờ.
3. Cách tính tổng giờ, phút, giây trong Excel
Khi đã hiểu nguyên lý excel xử lý dữ liệu thời gian và cách định dạng sao cho chuẩn, dưới đây là cách trường hợp hay gặp khi tính tổng giờ, phút, giây:
Cách 1: Cộng thời gian giữa các ô
Giả sử ta có bảng tính sau:
| Giờ bắt đầu (A2) | Giờ kết thúc (B2) | Tổng thời gian |
|---|---|---|
| 08:15:20 | 12:45:40 | =B2-A2 |
Để tính tổng thời gian giữa các ô, ta chỉ cần sử dụng phép toán cộng. Ví dụ như ở bảng dữ liệu trên thì kết quả của công thức =B2-A2 sẽ trả về 4:30:20 .
Cách 2: Cộng nhiều ô thời gian cùng lúc bằng hàm SUM
Khi có nhiều ô chứa thời gian và cần tính tổng, ta sử dụng hàm SUM để tính tổng thời gian 1 cách nhanh chóng.
Ví dụ ta có bảng dữ liệu sau:
| A | B | |
|---|---|---|
| 1 | Ngày | Giờ làm việc |
| 2 | Thứ 2 | 8:30:00 |
| 3 | Thứ 3 | 7:45:30 |
| 4 | Thứ 4 | 9:15:00 |
Để tính tổng giờ làm việc từ thứ 2 đến thứ 4, ta sử dụng hàm SUM với công thức sau:
=SUM(B2:B4)
Kết quả của hàm sẽ trả về 25:30:30 kết quả này sẽ tương đương với việc sử dụng công thức =B2+B3+B4
Cách 3: Cộng giờ, phút, giây bằng hàm TIME
Khi có dữ liệu giờ, phút, giây ở các cột riêng, ta có thể dùng hàm TIME để tạo ra các giá trị thời gian.
Ví dụ với bảng dữ liệu sau:
| A | B | C | |
|---|---|---|---|
| 1 | Giờ | Phút | Giây |
| 2 | 2 | 45 | 30 |
| 3 | 1 | 30 | 45 |
Lúc này, ta tính tổng giá trị thời gian đầy đủ (gồm giờ:phút:giây) với hàm TIME như sau:
=TIME(A2,B2,C2) + TIME(A3,B3,C3)
Kết quả hàm trên sẽ trả về 4:16:15
Cách này thường hiệu quả khi tách dữ liệu thời gian thành từng phần (ví dụ khi nhập từ các máy chấm công hoặc liệu IoT).
Cách 4: Cách cộng thêm giờ, phút, giây vào thời điểm có sẵn
Nếu có 1 thời gian cố định và muốn cộng thêm một khoảng thời gian, ta có thể sử dụng hàm TIME trực tiếp.
Ví dụ với bảng dữ liệu sau:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Giờ gốc | Thêm thời gian | Tính tổng | Kết quả |
| 2 | 08:00:00 | +2h 30m | =A2 + TIME(2,30,0) |
10:30:00 |
| 3 | 08:00:00 | +45 giây | =A2 + TIME(0,0,45) |
08:00:45 |
4. Các lỗi thường gặp khi cộng giờ, phút, giây trong Excel
| Lỗi hiển thị | Nguyên nhân | Cách khắc phục |
|---|---|---|
| 0.45833 | Ô chưa được định dạng thời gian | Đổi sang định dạng [h]:mm:ss |
| #VALUE! | Dữ liệu nhập sai định dạng (chứa ký tự văn bản) | Kiểm tra dữ liệu, loại bỏ ký tự không hợp lệ |
| Tổng vượt 24h bị reset | Dùng định dạng h:mm:ss |
Đổi sang [h]:mm:ss |
Excel không chỉ tính được số, mà còn xử lý thời gian cực kỳ linh hoạt. Khi bạn nắm rõ quy tắc lưu trữ và định dạng thời gian, việc cộng giờ, phút, giây trở nên cực kỳ đơn giản. Chúc các bạn áp dụng thành công!
