Công ty có người làm giờ hành chính, có người làm ca xoay, có bảo vệ làm ca 12 tiếng qua đêm, có tạp vụ làm ca gãy 2 khúc. Cuối tháng, bộ phận nhân sự phải dò từng dòng dữ liệu máy chấm công để biết ai đi muộn, ai làm thêm, ai nghỉ phép nửa ngày. File Excel chấm công theo ca trong bài này làm phần việc đó thay bạn.

Bạn dán dữ liệu xuất từ máy chấm công, xếp ca cho từng ngày ở sheet Lịch phân ca và nhập đơn từ. File tự tính giờ vào, giờ ra, đi muộn, về sớm, tiền phạt, giờ làm thêm, giờ đêm, phép còn lại, rồi lên bảng công tháng có 24 cột tổng cho từng nhân viên.

File chỉ dùng hàm có trong Excel 2016 trở lên và không dùng macro. Một file chứa được tối đa 120 nhân viên, 12.000 lần chấm và 300 đơn mỗi tháng.

Tải file mẫu: File mẫu Excel - Chấm công theo ca từ máy chấm công (OneDrive)

Bạn dùng file mà bị vướng ở bước nào? Bạn có thể đăng câu hỏi trong nhóm Vườn ươm nhà Nghề để cùng trao đổi và được giải đáp. Vào nhóm hỏi đáp
Sheet Bảng công tháng có ký hiệu chấm công 31 ngày của 20 nhân viên, ô E6 đang chọn
Sheet Bảng công tháng cho bạn thấy công cả tháng của mọi nhân viên trên một màn hình

File chấm công theo ca này có những gì?

File có 10 sheet. Dữ liệu đang có là ví dụ của tháng 10/2026 tại một công ty bất động sản, gồm 20 nhân viên, 9 loại ca và 909 lần chấm. Tên công ty và tên nhân viên trong file đều là tên giả.

Sheet

Sheet này làm gì

Bạn nhập gì

Hướng dẫn

Sheet này tóm tắt cách dùng file, cách file tính công và giới hạn của file.

Bạn không cần nhập gì.

Bảng công tháng

Báo cáo chính gồm ký hiệu từng ngày và 24 cột tổng của từng nhân viên.

Bạn không cần nhập gì.

Thông số

Sheet này lưu tháng chấm công, mức làm thêm giờ, ngày lễ, loại đơn và bậc phạt.

Bạn đổi tháng chấm công mỗi tháng.

Danh mục ca

Sheet này lưu giờ vào, giờ ra, giờ nghỉ và quy tắc của từng ca.

Bạn nhập một lần khi bắt đầu dùng file.

Nhân viên

Sheet này lưu mã chấm công, họ tên, phòng ban và ca mặc định của từng người.

Bạn thêm người mới vào cuối danh sách.

Lịch phân ca

Sheet này xếp ca cho từng nhân viên vào từng ngày trong tháng.

Bạn nhập ca cho người làm ca xoay.

Dữ liệu máy chấm công

Sheet này chứa từng lần chấm của tháng.

Bạn dán dữ liệu xuất từ máy mỗi tháng.

Đơn từ

Sheet này ghi các ngày nghỉ phép, nghỉ ốm, nghỉ không lương, công tác, việc riêng.

Bạn nhập khi có đơn.

Quỹ phép

Sheet này theo dõi số ngày phép còn lại của từng người.

Bạn nhập phép tồn đầu năm và cập nhật mỗi tháng.

Công chi tiết

Sheet này tính công từng ngày của từng nhân viên, mỗi người 31 dòng.

Bạn không cần nhập gì.

Ở các sheet nhập liệu, ô trắng là ô bạn nhập, còn ô xám là ô có công thức. Bạn không nên gõ đè lên ô xám để file luôn tính đúng. Riêng cột Phép được hưởng ở sheet Quỹ phép là ô trắng có sẵn công thức, và bạn được gõ đè số vào cột này.

Sheet Hướng dẫn có 7 bước dùng mỗi tháng và phần đầu của mục cách file tính công
Sheet Hướng dẫn tóm tắt 7 bước bạn làm mỗi tháng

Bạn dùng file chấm công theo ca như thế nào?

Lần đầu dùng file, bạn khai báo thông số, danh mục ca, danh sách nhân viên và quỹ phép. Những tháng sau, bạn chỉ cần đổi tháng, xếp lịch ca, dán dữ liệu máy chấm công và nhập đơn từ.

