Với nội dung này, mình sẽ giới thiệu và hướng dẫn các bạn cách tạo bảng chấm công trên Excel một cách đơn giản và hiệu quả, rất hữu ích cho kế toán, HCNS

Bạn đang xem: hàm tính ngày công trong excel

Tạo bảng chấm công bình Excel giúp tất cả chúng ta chuyên nghiệp hóa được công việc quản trị nhân lực. Đồng thời hệ thống này cũng giúp lưu trữ tôt và hoạt động trơn tru hơn so với truyền thống. Học Excel Online sẽ hướng dẫn bạn tạo bảng chấm công bình Excel vô cùng đơn giản

Trước khi lập 1 file excel, chúng ta nên tưởng tượng, hình dung ra trước xem file bảng chấm công gồm những gì, bao nhiêu sheet, nội dung như vậy nào, cách hoạt động ra sao…? Khi trả lời được các thắc mắc thì bạn càng biết rõ cách làm trên excel để có kết quả như ý muốn.
Tôi sẽ hướng dẫn cách lập 1 mô hình bảng chấm công ví dụ như sau:

Về mô hình là 13 sheet, nhưng khi khởi đầu làm ta chỉ cần 2 sheet:

+ 1 sheet là Danh sách nhân viên (DSNV)

+ 1 sheet là Tháng 1 (làm hoàn chỉnh cho 1 tháng, các tháng sau có thể sao chép sheet này rồi đổi tên)

Sheet Danh sách nhân viên:

Nội dung đa số trong sheet này là Tên và Mã nhân viên. Cần tạo mã nhân viên vì có thể có trường hợp trùng tên. Khi quản lý theo mã sẽ tránh trường hợp bị trùng.

Ngoài ra còn tồn tại các nội dung khác liên quan tới nhân viên, như: ngày sinh, quê quán, số CMT, ngày vào làm…
Bạn có thể tạo như sau:

+ Cột E (ngày sinh), bạn bôi đen cả cột (mang con trỏ chuột vào vị trí trên cùng của cột – chữ E, bấm chọn cả cột), bạn định dạng Format cell/Number/Custome/ chọn dd-mm-yyyy . Tác dụng: khi bạn nhập bất kỳ dữ liệu kiểu ngày tháng vào cột này sẽ tự động mang về dạng dd-mm-yyyy (01-01-2014)

Sheet danh sách nhân viên hầu như là nhập bằng tay nên không có gì phức tạp.
Lưu ý: bạn chừa ra khoảng 2-3 dòng trên cùng để tạo link tới các sheet khác. Bên trái cách ra 1 cột để dự trữ nếu cần bổ sung gì thêm.

Sheet Tháng 1

Trước tiên bạn tạo khung cho bảng chấm công, gồm các nội dung: tiêu đề – bảng chấm công, tháng, phòng ban chấm công, định mức ngày công trong tháng, các cột gồm: mã nhân viên, tên nhân viên, ngày trong tháng (31 cột tương ứng với 31 ngày – số ngày lớn nhất của 1 tháng), 4-5 cột tính quy ra công, 1 cột để ghi chú

Tiếp sau đó bạn co độ rộng của các cột sao cho gọn và dễ nhìn. Các cột ngày trong tháng có thể co lại nhỏ vừa đủ để chấm công thôi. Cột quy ra công cũng không cần rộng lắm. Đa số cột tên và mã nhân viên hiển thị đủ là được.

Cách co độ rộng: bạn bôi đen các cột ngày trong tháng, quy ra công (trong ví dụ là bôi đen từ cột E đến cột AM), sau đó co độ rộng ở cột E khoảng 30 pixels (hoặc bôi đen từ cột E đến cột AM, sau đó bấm chọn command Column Width, chọn 3,13 tương ứng 30 pixels)

Vậy là bạn đã làm xong phần khung của Bảng chấm công. Tiếp theo ta sẽ thực hiện thao tác chọn ngày tháng của bảng chấm công.

