Để tính thời gian tăng ca trong Excel, ta có thể sử dụng công thức đơn giản sau:
- Cách 1 – sử dụng hàm MAX:
=MAX(0, Giờ ra - Giờ vào - Số giờ làm việc quy định)*24- Áp dụng khi không có làm ca đêm nên chắc chắn giờ ra > giờ vào)
- Cách 2 – sử dụng hàm IF:
=IF(Giờ ra > Giờ vào, (Giờ ra - Giờ vào - Số giờ làm việc quy định)*24,0)- Áp dụng khi có trường hợp có giờ làm ca đêm nên giờ ra có thể < giờ vào. Lúc này giờ tăng ca đêm ta sẽ tách riêng và tính sau.
Lưu ý: Trong công thức trên định dạng các dữ liệu:
- Giờ ra, giờ vào và giờ làm theo quy định được định dạng Time (hh:mm)
- Kết quả tính số giờ tăng ca được định dạng Number dạng số thập phân.
Dưới đây là hướng dẫn chi tiết cách tính thời gian tăng ca từ cơ bản đến các trường hợp nâng cao trong thực tế.
Tải File Excel tính giờ tăng ca ngay
1. Công thức tính giờ tăng ca cơ bản
Xuyên suốt bài này ta sẽ sử dụng dữ liệu bảng tính về thời gian chấm công ra vào của nhân viên dưới đây:
Để tính thời gian tăng ca, ta chỉ cần sử dụng công thức tính toán:
Thời gian tăng ca = (Giờ vào – Giờ ra) – Số giờ quy định làm việc
Áp dụng vào trong Excel ta có thể sử dụng hàm MAX để tính với công thức:
=MAX(0, (Giờ kết thúc - Giờ bắt đầu) - Giờ làm chuẩn)*24
Trong đó:
MAX(0, (Giờ kết thúc - Giờ bắt đầu) - Giờ làm chuẩn)để tính số giờ tăng ca.*24để quy đổi giờ thành giá trị số thập phân.
Áp dụng vào ví dụ trên, ta nhập vào ô I2 để tính số giờ tăng ca của nhân viên “Nguyễn Văn A” như sau: =MAX(0, (E2-D2) - F2)*24
Tuy nhiên nếu áp dụng công thức trên thì khi xảy ra trường hợp giờ ra < giờ bắt đầu (do làm qua đêm) nếu áp dụng công thức tính ca đêm với hàm MAX trong trường hợp này thì kết quả sẽ sai.
Ví dụ nhân viên “Trần Thị B” có giờ bắt đầu là 22:02 và giờ ra là 2:00 thì khi áp dụng công thức =MAX(0, (22:02-2:00) - 8:00)*24 thì sẽ cho ra kết quả là 0 trong khi rõ ràng là nhân viên có đi làm và tăng ca đêm.
Trong trường hợp này ta nên áp dụng công thức với hàm IF để đảm bảo chỉ tính giờ tăng ca khi giờ ra lớn hơn giờ bắt đầu, và sẽ tính riêng giờ tăng ca đêm (xem mục 3).
Sử dụng hàm IF sau:
=IF(Giờ ra > Giờ vào, (Giờ ra - Giờ vào - Số giờ làm việc quy định)*24,0)
Áp dụng vào ví dụ trên:
2. Công thức tính giờ tăng ca trừ đi thời gian nghỉ trưa
Đa số thời gian làm việc đều có thời gian nghỉ trưa/giải lao. Trong trường hợp này khi tính thời gian tăng ca cần phải trử đi phần thời gian nghỉ này:
Ta sẽ sử dụng hàm MAX để tính toán với công thức như sau:
=MAX(0,Giờ ra - Giờ bắt đầu - Giờ làm chuẩn - Giờ nghỉ trưa)*24
Áp dụng cho bảng dữ liệu trên ta sẽ tính giờ tăng ca đã trừ đi giờ nghỉ trưa như sau:
Tại ô J2 nhập công thức =MAX(0,(E2-D2-G2)-F2)*24
3. Công thức tính thời gian tăng ca đêm
Khi tính thời gian tăng ca đêm, thì giờ vào < giờ ra. Do đó, ta sẽ sử dụng hàm IF để chỉ tính khi thỏa mãn điều kiện này với công thức chung như sau:
=IF(Giờ kết thúc < Giờ bắt đầu, (Giờ kết thúc + 1 - Giờ bắt đầu - Giờ nghỉ) * 24, 0)
Trong trường hợp muốn tối ưu, tự động tính thời gian tăng ca khi cả tăng ca đêm, và tăng ca thông thường thì ta sử dụng công thức sau:
=IF(Giờ kết thúc < Giờ bắt đầu, (Giờ kết thúc + 1 - Giờ bắt đầu - Giờ nghỉ) * 24, (Giờ kết thúc - Giờ bắt đầu) * 24)
Áp dụng với bảng dữ liệu trong bài này, ta sẽ sử dụng cách tính thời gian làm việc tăng ca đêm riêng biệt (vì thông thường hệ số lương tăng ca đêm sẽ khác với hệ số lương tăng ca thông thường).
Ví dụ, ta cần tính số giờ tăng ca đêm của Trần Thị B ta sẽ nhập công thức vào ô K3 như sau:
=IF(E3<D3,(E3+1-D3 -G3)*24,0)
4. Công thức tính thời gian tăng ca ngày lễ/tết
Đối với ngày nghỉ lễ/tết, thường hệ số lương sẽ khác so với ngày bình thường. Để tiến hành tính thời gian tăng ca lương thì ta sẽ tạo thêm cột phân loại ngày và ghi chú “Lễ” cho các ngày lễ.
Sau đó ta sẽ sử dụng hàm IF với công thức như sau:
=IF(="Lễ", Giờ ra - Giờ bắt đầu - Giờ nghỉ trưa,0)
Áp dụng vào bảng dữ liệu trên:
Như vậy, bài viết trên đã hướng dẫn các bạn cách tính thời gian làm thêm bằng Excel. Hy vọng bài viết sẽ hữu ích với các bạn trong quá trình làm việc. Chúc các bạn thành công!