Bước 1: Bạn chọn tháng và kiểm tra thông số

Ở sheet Thông số, bạn gõ một ngày bất kỳ của tháng cần chấm vào ô Tháng chấm công, ví dụ 01/11/2026 cho tháng 11. Ô này chỉ hiện tháng và năm, như 11/2026. File tự lấy ngày 1 của tháng đó làm ngày đầu kỳ, rồi tính ngày cuối kỳ và công chuẩn. Nếu máy của bạn dùng kiểu tháng/ngày/năm, bạn gõ ngày theo thứ tự của máy.

Sheet Thông số, ô công chuẩn B9 đang chọn, thanh công thức hiện =NETWORKDAYS(THANG,EOMONTH(THANG,0))
Công chuẩn khối hành chính bằng số ngày từ thứ 2 đến thứ 6 trong tháng, tháng 10/2026 có 22 ngày

Mục A. Thông số chung có các ô sau. File để sẵn giá trị thường gặp, bạn sửa theo quy định của công ty mình:

  • Số phút đi muộn, về sớm được bỏ qua: file để sẵn 5 phút. Nhân viên đến muộn 5 phút trở xuống vẫn được tính là đúng giờ.

  • Giờ bắt đầu và giờ kết thúc giờ đêm: file để sẵn 22:00 và 06:00.

  • Giờ tách ngày cho ca qua đêm: file để sẵn 12:00. Phần ca qua đêm ở dưới giải thích ô này.

  • Số phút tối thiểu từ lần chấm vào tới lần chấm ra: file để sẵn 30 phút. Lần chấm cuối cách lần chấm đầu chưa tới 30 phút thì file coi như nhân viên quên chấm.

  • Làm thêm sau giờ ra ca: nhân viên ở lại từ 30 phút trở lên mới được tính làm thêm, và giờ làm thêm được làm tròn xuống theo bội số 30 phút.

  • Hệ số làm thêm: file để sẵn 150% cho ngày thường, 200% cho ngày nghỉ và 300% cho ngày lễ.

  • Giờ nghỉ trừ khi làm ngày nghỉ, ngày lễ trên 6 tiếng: file để sẵn 1 giờ. Ca có giờ nghỉ riêng thì file trừ giờ nghỉ của ca thay cho ô này.

  • Số ngày phép năm mặc định: file để sẵn 12 ngày.

Mục B. Ngày lễ có sẵn một số ngày lễ của năm 2026 và ngày 20/10/2026 là ngày công ty cho nghỉ. Mỗi ngày lễ được ghi kèm năm, nên sang năm mới bạn phải nhập lại danh sách. Bạn bổ sung Tết Âm lịch, Giỗ Tổ, ngày nghỉ liền kề Quốc khánh và ngày nghỉ bù theo thông báo từng năm.

Mục C. Loại đơn từ có 5 loại đơn. Phép năm, công tác và việc riêng có lương được 1 công, còn nghỉ ốm và nghỉ không lương được 0 công.

Mục D. Bậc phạt đi muộn, về sớm quy định mức phạt theo số phút. Phần đi muộn ở dưới giải thích bảng này.

Các bảng cấu hình có giới hạn số dòng: danh mục ca chứa tối đa 15 ca, loại đơn tối đa 8 loại, bậc phạt tối đa 6 bậc và ngày lễ tối đa 20 ngày.

Bước 2: Bạn khai báo ca làm việc

Ở sheet Danh mục ca, mỗi dòng là một ca. File có sẵn 9 ca:

Mã ca

Tên ca

Giờ làm

Quy tắc riêng

HC

Hành chính

08:00 - 17:30

Nghỉ trưa 12:00 - 13:30 không tính công.

VIP

Ban lãnh đạo

08:00 - 17:30

Ca này không ghi nhận đi muộn, về sớm. Người có ca mặc định là VIP không được tính làm thêm. Ngày không chấm vẫn đủ công.

SA

Ca sáng

06:00 - 14:00

Ca này dành cho lễ tân, vận hành tòa nhà.

TO

Ca tối

14:00 - 22:00

Ca này dành cho lễ tân, vận hành tòa nhà.

DE

Ca đêm

22:00 - 06:00 hôm sau

Ca này kết thúc vào sáng hôm sau.

N12

Ca 12 tiếng ngày

06:00 - 18:00

Ca này được 1,5 công.

D12

Ca 12 tiếng đêm

18:00 - 06:00 hôm sau