Tạo ngày tháng trong bảng chấm công

– Trước tiên ta xác nhận năm sử dụng. Tại ô D1 nhập giá trị của năm. Ví dụ năm 2014

– Tại ô B4 (xác nhận tháng chấm công), ta nhập hàm xác nhận ngày: =date($𝓓$1;1;1)
Lưu ý dấu ngăn cách các thành phần trong hàm có thể là dấu ; hoặc dấu , tùy thiết lập của PC. Ở PC của tôi sử dụng dấu ;
Nội dung hàm date: xác nhận giá trị của ngày tháng theo giá trị xác nhận trong hàm. giá trị theo thứ tự là Năm, tháng, ngày. Ở hàm trên là Năm = giá trị tại ô D1, Tháng = 1, Ngày = 1

– Sau khoảng thời gian nhập hàm xong, tại ô B4, chọn format cell / custom / nhập giá trị [“tháng “mm” năm “yyyy] vào ô Type bên phải, xong rồi ấn ok (dấu nháy kép + tháng + dấu cách + dấu nháy kép + mm + dấu nháy kép + năm + dấu cách + dấu nháy kép + yyyy)

Xem thêm: Định dạng ngày tháng trong Excel

– Bạn có thể marge cell từ ô B4 đến D4 để cho hiển thị đủ nội dung cho dễ nhìn.

– Tại ô ngày 1 ( ô E9), bạn nhập =b4 để xác nhận ngày trước nhất trong tháng.

– Tại ô F9, bạn nhập =e9+1 (ngày tiếp theo trong tháng)

– Sao chép công thức tại ô F9 sang các ô bên cạnh, cho đến ô ngày thứ 31 (ô AI9) bằng cách sau:

  • Cách 1: Bôi đen từ ô F9 đến ô AI9, bấm Ctrl + Ŕ
  • Cách 2: Nhấp chuột vào ô F9, giữ chuột tại vị trí dấu chấm đen trong ô để con trỏ chuột trở thành dấu +, kéo chuột tới ô AI9 rồi thả ra

– Bôi đen từ ô E9 đến ô AI9, chọn Format cells / custom / mục Type bạn gõ chữ dd rồi bấm ok (chỉ hiện thị số ngày)
Nội dung trong ô E9 đến ô AI9 sẽ hiển thị ra ngày như trong bảng trên.

– Tại ô E10 nhập hàm =CHOOSE(WEEKDAY(E9);”Chủ nhật”;”Ƭ. hai”;”Ƭ. ba”;”Ƭ. tư”;”Ƭ. năm”;”Ƭ. sáu”;”Ƭ. bảy”)

Nội dung hàm:

  • Weekday(e9) : là lấy giá trị ngày trong tuần của ô E9. Nếu nội dung trong hàm weekday không có đặt quy định về thứ tự ngày trong tuần thì sẽ tự trả về theo thứ tự Chủ nhật, thứ hai, thứ 3… thứ 7 (Khởi đầu là chủ nhật, kết thúc là thứ 7), và trả về giá trị số từ 1 đến 8
  • Choose: là hàm chọn giá trị trả về. Giá trị trước nhất trong hàm là giá trị được chọn để làm căn cứ xác nhận. Các giá trị tiếp theo là nội dung được trả về tương ứng theo giá trị trước nhất.

Trong hàm trên, ngày 01/01/2014 khi dùng hàm weekday sẽ trả về giá trị là 4, khi dùng hàm choose với thứ tự tương ứng là Chủ nhật, thứ hai, thứ ba… thứ bảy thì giá trị 4 sẽ tương ứng với thứ tư. Bởi vì hàm weekday không tự trả về thứ theo tiếng việt, nên ta phải kết phù hợp với hàm choose để có được nội dung là thứ mấy trong tuần

– Sao chép công thức tại ô E10 sang các ô tiếp theo bên phải, cho tới ô AI10 (ngày thứ 31)

