Chấm công theo giờ vào – giờ ra: công, đi muộn, tăng ca, ca đêm
Đầu bài
Máy chấm công xuất ra giờ vào và giờ ra từng ngày. Cần quy ra số công, số phút đi muộn, số giờ tăng ca — và ca đêm ra về hôm sau thì không được ra số âm.
Các bước làm
- 1
Trừ giờ mà không sợ qua đêm
=MOD(giờ_ra - giờ_vào, 1) — MOD tự cộng thêm một ngày khi giờ ra nhỏ hơn giờ vào, nên ca 22h–6h ra đúng 8 tiếng thay vì âm 16 tiếng. Cả bài xoay quanh mẹo này.
- 2
Đổi ra giờ thập phân để nhân tiền
=MOD(...)*24, rồi Ctrl+Shift+` trả ô về General. Để nguyên định dạng giờ là nhìn thấy 8:00 rất hợp lý nhưng nhân lương ra sai bét.
- 3
Trừ giờ nghỉ trưa cho đúng người
Thô thì =MAX(0, số_giờ - 1). Chuẩn hơn thì trừ đúng phần giao với khung nghỉ: =MAX(0, MIN(giờ_ra, giờ_hết_nghỉ) - MAX(giờ_vào, giờ_bắt_đầu_nghỉ)) — cách này đúng cả với người về trước giờ nghỉ.
- 4
Đi muộn, về sớm tính bằng phút
=MAX(0, (giờ_vào - giờ_chuẩn_vào)*24*60) và =MAX(0, (giờ_chuẩn_ra - giờ_ra)*24*60). Bọc MAX(0, …) để người đến sớm không bị cộng điểm âm thành ra được thưởng.
- 5
Tăng ca tách theo ba loại
Ngày thường =MAX(0, số_giờ - 8); cuối tuần =IF(WEEKDAY(ngày,2)>=6, số_giờ, 0); ngày lễ =IF(COUNTIF(ds_lễ, ngày)>0, số_giờ, 0). Ba loại hệ số khác nhau nên bắt buộc ba cột.
- 6
Làm tròn theo quy định công ty
=FLOOR(số_giờ, 0.5) nếu tính tròn nửa tiếng. Chốt một quy tắc rồi ghi ngay cạnh bảng — chỗ này mỗi nơi mỗi khác và là chỗ nhân viên thắc mắc nhiều nhất.
- 7
Cộng về từng người rồi soát
=SUMIFS(số_giờ, cột_mã_NV, mã) cho cả tháng. Hai chốt chặn: =COUNTIFS(cột_mã_NV, mã) phải bằng số ngày có chấm, và không dòng nào được vượt 16 tiếng — vượt là quẹt thẻ sót một lần.
Phím dùng trong bài
Bẫy hay dính
Giờ trong Excel là phân số của một ngày, nên cộng quá 24 tiếng thì ô hiện 1:30 thay vì 25:30. Muốn hiện đúng tổng giờ thì Ctrl+1 đặt định dạng tuỳ chỉnh [h]:mm — cặp ngoặc vuông chính là thứ bảo Excel đừng quay vòng về 0.
Cùng nhóm nhân sự, chấm công & lương
- Bảng chấm công ký hiệu → quy ra lương thángBảng chấm công ghi X, P, KP, ½ cho từng ngày. Cuối tháng phải đếm công, quy ra tiền, trừ ngày nghỉ không phép — làm tay dễ đếm sót.
- Từ file bán hàng thô → bảng hoa hồng từng nhân viênXuất file bán hàng từ phần mềm ra, cần tính hoa hồng mỗi nhân viên theo doanh số lũy tiến, cộng thưởng nếu vượt chỉ tiêu. Làm tay mỗi kỳ mất cả buổi.
- Timesheet dự án → chi phí theo giờ và theo ngườiNhân viên log giờ vào từng dự án với đơn giá giờ khác nhau. Cần chi phí mỗi dự án, mỗi người, và cảnh báo dự án vượt ngân sách.
- Bảng lương tháng: từ lương gộp ra thực nhậnCần một bảng lương chạy được cho cả trăm người: lương theo công, phụ cấp, bảo hiểm, thuế, tạm ứng. Sửa một tham số là cả bảng tính lại, không phải sờ từng dòng.