Ca này được 1,5 công và kết thúc vào sáng hôm sau.

GAY

Ca gãy

07:00 - 20:00

Khoảng 11:00 - 16:00 không tính công, nhân viên phải chấm ra và vào ở giữa ca.

OFF

Nghỉ theo lịch

Không có

Mã này đánh dấu ngày nghỉ trong lịch phân ca.

Sheet Danh mục ca có 9 ca, ô G12 của ca gãy đang chọn, thanh công thức hiện công thức tính giờ công chuẩn
Ca gãy làm từ 07:00 tới 20:00 nhưng chỉ có 8 giờ công chuẩn, vì 5 giờ ở giữa không tính công

Bạn sửa giờ vào, giờ ra cho đúng công ty mình. Ca có khoảng nghỉ không tính công thì bạn nhập giờ nghỉ từ và giờ nghỉ đến. Ca không có khoảng nghỉ thì bạn để trống 2 ô này. Cột Giờ công chuẩn và cột Ca qua đêm tự tính từ giờ bạn nhập.

Ba cột Có hoặc Không quyết định cách tính của từng ca:

  • Ghi nhận đi muộn, về sớm, làm thêm: ca có chữ Không thì file không đếm phút đi muộn, về sớm và không tính làm thêm sau giờ ra ca.

  • Có ăn ca: ca có chữ Có thì mỗi ngày có chấm công được tính 1 ca ăn.

  • Không bắt buộc chấm công: ca có chữ Có thì ngày không chấm vẫn được đủ công.

Cột Số lần chấm yêu cầu cho biết ca cần bao nhiêu lần chấm. Ca gãy cần 4 lần: vào khúc 1, ra khúc 1, vào khúc 2, ra khúc 2.

Bước 3: Bạn khai báo nhân viên và xếp lịch phân ca

Ở sheet Nhân viên, mỗi người có mã chấm công, họ tên, phòng ban và ca mặc định. Mã chấm công phải trùng với mã trên máy, vì file dùng mã này để nối dữ liệu máy với nhân viên. Cột Ngày vào làm và Ngày nghỉ việc để trống nếu nhân viên làm cả tháng. Khi bạn nhập ngày, file đánh dấu các ngày ngoài thời gian làm việc bằng dấu - và không tính vắng những ngày này.

Bạn thêm người mới vào cuối danh sách, và không xóa dòng, chèn dòng hay sắp xếp sheet Nhân viên. Sheet Lịch phân ca, Quỹ phép và Bảng công tháng lấy nhân viên theo thứ tự dòng, còn lịch ca và số phép bạn nhập thì nằm cố định theo dòng. Khi bạn xóa một dòng, lịch ca và số phép của người này sẽ nhảy sang người khác. Người nghỉ việc thì bạn giữ nguyên dòng và nhập Ngày nghỉ việc.

Ở sheet Lịch phân ca, mỗi dòng là một nhân viên, mỗi cột từ 1 tới 31 là một ngày:

  • Người làm giờ cố định: bạn để trống cả dòng. File tự xếp ca mặc định từ thứ 2 đến thứ 6, còn thứ 7 và chủ nhật là ngày nghỉ.

  • Người làm ca xoay: bạn nhập mã ca cho từng ngày, ví dụ SA, TO, N12, D12, và nhập OFF cho ngày nghỉ.

  • Cột Ca ngày cuối tháng trước: bạn nhập ca của ngày cuối tháng trước cho người làm ca đêm. Phần ca qua đêm ở dưới giải thích vì sao file cần cột này.

Sheet Lịch phân ca, 13 nhân viên đầu để trống, 7 nhân viên cuối có mã ca từng ngày được tô màu theo ca
Nhân viên 1014 làm 3 ngày ca sáng, 3 ngày ca tối rồi nghỉ 1 ngày, nhân viên 1019 và 1020 làm ca 12 tiếng xoay vòng 4 ngày

Mỗi ô trong lịch có danh sách thả xuống lấy từ sheet Danh mục ca, nên bạn hạn chế được việc gõ sai mã ca. Khi dán lịch từ nơi khác vào, bạn kiểm tra lại các mã ca vì Excel không kiểm tra dữ liệu dán.

Bước 4: Bạn dán dữ liệu máy chấm công