Tuy nhiên không phải tháng nào cũng có 31 ngày, nên để tránh việc hiển thị các ngày của tháng khác vào tháng này, ta thực hiện các thao tác sau:
+ Khởi nguồn từ ngày thứ 29 (ô AG9), ta điều chỉnh về hàm như sau: =IF(DAY(AF9+1)=DAY(E9);””;AF9+1)
Nội dung hàm: Nếu giá trị ngày của ô AF9 +1 bằng giá trị ngày của ô E9 sẽ trả về giá trị là rỗng, nếu không bằng thì sẽ trả về giá trị ô AF9 + 1 (Nghĩa là nếu tháng 2 có 28 ngày, thì ngày thứ 29 sẽ là ngày 01/03, vậy giá trị ngày là 1, bằng với giá trị ngày của ô E9, nếu đúng như vậy thì sẽ là rỗng, không hiển thị ngày 01/03. Còn nếu tháng 2 đó có 29 ngày thì sẽ hiện ngày 29)

+ Tại ô AH9, ta dùng hàm sau: =IF(AG9=””;””;if(DAY(AG9+1)=DAY(E9);””;AG9+1))
Nội dung hàm:

  • IF thứ 1: Nếu tại ô AG9 là giá trị rỗng, thì ô AH9 cũng sẽ có giá trị rỗng (tức là vào trường hợp tháng 2 có 28 ngày thì sẽ không có ngày thứ 30)
  • IF thứ 2: tương tự với hàm if tại ô AG9, tức là trường hợp tháng 2 có 29 ngày, thì sẽ không hiển thị ngày 30.

+ Tại ô AI9, ta dùng hàm sau: =IF(AH9=””;””;if(DAY(AH9+1)=DAY(E9);””;AH9+1))

Nội dung hàm:

  • IF thứ 1: Nếu AH9 là giá trị rỗng, thì AI9 cũng sẽ có giá trị rỗng (tức là trường hợp không có ngày thứ 30 thì cũng không có ngày thứ 31)
  • IF thứ 2: trường hợp tháng chỉ có 30 ngày thì sẽ không có ngày thứ 31

+ Khi các ô AG9, AH9, AI9 là rỗng thì hàm choose ở các ô AG10, AH10, AI10 sẽ phát sinh lỗi. Nhưng không tác động gì đến bảng tính nên không cần lo ngại về việc này.

Như vậy ta đã thực hiện được phần các ngày trong tháng, và các thứ trong tuần.

Tiếp theo là việc tự động đổi màu cho các ngày thứ 7, chủ nhật cho khác với ngày thường

+ Bôi đen nội dung trong bảng (Từ E9 đến AI30 – tức là toàn bộ các nội dung về ngày trong tháng lẫn phần chấm công của các nhân viên), chọn tính năng Conditional Formatting (định dạng theo điều kiện), trong mục Condittional Formatting, chọn New Rule.

Trong bảng New Formatting Rule, bạn chọn dòng cuối cùng trong mục Select α Rule Type là Use α formula to determine which cells to format (Dùng công thức để mô tả cách định dạng cells)

+ Tại mục Format values where this formula is true, ta nhập hàm

=if(weekday(e$9)=1;true;false)

Nội dung hàm: Nếu ngày trong tuần tại ô e9 là 1 (tức là chủ nhật) thì là đúng, không thì sẽ sai. Nếu dúng thì sẽ ứng dụng định dạng của Conditional formatting. Đặt dấu cố định e$9 là cố định hàng 9, tính từ cột e.

+ Sau khoảng thời gian đặt hàm, ta thiết lập định dạng cho CF. Trong bảng định dạng, bạn có thể chọn font chữ, màu chữ tại Font, màu nền tại Fill, kẻ đường viền ô tại Border… (giống định dạng ô của Format cells thông thường). Chọn định dạng xong bấm ok.

Kết quả khi thực hiện Conditional formatting với điều kiện ngày chủ nhật.

+ Làm tương tự với định dạng ngày thứ 7:

