ZoneTechCách làm bảng chấm công trên Excel: ký hiệu, công chuẩn và giờ tăng ca
Cách làm bảng chấm công trên Excel gọn nhất là mỗi tháng một sheet, mỗi nhân viên một dòng, mỗi ngày một cột ghi ký hiệu, cuối dòng dùng COUNTIF đếm công. Nếu công ty có máy chấm công, thêm một sheet dán giờ vào, giờ ra để tính giờ làm, đi muộn và tăng ca.
Tóm tắt nhanh
- Thống nhất bảng ký hiệu trước: X đủ công, X/2 nửa công, P phép, L lễ, Ô ốm, KL không lương.
- Hàng ngày tháng tự sinh từ năm và tháng, tự ẩn ngày thừa ở tháng ngắn.
- Công chuẩn theo lịch tính bằng NETWORKDAYS.INTL, không gõ cố định 26.
- Giờ làm từ máy chấm công tính bằng giờ ra trừ giờ vào, xử lý được ca qua đêm.
- Giờ làm thêm có giới hạn theo Bộ luật Lao động, cần theo dõi lũy kế.
Bảng chấm công Excel gồm những phần nào?
Một bảng chấm công tháng có bốn vùng: tháng và công chuẩn ở đầu bảng, hàng ngày 1 đến 31 kèm thứ, thân bảng ghi ký hiệu từng ngày của từng người, cuối dòng là các cột đếm công. Trong sheet CHAM_CONG của file mẫu bản Free, công chuẩn ở ô C2, ngày ở hàng 3, thứ ở hàng 4, cột AJ là Công hưởng lương.
| Ký hiệu | Ý nghĩa | Tính lương |
|---|---|---|
| X | Đi làm đủ ngày | 1 công |
| X/2 | Làm nửa ngày | 0,5 công |
| P | Nghỉ phép năm | Hưởng lương, trừ phép |
| L | Nghỉ lễ, tết | Hưởng lương |
| Ô | Nghỉ ốm | Theo chế độ bảo hiểm, không tính lương công ty |
| KL | Nghỉ không lương | 0 công |
File mẫu bản Free có thêm NB (nghỉ bù), CT (công tác), H (học, họp), TS (thai sản), V (vắng không phép). Ghi bảng ký hiệu vào nội quy lao động hoặc quy chế lương để mọi người hiểu giống nhau.
Cách làm bảng chấm công trên Excel từng bước
Bố cục gợi ý khi tự dựng: hàng 3 chứa ngày đầy đủ (E3 đến AI3) và được ẩn, hàng 4 hiện số ngày, hàng 5 hiện thứ, nhân viên từ dòng 6. Giả sử ô B1 là năm, D1 là tháng.
Bước 1. Tự sinh ngày. Ở E3: =DATE($B$1,$D$1,1). Ở F3: =IF(E3="","",IF(MONTH(E3+1)=$D$1,E3+1,"")), kéo sang tới cột AI. Tháng 30 ngày tự để trống cột 31. Ở E4: =IF(E3="","",DAY(E3)), rồi ẩn hàng 3.
Bước 2. Hiện thứ. Ở E5: =IF(E3="","",CHOOSE(WEEKDAY(E3),"CN","T2","T3","T4","T5","T6","T7")).
Bước 3. Tô màu Chủ nhật và ngày lễ. Chọn vùng ô chấm công E6:AI105, Conditional Formatting, New Rule, Use a formula, nhập quy tắc tô vàng =AND(E$3>0,OR(COUNTIF(DS_LE,E$3)>0,WEEKDAY(E$3)=1,AND(WEEKDAY(E$3)=7,THAM_SO!$B$12="Nghỉ thứ Bảy và Chủ nhật"))), trong đó DS_LE là danh sách ngày lễ ở sheet tham số THAM_SO.
Bước 4. Ký hiệu chọn từ danh sách. Data Validation dạng List trỏ vào bảng ký hiệu, ví dụ vùng tên DS_KH. Không cho gõ tay để tránh "x" thường, "X " có dấu cách.
Bước 5. Công chuẩn theo lịch tháng. Công ty nghỉ Chủ nhật: =NETWORKDAYS.INTL(DATE($B$1,$D$1,1),EOMONTH(DATE($B$1,$D$1,1),0),"0000001"). Chuỗi bảy ký tự tính từ thứ Hai, số 1 là ngày nghỉ; nghỉ thứ Bảy và Chủ nhật thì đổi thành "0000011". Tháng 10/2026 có 31 ngày và 4 Chủ nhật nên công chuẩn là 27.
Bước 6. Đếm công hưởng lương. Cuối dòng 6: =COUNTIF(E6:AI6,"X")+COUNTIF(E6:AI6,"X/2")*0.5+COUNTIF(E6:AI6,"P")+COUNTIF(E6:AI6,"L"). Nếu dùng NB, CT, H thì cộng thêm. Bản Free có sẵn bốn cột Công hưởng lương, Ngày đi làm, Nghỉ phép, Nghỉ không lương (AJ đến AM) để đếm tay; dán công thức này vào AJ (đổi số dòng cho khớp) thì cột tự đếm.
Cần khung bảng để chấm ngay thì tải bản Free bảng chấm công dựng sẵn, miễn phí, không cần để lại thông tin.
Tính giờ làm, đi muộn, tăng ca từ máy chấm công
Máy chấm công thường xuất được ngày, mã nhân viên, giờ vào, giờ ra. Dán vào một sheet riêng, mỗi dòng một người một ngày: A Ngày, B Mã NV, D Giờ vào, E Giờ ra, F Nghỉ giữa ca (giờ). Thêm G Loại ngày, H Giờ làm, I Đi muộn, J Về sớm, L Giờ tăng ca, và đặt giờ vào ca ở một ô tên GIO_VAO_CA. File mẫu bản Free chưa có sheet này, bạn tự dựng theo bảng dưới.
| Cần tính | Công thức ở dòng 5 | Giải thích |
|---|---|---|
| Giờ làm | =(E5-D5+(E5<D5))*24-F5 |
Ca qua đêm (giờ ra nhỏ hơn giờ vào) được cộng thêm 1 ngày |
| Đi muộn (phút) | =MAX(0,ROUND((D5-GIO_VAO_CA)*1440,0)) |
1 ngày có 1.440 phút |
| Tăng ca ngày thường | =MAX(0,H5-8) |
H5 là giờ làm, 8 là giờ chuẩn mỗi ngày |
| Loại ngày | =IF(COUNTIF(DS_LE,A5),"Lễ",IF(WEEKDAY(A5)=1,"Nghỉ tuần","Thường")) |
Để nhân hệ số tăng ca đúng |
Tổng giờ tăng ca tháng của một người lấy bằng SUMIFS theo mã nhân viên, loại ngày và khoảng ngày. Giờ làm vào ngày nghỉ tuần, ngày lễ tính toàn bộ là giờ làm thêm.
Bộ luật Lao động 2019 giới hạn giờ làm thêm không quá 50% giờ làm bình thường trong ngày, không quá 40 giờ một tháng và 200 giờ một năm; một số ngành, nghề được tới 300 giờ một năm (LuatVietnam). Thêm cột lũy kế giờ tăng ca theo tháng, năm và tô đỏ khi vượt ngưỡng.
Ví dụ giả định: một tuần chấm công của nhân viên kho
Ví dụ giả định, ca bắt đầu 8:00, nghỉ trưa 1 giờ (Chủ nhật chỉ làm buổi sáng, không trừ nghỉ trưa), giờ chuẩn 8 giờ một ngày.
| Ngày | Giờ vào | Giờ ra | Giờ làm | Đi muộn | Tăng ca |
|---|---|---|---|---|---|
| T2 05/10 | 7:55 | 17:00 | 8,08 | 0 | 0,08 |
| T3 06/10 | 8:12 | 17:00 | 7,80 | 12 phút | 0 |
| T4 07/10 | 7:58 | 19:00 | 10,03 | 0 | 2,03 |
| CN 11/10 | 8:00 | 12:00 | 4,00 | 0 | 4,00 (ngày nghỉ tuần) |
Dòng thứ Hai cho thấy vấn đề: 5 phút đến sớm thành 0,08 giờ tăng ca. Nên quy định tăng ca phải được duyệt, làm tròn theo block 30 phút: =FLOOR(MAX(0,H5-8),0.5).
Lỗi hay gặp khi chấm công bằng Excel
- Gõ công chuẩn cố định 26 cho mọi tháng, lương ngày bị tính sai ở tháng có 27 ngày làm việc.
- Chèn hoặc xóa dòng nhân viên giữa tháng làm lệch công thức ở bảng lương. Thêm người mới ở cuối danh sách.
- Giờ từ máy chấm công xuất ra dạng chữ "08:12". Dùng
=TIMEVALUE(D5)để đổi sang giờ trước khi tính.
Checklist chốt công cuối tháng
- [ ] Ngày lễ của năm đã nhập đủ ở sheet tham số.
- [ ] Đơn nghỉ phép khớp số P trên bảng.
- [ ] Giờ tăng ca có phiếu duyệt, lũy kế chưa vượt giới hạn tháng, năm.
- [ ] Nhân viên xem và xác nhận bảng công trước khi chuyển sang tính lương.
- [ ] Khóa sheet tháng đã chốt.
Bước tiếp theo là đưa công và giờ tăng ca sang bảng lương, xem bài cách làm bảng lương trên Excel.
Tải bảng chấm công bản Free
File mẫu bản Free bộ nhân sự, chấm công và tiền lương gồm sheet NHAN_VIEN lưu hồ sơ, lương chính và CHAM_CONG cho một tháng. Bạn sửa tháng, thứ, công chuẩn ở đầu bảng, chọn ký hiệu cho từng ngày; cột Chủ nhật tô sẵn, sang tháng khác thì đổi tay. Cuối tháng bạn đếm tay bốn cột công theo bảng ký hiệu cuối sheet, chỉ dòng Cộng tự cộng bốn cột này. Bản Free chưa có sheet giờ vào, giờ ra và bảng lương. Muốn cột công tự đếm, áp dụng công thức COUNTIF ở bước 6 vào chính file. File không dùng macro.
Zonetech hỗ trợ gì?
Zonetech có ZoneKit, bộ 90 quy trình mẫu (SOP) cho 15 phòng ban, kèm bài chấm điểm vận hành 8 câu miễn phí. Bạn tự làm theo từng phòng ban hoặc làm cùng Zonetech trong 30 ngày. Gọi 0828.229.666 để nhận tư vấn.
Câu hỏi thường gặp
Bảng chấm công Excel có dùng được trên Google Sheets không?
Được. NETWORKDAYS.INTL, COUNTIF, Conditional Formatting đều chạy trên Google Sheets.
Công chuẩn tháng nên tính theo lịch hay cố định 26 ngày?
Tùy quy chế lương công ty. Theo lịch thì lương ngày đổi theo tháng; cố định thì dễ tính nhưng tháng nhiều ngày làm việc người đi đủ vẫn chỉ có 26 công.
Ngày lễ có cần chấm ký hiệu không?
Có, chấm L để tính vào công hưởng lương. Nếu đi làm ngày lễ, ghi giờ làm ở cột tăng ca để nhân đúng hệ số.
Nhân viên làm theo giờ chấm công thế nào?
Dùng sheet giờ vào, giờ ra. Tổng giờ làm trong tháng nhân với đơn giá giờ, không cần ký hiệu công.
Đọc tiếp: mẫu quản lý nhân sự bằng Excel.
Cập nhật lần cuối: 14/10/2026.