Bạn xuất dữ liệu từ máy chấm công, xóa dữ liệu tháng trước ở 5 cột trắng rồi dán dữ liệu mới vào. Mỗi lần chấm nằm trên một dòng. File chỉ cần 3 cột: mã chấm công ở cột A, ngày ở cột C và giờ ở cột D. Cột B Họ tên trên máy và cột E Thiết bị chỉ để bạn đối chiếu, nên có thể để trống. Nếu máy của bạn xuất kiểu khác, bạn sắp lại dữ liệu cho đúng 3 cột này trước khi dán.

Sheet Dữ liệu máy chấm công, ô I6 đang chọn, lần chấm 06:02 ngày 01/10 của nhân viên 1020 có ngày công là 30/09
Cột Ngày công quyết định lần chấm thuộc về ngày nào

5 cột xám tự tính thời điểm chấm, ca của ngày hôm trước, ca đó có qua đêm không, ngày công và khóa tra cứu. Bạn nhớ xuất thêm dữ liệu buổi sáng ngày 1 của tháng sau, để ca đêm của ngày cuối tháng có giờ ra.

Bước 5: Bạn nhập đơn từ

Ở sheet Đơn từ, mỗi dòng là một đơn. Bạn nhập mã chấm công, chọn loại đơn, nhập từ ngày và đến ngày. Cột Họ tên tự hiện tên nhân viên để bạn kiểm tra mã đã đúng chưa. Gõ sai mã thì cột này báo Không có mã này.

Cột Buổi nghỉ dùng cho đơn nửa ngày. Bạn để trống hoặc chọn Cả ngày khi nghỉ trọn ngày. Bạn chọn Sáng hoặc Chiều khi nhân viên chỉ nghỉ một buổi, và buổi còn lại nhân viên vẫn phải đi làm.

Sheet Đơn từ có 9 đơn, ô F10 đang chọn là đơn nghỉ phép buổi sáng ngày 21/10 của nhân viên 1005
Đơn nửa ngày chọn Sáng hoặc Chiều ở cột Buổi nghỉ

Bước 6: Bạn cập nhật quỹ phép

Ở sheet Quỹ phép, cột Phép được hưởng trong năm tự lấy số ngày phép mặc định ở sheet Thông số. Người được nhiều phép hơn, ví dụ nhờ thâm niên, thì bạn gõ đè số vào ô của người đó. Đầu năm, bạn nhập Phép tồn năm trước. Mỗi tháng, bạn nhập tổng số ngày phép đã nghỉ từ đầu năm tới hết tháng trước vào cột Đã nghỉ trước tháng này.

File tự đếm số ngày nghỉ phép trong tháng từ sheet Công chi tiết. Ngày nghỉ phép nửa ngày được tính 0,5 ngày. Phép còn lại bằng phép được hưởng cộng phép tồn, trừ số ngày đã nghỉ trước tháng này và số ngày nghỉ trong tháng.

Sheet Quỹ phép, ô H9 của nhân viên 1005 đang chọn, phép còn lại là -0,5 và cột Cảnh báo báo Vượt phép
Nhân viên 1005 đã nghỉ đủ 12 ngày phép trước tháng 10, nên nửa ngày phép ngày 21/10 bị báo vượt phép

Bước 7: Bạn xem kết quả ở sheet Bảng công tháng

Mỗi dòng là một nhân viên, mỗi cột từ 1 tới 31 là một ngày. Dòng dưới số ngày ghi thứ của ngày đó, như T2, T3. Tháng có 30 ngày thì cột 31 tự để trống. Ô ký hiệu được tô màu theo nhóm: vắng và nghỉ không lương màu đỏ, đi muộn và quên chấm màu vàng, các đơn còn lại màu xanh dương, ngày lễ màu xanh lá, làm ngày nghỉ màu tím.

Ký hiệu

Ý nghĩa

Công

X

Nhân viên đủ công và đúng giờ.

Theo số công của ca

M, S, MS

Nhân viên đi muộn, về sớm, hoặc vừa đi muộn vừa về sớm quá số phút được bỏ qua.

Theo số công của ca. Công bị trừ theo bậc phạt nằm ở cột Trừ công

Q

Nhân viên chỉ có 1 lần chấm, hoặc lần chấm cuối cách lần đầu chưa tới số phút tối thiểu.

Theo số công của ca

V

Nhân viên vắng không có đơn.

0

P, Ô, KL, CT, R

Nhân viên có đơn phép năm, nghỉ ốm, nghỉ không lương, công tác hoặc việc riêng có lương cả ngày.

Theo loại đơn

P/2, Ô/2, ...

Nhân viên có đơn nghỉ nửa ngày.