Bôi đen vùng trong bảng, chọn tính năng Conditional Formatting/ New rule/ Use α formulas…, nhập hàm =if(weekday(e$9)=7;true;false) , chọn kiểu định dạng cho ngày thứ 7

Kết quả khi thực hiện định dạng thứ 7 và chủ nhật

+ Thiết lập định dạng cho các cột không chứa giá trị ngày (ví dụ tháng 2 có 28 ngày, tự động bôi đen các ngày trống để phân biệt)

Bước 1: Tại ô B4, sửa tạm giá trị tháng là 2 để làm thử

Bước 2: Bôi đen vùng trong bảng, chọn tính năng Conditional formatting / new rule / use α formulas…, nhập hàm =if(e$9=””;true;false), chọn định dạng là tô màu xám cho chữ và nền, ta có kết quả như sau:

Khi làm xong nhớ trả lại giá trị tháng 1 ở ô B4 nhé.

Phù, vậy là xong phần thiết lập ngày tháng cho bảng chấm công. Giờ còn khâu cuối cùng là đặt ký hiệu chấm công nữa là xong.

Đặt ký hiệu chấm công

+ Bạn chọn 1 vài ký hiệu chấm công cho 1 số loại công như sau:

  • Ngày công thực tiễn (đi làm đủ số công):
  • Nửa ngày công (làm nửa ca, nửa ngày…)
  • Ngày nghỉ hưởng nguyên lương (đi học, họp, phép… nghỉ mà có lương)
  • Nghỉ không lương

…. (tùy nhà cung cấp mà chọn các hình thức chấm công khác nhau)

Lưu ý mỗi kí hiệu chấm công sẽ tương ứng với 1 cột quy ra công (hoặc có thể gộp), ngoài ra thêm 1 cột tính tổng số công

Tại ví dụ tôi đặt như sau:

  • Ngày công thực tiễn: Ҳ
  • Nửa ngày công : 𝒱
  • Ngày nghỉ hưởng nguyên lương: ᴘ
  • Nghỉ không lương: ₭
  • Ốm đau, thai sản: Σ

Hàm, công thức tính công

Tại cột Ngày công thực tiễn (cột AJ), tại ô AJ11, đặt hàm sau:

=COUNTIF($E11:$AI11;$₲$34)

Nội dung hàm: Đếm số lần xuất hiện của giá trị tại ô G34, trong vùng E11 đến AI11. Giá trị tại ô G34 là ký hiệu chấm công của ngày công đủ, vùng E11 đến AI11 là số ngày công trong tháng của người trước nhất (hàng 11), cố định cột E và AI để khi sao chép công thức không bị tác động tới vùng chấm công

Tương tự với các cột khác, ta đặt công thức:

  • Ô AK11 (Nửa công) = countif($E11:$AI11;$₲$35)
  • Ô AL11 (Nghỉ hưởng lương) = countif($E11:$AI11;$₲$36)
  • Ô AM11 (nghỉ không lương) = countif($E11:$AI11;$₲$37)
  • Ô AN11 (ốm đau, thai sản) = countif($E11:$AI11;$₲$38)

Tổng số công sẽ tính tùy vào yêu cầu tính công của nhà cung cấp. Ví dụ: tổng ngày công = Ngày công thực tiễn + Nửa công Ҳ 0,5 + Nghỉ hưởng lương + Ốm đau, thai sản

Ô AO11 = AJ11+AK11*0,5+AL11+AN11

Sau khoảng thời gian đặt công thức xong, ta sao chép công thức xuống cho các nhân viên khác

Khi hoàn thiện sẽ có như sau:

Như vậy là đã hoàn thiện xong việc chấm công, tạo ký hiệu và tính số công. Giờ bạn có thể xác minh công thức bằng cách chấm thử vài ký hiệu vào bảng để xác minh.

Như vậy là ta đã hoàn chỉnh xong bảng chấm công của tháng 1. Giờ chỉ còn việc sao chép ra các tháng sót lại.

