Hình dưới đây là bảng chấm công hoàn chỉnh: một bảng tính toán tổng số giờ làm việc mỗi ngày, mỗi tuần của nhân viên làm trong giờ hành chính cũng như ngoài giờ.
Bắt đầu từ việc nhập tiêu đề bảng. Từ dòng hai đến dòng tám chỉ có chữ mà thôi (không có công thức). Dĩ nhiên, bạn có thể định dạng chữ tùy thích. Nhập nội dung cho hộp “Tổng số giờ làm việc trong tuần”.
Bây giờ chuẩn bị nhập công thức. Vào ô B9 và nhập =D5. Công thức này sẽ lấy ngày bắt đầu trong tuần đã được nhập vào ô D5. Vào ô B10, nhập B9+1 rồi sao chép công thức này vào vùng B11:B15. Kế tiếp, nhập thứ trong tuần vào ô C9:C15. Bạn cần định dạng ngày trong cột C để hiển thị ngày rong tuần (Mon, Tue, Wed,…). Muốn thế, chọn ô C9:C15 và chọn Format. Cells. Vào khung hội thoại Format Cells, nhấn mục Number. Chọn Custom rồi gõ dddd trong khung type. Nhấn OK để đóng khung hội thoại này.
Trước khi nhập bất kỳ công thức nào thực hiện tính toán thực tế, bạn nên kiểm tra và giá trị thời gian mẫu trong vùng D9:G15. Nếu dùng AM hay PM, nhớ chừa một khoảng trắng trước đó.
Kế tiếp vào ô H9 rồi nhập công thức =IF(E9<D9, E9+1-D9,E9-D9)+IF(G9<F9,G9+1-G9,G9-F9)
Giải thích
Phần đầu của công thức tính tổng số giờ làm việc trong buổi sáng bằng cách lấy giờ trong cột E trừ đi giờ trong cột D. Phần thứ hai tính tổng số giờ làm việc trong buổi chiều bằng cách lấy giờ trong cột G trừ đi giờ trong cột F. Các hàm IF áp dụng cho trường hợp làm ca ba đến nửa đêm, ví dụ, một công nhân có thể bắt đầu làm việc từ lúc 10 giờ đêm và “ăn trưa” lúc 2 giờ sáng. Không có lệnh IF, kết quả công thức sẽ bị âm.
Chép công thức trong ô H9 vào H10:H15. Bây giờ bạn cần định dạng những ô này để hiển thị giờ giấc. Đánh dấu vùng H9:H15, chọn Format.Cells rồi nhấn mục Number. Chọn Time trong danh sách Catelogy. Chọn tùy chọn đầu tiên trong danh sách Type rồi nhấn OK.
Bước kế tiếp là tính tổng số giờ làm việc trong tuần. Vào ô H18, nhập SUM=(H9:H15). Công thức này tính tổng số giờ làm việc trong cột H. Bây giờ nhập vào ô H19 công thức =MIN(H18,1+(TIME(40,0,0)). Công thức này giả định rằng một tuần có 40 giờ làm việc, kết quả công thức này sẽ hoặc nhỏ hơn tổng số giờ đã làm việc hoặc nhỏ hơn 40 giờ. Công thức cuối cùng trong ô H20 là H18-H19. Công thức này tính số giờ làm thêm bằng cách lấy tổng số giờ làm việc thực tế trừ đi số giờ quy định. Nếu công tybạn quy định một tuần làm việc hơn 40 giờ, bạn phải đổi công thức trong ô H19.
Còn một bước quan trọng nữa: định dạng giờ trong vùng H18:H20. Vì những ô này có thể hiện giá trị giờ lớn hơn 24 giờ nên bạn cần tùy biến định dạng giờ. Chọn vùng H18:H20, chọn Format.Cells rồi nhấn mục Number. Chọn Custom từ danh sách Catelory, nhập [h]:mm vào khung Type rồi nhấn OK. Dùng dấu ngoặc vuông cho phần giờ để bảng tính hiển thị được giá trị lớn hơn 24 giờ