Nửa công theo loại đơn, cộng nửa công nếu buổi còn lại có chấm công

L

Ngày đó là ngày lễ.

1, ca 12 tiếng được 1,5

*

Nhân viên có chấm công vào ngày nghỉ theo lịch.

0, giờ làm được tính làm thêm ngày nghỉ

Ô trống

Ngày đó là ngày nghỉ theo lịch.

0

-

Ngày nằm ngoài thời gian làm việc của nhân viên.

0

Bên phải bảng có 24 cột tổng: công chuẩn, công thực tế, trừ công đi muộn, công tính lương, phép, ốm, không lương, công tác, việc riêng có lương, vắng không đơn, lễ, số lần và số phút đi muộn, số lần và số phút về sớm, quên chấm, tiền phạt, giờ làm thêm ngày thường, ngày nghỉ, ngày lễ, giờ làm thêm quy đổi, giờ đêm, số ca ăn và phép còn lại.

Phần cột tổng của Bảng công tháng, ô AM6 công tính lương của nhân viên 1001 đang chọn
Công tính lương bằng công thực tế trừ đi số công bị trừ vì đi muộn, về sớm

Bạn dùng các cột tổng này để đưa sang bảng lương. Trong ví dụ:

  • Nhân viên 1001 có công chuẩn 22, công thực tế 21,5 vì nghỉ phép buổi sáng ngày 30/10 nhưng buổi chiều không đi làm. Nhân viên này có 4,5 giờ làm thêm ngày thường, quy đổi thành 6,75 giờ.

  • Nhân viên 1002 bị trừ 1,5 công vì có những lần đi muộn hoặc về sớm từ 31 phút trở lên, nên công tính lương còn 20,5.

  • Nhân viên 1006 đi làm thêm thứ 7 ngày 10/10 và chủ nhật ngày 25/10, có 11 giờ làm thêm ngày nghỉ, quy đổi thành 22 giờ.

  • Nhân viên 1020 làm ca 12 tiếng đêm vào ngày 20/10 là ngày công ty cho nghỉ, có 11 giờ làm thêm ngày lễ. Nhân viên này có 55,35 giờ đêm, gồm cả 8 giờ đêm của ca ngày lễ.

File tính công từng ngày như thế nào?

Sheet Công chi tiết là nơi file làm toàn bộ phép tính. Mỗi nhân viên có 31 dòng, mỗi dòng là một ngày. Sheet Bảng công tháng và sheet Quỹ phép chỉ lấy kết quả từ sheet này.

Sheet Công chi tiết của nhân viên 1001, ô R11 đang chọn, thanh công thức hiện công thức tính giờ làm thêm
Ngày 7/10 nhân viên 1001 ra lúc 19:15, ở lại 105 phút sau giờ ra ca nên được 1,5 giờ làm thêm

Với mỗi ngày, file làm theo thứ tự sau:

  1. File lấy ca của ngày từ sheet Lịch phân ca, rồi xác định loại ngày là ngày làm việc, ngày nghỉ, ngày lễ hay ngày ngoài thời gian làm việc.

  2. File đếm số lần chấm của nhân viên trong ngày bằng hàm COUNTIF.

  3. File lấy giờ vào là lần chấm sớm nhất và giờ ra là lần chấm muộn nhất, bằng hàm AGGREGATE. Nhân viên bấm máy 2, 3 lần liền nhau cũng không làm sai kết quả.

  4. File tìm đơn từ có khoảng ngày chứa ngày đang xét, và lấy buổi nghỉ của đơn đó.

  5. File tính giờ vào chuẩn và giờ ra chuẩn. Ngày nghỉ buổi sáng thì giờ vào chuẩn dời tới giờ hết nghỉ trưa, ngày nghỉ buổi chiều thì giờ ra chuẩn dời về giờ bắt đầu nghỉ trưa.

  6. File so giờ vào, giờ ra với giờ chuẩn để ra số phút đi muộn, về sớm, rồi tra bậc phạt.

  7. File tính giờ làm, giờ làm thêm, giờ đêm, rồi chọn ký hiệu, tính công và ca ăn của ngày.

Ca qua đêm được tính như thế nào?

Ca 12 tiếng đêm bắt đầu lúc 18:00 và kết thúc lúc 06:00 sáng hôm sau. Lần chấm ra lúc 06:00 nằm ở ngày hôm sau, nên file cần biết lần chấm đó thuộc ngày nào.