Ngoài việc chấm công, tất cả chúng ta còn cần phải làm những công việc khác liên quan tới tiền lương, bảo hiểm, thuế TNCN… Để nắm được những tri thức và cách ứng dụng Excel trong công việc hành chính-nhân sự, mời bạn tham gia khóa học Hành chính nhân viên từ 𝓐-Ż của Học Excel Online. Tiết lộ là bạn còn được tặng kèm software quản lý nhân viên khi tham gia khóa học này nhé. À quên, bạn có thể tải về file excel theo dõi nghỉ phép khi tham gia khóa học nữa. Cụ thể xem tại: Học Excel Online


Xem thêm những thông tin liên quan đến đề tài hàm tính ngày công trong excel

Hướng dẫn tạo bảng chấm công sử dụng hàm Countif – Đỗ Thị Yến – 0132 – KT0802

alt

  • Tác giả: Yến Đỗ Thị
  • Ngày đăng: 2018-04-27
  • Nhận xét: 4 ⭐ ( 7476 lượt nhận xét )
  • Khớp với kết quả tìm kiếm:

Công thức tính ngày công bình các hàm trong Microsoft Excel

  • Tác giả: gitiho.com
  • Nhận xét: 4 ⭐ ( 1904 lượt nhận xét )
  • Khớp với kết quả tìm kiếm: Trong nội dung này, Gitiho sẽ hướng dẫn bạn các công thức tính ngày công trên bảng chấm công có cả số và ký hiệu chữ bằng các hàm trong Excel vô cùng đơn giản.

Phương pháp tính số ngày trong Excel – Hàm, công thức tính số ngày trong Excel

  • Tác giả: congthucexcel.com
  • Nhận xét: 5 ⭐ ( 2728 lượt nhận xét )
  • Khớp với kết quả tìm kiếm: Trong quá trình xử lý dữ liệu trên Excel, chắc hẳn bạn sẽ gặp nhiều trường hợp cần tính toán số ngày như số ngày khách lưu trú, số ngày thực hiện công việc…

Hướng dẫn công thức tính ngày công trong excel tiên tiến nhất 2020

  • Tác giả: odoovietnam.com.vn
  • Nhận xét: 4 ⭐ ( 7219 lượt nhận xét )
  • Khớp với kết quả tìm kiếm: Công thức tính ngày công trong excel là một trong những từ khóa được gg search nhiều nhất về đề tài công thức tính ngày công trong excel. Trong nội dung này, odoovietnam.com.vn sẽ viết nội dung hướng dẫn công thức tính ngày công trong excel tiên tiến nhất 2020.

Cách cộng ngày trong Excel đơn giản dễ hiểu dành riêng cho bạn

  • Tác giả: teky.edu.vn
  • Nhận xét: 5 ⭐ ( 1852 lượt nhận xét )
  • Khớp với kết quả tìm kiếm: Cách cộng ngày trong Excel không hề phức tạp như các bạn hình dung. Chúng rất dễ thao tác với các phép tính và hàm cơ bản.

Công thức tính ngày công bình các hàm trong Microsoft Excel

  • Tác giả: caodangytehadong.edu.vn
  • Nhận xét: 4 ⭐ ( 4082 lượt nhận xét )
  • Khớp với kết quả tìm kiếm: Nội dung chính

Hướng dẫn công thức tính ngày công trong excel tiên tiến nhất [thoigian] – Lava

  • Tác giả: lava.com.vn
  • Nhận xét: 4 ⭐ ( 9650 lượt nhận xét )
  • Khớp với kết quả tìm kiếm:

Xem thêm các nội dung khác thuộc thể loại: Thủ thuật máy tính

Xem Thêm  Cách Làm Kem Que Dưa Hấu Đơn Giản Bằng Máy Xay Sinh Tố, Cách Làm Kem Dưa Hấu Không Cần Máy - cách làm kem que dưa hấu

By ads_php