Trong bài viết này, Kế Toán VN sẽ hướng dẫn bạn cách tính số làm việc trong Excel để làm bảng lương, tính giờ công, ngày công và phục vụ các công việc khác. Bài viết này sẽ đi vào chi tiết và thực hành thực tế.
Tải ngay file ví dụ Excel tính giờ làm việc chi tiết
1. Nguyên lý tính thời gian trong Excel
Thời gian trong Excel có thể quy đổi thành phần của 1 ngày. Ví dụ:
- 1 ngày = 1.
- 1 giờ = 1/24 ngày
- 1 phút = 1/1440 ngày
Vì vậy, áp dụng nguyên lý trên để tính số giờ làm việc ta sẽ sử dụng công thức sau:
- Số giờ = (Giờ ra − Giờ vào − Nghỉ/1440) × 24
- Nếu qua nửa đêm (ra < vào) thì coi Giờ ra + 1 (thêm 1 ngày).
Áp dụng nguyên lý trên, trong bài này ta sẽ cùng đi vào một bảng dữ liệu chấm công thực tế để từ đó tính giờ làm trong cả các trường hợp, tăng ca, làm qua đêm, làm ngày lễ,…
Trong File này ta sẽ có 3 Sheet:
- Sheet “ThamSo” cung cấp cấu hình chấm công, thiết lập ngày công và danh sách ngày nghỉ lễ.
- Sheet “TimeSheet” sẽ gồm dữ liệu chấm công vào ra chi tiết các ngày trong tháng.
- Sheet “TongKetThang” sẽ cung cấp con số tổng kết giờ làm việc của tháng.
2. Công thức tính giờ làm việc
Áp dụng trực tiếp vào dữ liệu mẫu trong bài ta lần lượt tính giờ làm việc, ta giả sử như sau:
- Ngày = A2
- Vào = B2 (định dạng hh:mm)
- Ra = C2 (định dạng hh:mm)
- Nghỉ (phút) = D2 (số phút, vd 60)
- Số giờ chuẩn/ngày = ThamSo!B6 (vd 8)
- Bước làm tròn (phút) = ThamSo!B7 (vd 15)
- Danh sách ngày lễ = ThamSo!A10:A1000
2.1. Công thức tính giờ làm việc thô (xử lý cả giờ ca qua đêm và trừ thời gian nghỉ)
Công thức tính thời gian làm việc (tính cả ca đêm và trừ thời gian nghỉ):
=IF(OR(B2="",C2=""),"", MAX(0, ((IF(C2<B2, C2+1, C2) - B2) - D2/1440) * 24))
Giải thích:
IF(C2<B2, C2+1, C2)cộng thêm 1 ngày nếu ra < vào (ca qua đêm).D2/1440chuyển phút nghỉ thành “phần của ngày”.*24đổi “phần của ngày” thành giờ thập phân.MAX(0, …)bảo vệ khỏi kết quả âm khi thiếu dữ liệu.
2.2. Công thức làm tròn giờ làm việc
Đa số công ty/PM nhân sự không tính lẻ từng phút mà quy ước bậc 5/6/10/12/15/30/60 phút, do đó ta cần làm tròn giờ để làm tròn lên bậc tương ứng giúp hỗ trợ tính lương.
Để làm tròn giờ, ta sử dụng công thức sau:
=IF(E2="","", MROUND(E2, ThamSo!B7/60))
MROUND(số, bậc)sẽ kéo E2 về bội số gần nhất của bậc = B7/60.- Ví dụ B7=15 → bậc = 0,25 giờ. E2 = 8,23 giờ (~ 8:13) gần nhất với 8,25 giờ (8:15) hơn là 8,00 hay 8,50.
Nếu phiên bản Excel không có hàm MROUND, thì ta có thể sử dụng hàm ROUND:
=IF(E2="","", ROUND(E2*60/ThamSo!B7, 0) * ThamSo!B7/60)
Trong đó:
- E2*60 → đổi giờ → phút.
- /B7 → đổi phút → số bậc (bao nhiêu “block” 15’).
- ROUND(…,0) → làm tròn đến block gần nhất.
- *B7 → quay về phút.
- /60 → quay về giờ.
Ngoài ra, tùy vào quy định của công ty ta cũng có thể tính làm tròn lên hoặc làm tròn xuống với làm CEILING và FLOOR như sau:
- Làm tròn giờ lên:
=IF(E2="","", CEILING(E2, ThamSo!B7/60))ví dụ: Ví dụ 8,01h với bậc 0,25h → 8,25h - Làm tròn giờ xuống:
=IF(E2="","", FLOOR(E2, ThamSo!B7/60))ví dụ: 8,24h với bậc 0,25h → 8,00h
2.3. Công thức tính giờ làm việc tăng ca (OT)
Khi tính giờ làm việc thực tế, chắc chắn sẽ phải tính số giờ tăng ca bởi vì giờ tăng ca sẽ nhân với hệ số lương khác lương thường ngày.
Để tính giờ tăng ca (hay OT) ta sử dụng công thức sau:
=IF(F2="","", MAX(0, F2 - ThamSo!B6))
Trong đó:
F2= Công làm tròn (giờ) trong ngày (đã xử lý ca qua đêm + trừ nghỉ + làm tròn).ThamSo!B6= Giờ chuẩn/ngày (vd 8).F2 - ThamSo!B6= phần giờ vượt chuẩn.MAX(0, …)= không âm (nếu làm < chuẩn thì OT = 0).IF(F2="","", …)= nếu chưa nhập giờ vào/ra thì để trống (tránh rác số).
Ví dụ như:
- Nếu F2 = 8,25h (8h15’) và B6 = 8 → thì thời gian OT = 0,25h
- Nếu F2 = 7,50h và B6 = 8 → thời gian OT = 0h
- Nếu F2 trống → thời gian OT trống
Lưu ý: Công thức trên đang tính thời gian tăng ca trên giờ đã làm tròn. Nếu muốn tính thời gian tăng ca trên giờ trước làm tròn thì chỉ cần thay F2 thành E2.
Ngoài cách tính giờ tăng ca trên, ta có thể phát triển thêm các biến thể công thức tính tăng ca khác như:
- Tạo giới hạn thời gian tăng ca/ngày (VD: 4 tiếng):
=IF(F2="","",
MIN(4, MAX(0, F2 - ThamSo!B6))) - Tính tổng thời gian tăng ca của tuần:
- Tạo 2 ô phụ (ở đâu tùy bạn):
- Tuần bắt đầu:
=A2-WEEKDAY(A2,2)+1(thứ Hai của tuần A2) - Tuần kết thúc:
=A2+(7-WEEKDAY(A2,2))
- Tuần bắt đầu:
- Tính giờ chuẩn tuần (loại T7, CN & nghỉ lễ):
=ThamSo!B6 * NETWORKDAYS(tuan_bat_dau, tuan_ket_thuc, ThamSo!A10:A1000) - Làm tròn giờ làm việc tuần:
=SUMIFS(F:F, A:A, ">="&tuan_bat_dau, A:A, "<="&tuan_ket_thuc) - Tính thời gian tăng ca cả tuần:
=MAX(0, tong_gio_tuan - gio_chuan_tuan)
- Tạo 2 ô phụ (ở đâu tùy bạn):
2.4. Đánh dấu ngày làm việc cuối tuần & ngày lễ
Trong quá trình làm việc, chắc chắn phải có ngày lễ, ngày nghỉ hay chỉ đơn giản là cuối tuần. Nếu người lao động có đi làm vào những ngày này sẽ có mức tính lương khác nhau. Để tiện tính toán thời gian làm việc, ta sẽ cần phải đánh dấu những ngày đó để tính toán sau này.
Để đánh dấu ta sử dụng công thức sau:
- Đánh dấu là ngày cuối tuần:
=IF(A2="","", IF(WEEKDAY(A2,2)>5,"Cuối tuần","")) - Đánh dấu là ngày lễ:
=IF(A2="","", IF(COUNTIF(ThamSo!A10:A1000, A2)>0, "Ngày lễ",""))
2.5. Phân loại giờ làm việc tính lương (ngày thường, ngày cuối tuần, ngày lễ)
Nếu tính lương theo số giờ làm việc, thì ta cũng cần phải phân biệt rõ số giờ làm việc tính theo lương thường, số giờ làm việc ngày cuối tuần hoặc ngày lễ.
Để phân loại ta sử dụng công thức sau:
- Phân loại giờ ngày thường:
=IF(OR(I2<>"",J2<>""), 0, F2) ' Giờ ngày thường - Phân loại giờ cuối tuần/lễ:
=IF(OR(I2<>"",J2<>""), F2, 0) ' Giờ cuối tuần/lễ
3. Công thức tính giờ làm việc đơn giản
Nếu bảng chấm công chỉ đơn giản gồm giờ vào, giờ ra và ta chỉ cần tính thời gian làm việc. Ví dụ ta có bảng chấm công như hình dưới. Yêu cầu tính thời gian làm việc trong ngày của từng người.
Lúc này ta chỉ cần thực hiện tính thời gian làm việc dựa trên hàm HOUR và hàm MINUTE như sau:
Bước 1. Tính số giờ làm việc bằng cách lấy giờ ra trừ giờ vào
Cách đơn giản nhất để tính thời gian làm việc trên bảng chấm công chính là lấy giờ ra trừ giờ vào. Tại ô D2 các bạn nhập công thức như sau:
=C2-B2
Sao chép công thức cho tất cả ô còn lại ta sẽ thu được kết quả.
Bước 2. Tính thời gian làm việc bằng cách sử dụng hàm
Bằng cách này thì ta sẽ cần sử dụng hai hàm đó là hàm HOUR và hàm MINUTE. Cấu trúc của hai hàm như sau:
a. Hàm HOUR
Cú pháp hàm: =HOUR(serial_number)
Trong đó: serial_number: đối số bắt buộc, là thời gian có chứa giờ mà bạn muốn tách ra.
Hàm HOUR được sử dụng trong Excel để trả về giá trị là giờ trong ô Excel. Giá trị giờ được trả về ở dạng số nguyên từ 0 (12:00 SA) đến 23 (11:00 CH).
b. Hàm MINUTE
Cú pháp hàm: =MINUTE(serial_number)
Trong đó: serial_number: đối số bắt buộc, là thời gian có chứa phút mà bạn muốn tách ra.
Hàm MINUTE được sử dụng trong Excel để trả về giá trị là phút trong ô Excel. Giá trị phút được trả về ở dạng số nguyên từ từ 0 tới 59.
Áp dụng cấu trúc của hai hàm như trên ta có công thức tính thời gian làm việc trong bảng chấm công như sau:
=(HOUR(giờ ra)*60-HOUR(giờ vào)*60+MINUTE(giờ ra)-MINUTE(giờ vào))/60
Tại ô D2 các bạn nhập công thức như sau:
=(HOUR(C2)*60-HOUR(B2)*60+MINUTE(C2)-MINUTE(B2))/60
Sao chép công thức cho các ô bên dưới. Sau đó các bạn bôi đen toàn bộ ô kết quả rồi chọn định dạng General tại mục Number.
Kết quả ta sẽ thu được thời gian làm của tất cả nhân viên như sau:
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 việc trên bảng chấm cô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!