Công thức ở cột Ngày công của sheet Dữ liệu máy chấm công là:

=IF(F5="","",IF(AND(H5="Có",MOD(F5,1)<GIO_TACH),INT(F5)-1,INT(F5)))

Cột H cho biết ca của ngày hôm trước có qua đêm hay không. Nếu có, và giờ chấm nằm trước mốc GIO_TACH là 12:00 trưa, file tính lần chấm cho ngày hôm trước. Hàm MOD lấy phần giờ của thời điểm chấm, còn hàm INT lấy phần ngày.

Ví dụ, nhân viên 1020 vào ca D12 lúc 17:55 ngày 20/10 và ra lúc 06:12 ngày 21/10. Ngày 20/10 nhân viên này làm ca đêm, nên lần chấm 06:12 được ghép về ngày 20/10.

Lần chấm 06:02 sáng ngày 01/10 của nhân viên 1020 là giờ ra của ca đêm ngày 30/09. Ở sheet Lịch phân ca, cột Ca ngày cuối tháng trước của nhân viên này ghi D12, nên file đưa lần chấm đó về ngày 30/09 và không tính vào tháng 10.

Ca gãy được tính như thế nào?

Ca gãy làm 2 khúc, từ 07:00 tới 11:00 và từ 16:00 tới 20:00. Ở sheet Danh mục ca, khoảng 11:00 - 16:00 được nhập là giờ nghỉ không tính công. File lấy giờ vào là lần chấm đầu tiên và giờ ra là lần chấm cuối cùng, rồi trừ phần nằm trong giờ nghỉ.

Ca gãy cần 4 lần chấm, trong đó lần ra khúc 1 và lần vào khúc 2 nằm quanh giờ nghỉ giữa ca. File đếm số lần chấm từ 1 giờ trước khi nghỉ tới 1 giờ sau khi hết nghỉ, tức là từ 10:00 tới 17:00. Nếu khoảng này có ít hơn 2 lần chấm, cột Ghi chú báo Ca gãy thiếu lượt chấm giữa ca để bạn hỏi lại nhân viên. Nhân viên bấm trùng lúc vào ca hay lúc ra ca cũng không che được cảnh báo này.

File chỉ ghi chú, không trừ công ngày thiếu lượt chấm giữa ca. Ngày đó vẫn có ký hiệu X, được 1 công và 8 giờ làm. Nếu công ty bạn trừ công trong trường hợp này, bạn sửa ở bảng lương.

Để xem riêng một nhân viên, bạn bấm vào mũi tên lọc ở ô tiêu đề Mã chấm công của sheet Công chi tiết, bỏ chọn Select All, chọn mã cần xem rồi bấm OK. Ảnh dưới đây lọc nhân viên 1017 làm ca gãy.

Sheet Công chi tiết đã lọc nhân viên 1017 làm ca gãy, ô AA513 của ngày 13/10 đang chọn, cột Ghi chú báo Ca gãy thiếu lượt chấm giữa ca
Ngày 13/10 nhân viên 1017 chỉ chấm 2 lần lúc 06:55 và 20:12, nên file báo thiếu lượt chấm giữa ca

Làm thêm giờ được tính như thế nào?

File tính 3 loại làm thêm giờ, mỗi loại có hệ số riêng ở sheet Thông số.

Làm thêm ngày thường là phần thời gian nhân viên ở lại sau giờ ra ca. File chỉ tính khi nhân viên ở lại từ 30 phút trở lên, và làm tròn xuống theo bội số 30 phút. Ví dụ, ngày 7/10 nhân viên 1001 ra lúc 19:15, tức là ở lại 105 phút sau 17:30. Số phút này được làm tròn xuống còn 90 phút, nên nhân viên được 1,5 giờ làm thêm. Phần đi làm sớm hơn giờ vào ca chưa được tính làm thêm.

Làm thêm ngày nghỉ là toàn bộ thời gian nhân viên làm vào ngày nghỉ theo lịch. Nếu nhân viên làm trên 6 tiếng, file trừ 1 giờ nghỉ. Ví dụ, nhân viên 1006 làm chủ nhật ngày 25/10 từ 08:00 tới 17:00, tức là 9 tiếng. File trừ 1 giờ nghỉ, nên nhân viên được 8 giờ làm thêm ngày nghỉ. Giờ làm thêm ngày nghỉ cũng được làm tròn xuống theo bội số 30 phút, nhưng không cần đủ 30 phút tối thiểu. Ngày 10/10 nhân viên 1006 làm từ 08:30 tới 11:45, tức là 3 giờ 15 phút, nên được 3 giờ làm thêm.

Làm thêm ngày lễ được tính giống làm thêm ngày nghỉ, cộng với 1 công ngày lễ. Nếu ca của ngày lễ có giờ nghỉ riêng, như nghỉ trưa của ca hành chính hay giờ nghỉ giữa ca gãy, file trừ phần thời gian nằm trong giờ nghỉ đó thay cho 1 giờ nghỉ. Ví dụ, nhân viên 1017 làm ca gãy ngày 20/10 từ 06:45 tới 20:00. File trừ 5 giờ nghỉ giữa ca, rồi làm tròn xuống còn 8 giờ làm thêm ngày lễ. Tương tự, nhân viên hành chính làm ngày lễ từ 08:00 tới 17:30 được 8 giờ làm thêm, vì file trừ 1,5 giờ nghỉ trưa.

Cột Giờ làm thêm quy đổi ở sheet Bảng công tháng bằng giờ làm thêm ngày thường nhân 150%, cộng giờ làm thêm ngày nghỉ nhân 200%, cộng giờ làm thêm ngày lễ nhân 300%. Bạn nhân cột này với đơn giá một giờ công để ra tiền làm thêm.

Ngày có đơn từ không được tính làm thêm sau giờ ra ca. Người có ca mặc định là VIP không được tính làm thêm vào bất kỳ ngày nào. File cũng chưa có bước duyệt làm thêm. Giờ làm thêm nào không được duyệt thì bạn sửa ở bảng lương.

Nghỉ nửa ngày được tính công như thế nào?

Đơn nghỉ nửa ngày được nửa công theo loại đơn, cộng nửa công đi làm nếu buổi còn lại có chấm công. Giờ vào chuẩn và giờ ra chuẩn của buổi còn lại cũng được dời theo buổi nghỉ:

  • Nghỉ buổi sáng: nhân viên 1005 nghỉ phép sáng ngày 21/10 và vào làm lúc 13:34. Giờ vào chuẩn là 13:30, tức là giờ hết nghỉ trưa, nên nhân viên đến muộn 4 phút và vẫn nằm trong mức bỏ qua. Ngày này có ký hiệu P/2 và được 1 công.

  • Nghỉ buổi chiều: nhân viên 1008 nghỉ phép chiều ngày 28/10, làm từ 08:00 tới 12:00. Giờ ra chuẩn là 12:00, tức là giờ bắt đầu nghỉ trưa, nên nhân viên không bị tính về sớm. Ngày này cũng được 1 công.

  • Nghỉ một buổi và vắng buổi còn lại: nhân viên 1001 nghỉ phép sáng ngày 30/10 nhưng buổi chiều không chấm công. Ngày này chỉ được 0,5 công, cột Vắng không đơn ở sheet Bảng công tháng cộng 0,5 ngày, còn cột Ghi chú báo Vắng nửa ngày còn lại.

Ca không có nghỉ trưa, như ca sáng hay ca 12 tiếng, thì file lấy thời điểm giữa ca để chia buổi sáng và buổi chiều.

Đi muộn, về sớm bị phạt như thế nào?

Ở mục D của sheet Thông số, mỗi bậc phạt có số phút bắt đầu, tiền phạt và số công bị trừ:

Từ số phút

Tiền phạt

Trừ công

0

0

0

6

50.000

0

16

100.000

0

31

0

0,5

Bảng bậc phạt đi muộn, về sớm ở mục D của sheet Thông số, ô B39 đang chọn
Mỗi lần đi muộn và mỗi lần về sớm được tra bậc phạt riêng

File tra bậc phạt cho từng lần đi muộn và từng lần về sớm. Công thức tra bậc dùng hàm LOOKUP:

LOOKUP(2,1/((PHAT_TU<>"")*(PHAT_TU<=N(O5))),PHAT_TIEN)

Phần 1/((PHAT_TU<>"")*(PHAT_TU<=N(O5))) cho ra 1 ở các bậc có số phút bắt đầu nhỏ hơn hoặc bằng số phút đi muộn, và báo lỗi ở các bậc còn lại. Hàm LOOKUP tìm số 2 không có trong dãy, nên dừng ở số 1 cuối cùng và lấy tiền phạt của bậc cao nhất mà số phút đi muộn đạt tới.

Trong ví dụ:

  • Ngày 1/10 nhân viên 1002 vào lúc 08:18, tức là đi muộn 18 phút. Lần đi muộn này rơi vào bậc 16 phút, nên nhân viên bị phạt 100.000 đồng.

  • Ngày 2/10 nhân viên 1002 ra lúc 16:55, tức là về sớm 35 phút. Lần về sớm này rơi vào bậc 31 phút, nên nhân viên không bị phạt tiền mà bị trừ 0,5 công.

Bạn sửa bảng này theo quy định của công ty. Bạn giữ bậc đầu tiên là 0 và xếp các bậc theo số phút tăng dần. Ca VIP không bị phạt, vì ca này không ghi nhận đi muộn, về sớm. Công bị trừ không làm giảm cột Công của ngày đó, mà được cộng ở cột Trừ công rồi trừ vào cột Công tính lương ở sheet Bảng công tháng.

Bạn kiểm tra những ngày bất thường như thế nào?

Cột cuối của sheet Công chi tiết là Ghi chú. Cột này báo các trường hợp cần kiểm tra:

  • Thiếu lượt chấm vào hoặc ra: ghi chú này hiện ở ngày có ký hiệu Q.

  • Ca gãy thiếu lượt chấm giữa ca: ghi chú này hiện khi nhân viên làm ca gãy không chấm ra và vào quanh giờ nghỉ giữa ca.

  • Làm ngày nghỉ, tính làm thêm: ghi chú này hiện khi nhân viên có chấm công vào ngày nghỉ theo lịch.

  • Làm ngày nghỉ nhưng thiếu lượt chấm: ghi chú này hiện khi nhân viên chỉ chấm 1 lần vào ngày nghỉ, nên file chưa tính được giờ làm thêm.

  • Có chấm công ngày lễ, tính làm thêm: ghi chú này hiện khi nhân viên đi làm vào ngày lễ.

  • Vắng nửa ngày còn lại: ghi chú này hiện khi nhân viên có đơn nửa ngày nhưng buổi còn lại không chấm công.

  • Có đơn nhưng vẫn chấm công: ghi chú này hiện khi nhân viên có đơn nghỉ cả ngày hoặc công tác nhưng máy vẫn ghi nhận lần chấm.

  • Có chấm công ngoài thời gian làm việc: ghi chú này hiện khi nhân viên có chấm công trước ngày vào làm hoặc sau ngày nghỉ việc.

  • Nhân viên chưa có ca: ghi chú này hiện khi nhân viên chưa được gán ca mặc định ở sheet Nhân viên.

Để lọc nhanh các ngày này, bạn bấm mũi tên lọc ở ô tiêu đề Ghi chú, bỏ chọn dòng (Blanks) rồi bấm OK.

Sheet Công chi tiết đã lọc cột Ghi chú, chỉ còn các ngày cần kiểm tra
Bạn xem lại từng ngày cần kiểm tra trước khi chốt công

Sau khi kiểm tra, bạn bấm nút Clear trong nhóm Sort & Filter của tab Data để hiện lại toàn bộ dòng.

File chấm công này chưa làm được gì?

  • File chưa tính lương. Bạn dùng các cột tổng ở sheet Bảng công tháng để đưa sang bảng lương.

  • File chưa tính làm thêm trước giờ vào ca, và chưa có bước duyệt làm thêm.

  • Mỗi ngày, mỗi nhân viên chỉ làm một ca. Nhân viên làm 2 ca trong cùng một ngày thì bạn cần tính tay ngày đó.

  • Với ca không qua đêm, lần chấm ra sau 0 giờ, ví dụ ca tối ra lúc 00:30, bị tính sang ngày hôm sau.

  • Phép năm chưa tự cộng dồn theo tháng làm việc, và số ngày đã nghỉ của các tháng trước do bạn nhập tay.

  • File chưa cộng giờ đêm của ngày nghỉ theo lịch. Giờ đêm của ngày làm việc và ngày lễ thì vẫn được cộng.

  • File không tự sửa lịch ca và số phép khi bạn xóa hoặc sắp xếp dòng ở sheet Nhân viên, nên bạn chỉ thêm người mới vào cuối danh sách.

Nếu bạn cần file tính lương từ bảng công, bạn có thể tham khảo bài mẫu bảng lương Excel tính thuế TNCN, BHXH. Nếu công ty bạn cần xếp lịch ca trước khi chấm công, bạn có thể dùng file Excel lịch làm việc theo ca.

Bạn muốn học Excel bài bản hơn?

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.

Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.