Tự động phân biệt ngày lễ, ngày chủ nhật trong bảng chấm công mỗi ngày có 2 cột

Trong các bài viết về hướng dẫn tạo bảng chấm công trên Excel trước đây, Học Excel Online đã giới thiệu tới các bạn cách sử dụng chức năng Conditional formatting để tự động phân biệt ngày lễ, ngày chủ nhật trong tháng một cách tự động. Tuy nhiên đó là dạng bảng chấm công mà mỗi ngày chỉ có 1 cột. Nhưng trong một số trường hợp yêu cầu chấm công phức tạp hơn thì đôi khi chúng ta phải sử dụng nhiều hơn 1 cột cho 1 ngày. Ví dụ như 1 ngày bao gồm vừa chấm công giờ làm chính, vừa chấm công giờ làm thêm thì cần 2 cột thể hiện 2 loại giờ công này. Vậy chúng ta hãy cùng tìm hiểu xem cách làm thế nào để tự động phân biệt ngày lễ, ngày chủ nhật trong bảng chấm công mỗi ngày có 2 cột nhé:

1. Mẫu bảng chấm công mỗi ngày có 2 cột

Chúng ta có mẫu bảng chấm công như sau:

Trong bảng chấm công này đã xác định:

• Ngày trong tháng: Mỗi khi thay đổi số tháng hoặc số năm thì ngày trong tháng sẽ tự động thay đổi theo (dòng 7)
• Thứ trong tuần: Khi ngày thay đổi thì thứ tương ứng với ngày đó cũng thay đổi theo (dòng 8)
• Loại ngày: Phân biệt ngày trong tuần là ngày chủ nhật hay ngày lễ (dòng 9)
• Hai cột chấm công trong mỗi 1 ngày là C (công hành chính) và T (công làm thêm) (dòng 10)

Tất cả những nội dung này đã được hướng dẫn trong bài: Hướng dẫn cách lập bảng chấm công bộ phận hành chính

Các bạn có thể tải về mẫu bảng chấm công này tại địa chỉ: http://bit.ly/2ljvVwg

Mục đích của chúng ta trong bài viết này đó là tự động định dạng để phân biệt ngày chủ nhật, ngày lễ trong tháng bằng chức năng Conditional Formatting.

2. Thiết lập dòng loại ngày một cách chính xác

Trong bảng chấm công trên, dòng loại ngày (dòng 9) về mặt công thức là đúng, nhưng về cách trình bày lại chưa chính xác: Bởi việc sử dụng merge cell trong mỗi 1 ngày như vậy sẽ không thể áp dụng được Conditional Formatting một cách chính xác được.

Cách khắc phục như sau:

a. Bỏ chức năng Merge cell tại dòng 9

Các bạn chọn toàn bộ dòng 9, từ cột D tới cột BM (trong phạm vi các ngày trong tháng) và bấm vào Merge cell trên thẻ Home để bỏ đi chức năng Merge cell đang áp dụng tại đây

b. Thay đổi công thức xác định loại ngày:

Tại ngày đầu tiên của tháng, ô D9 có công thức:

D9=IF(D7=””,””,IF(COUNTIF($B$32:$B$42,D7)>0,3,IF(D8=”CN”,2,1)))

Nội dung công thức:

• Nếu D7 là rỗng (tức không có ngày) thì kết quả là rỗng để tránh lỗi
• Nếu D7 nằm trong vùng B32:B42 (danh sách ngày lễ) thì kết quả là 3 (mã kí hiệu loại ngày là ngày lễ)
• Nếu D8 là CN (ngày chủ nhật) thì kết quả là 2 (mã kí hiệu loại ngày là ngày chủ nhật)
• Còn lại không phải các trường hợp trên thì là 1 (mã kí hiệu loại ngày là ngày thường)

Công thức này đã đúng.

Nhưng trong ngày 01 có 2 cột, nên tại vị trí E9 cũng cần có công thức xác định loại ngày. Do đó tại E9 chúng ta sẽ viết lại chính công thức tại D9, hay có thể dùng E9=D9

Sau đó chọn cả hai ô D9 và E9 rồi Fillright (copy công thức sang phải) với cả 2 ô này. Thao tác như sau:

Như vậy kết quả cuối cùng chúng ta thu được sẽ là:

3. Thiết lập Conditional Formatting để tự động phân biệt loại ngày

Khi đã xong bước 2 rồi thì việc thiết lập Conditional Formatting lại trở nên rất dễ dàng. Chúng ta chỉ cần đặt 2 lần (2 rule) tương ứng với loại ngày Chủ nhật và loại ngày Lễ. Cách làm như sau:

a. Tự động phân biệt ngày lễ

Vì ngày lễ được ưu tiên hơn so với ngày chủ nhật nên sẽ thực hiện trước.

• Trong Conditional Formatting chọn New Rules
• Trong cửa sổ New Formatting Rule chọn Use a formula…
• Nhập công thức =D$9=3 (xét mỗi giá trị ở dòng 9, bắt đầu từ cột D tới cột BM có bằng 3 hay không)

Kết quả thu được là:

b. Tự động phân biệt ngày chủ nhật

Tương tự như ngày lễ, nhưng công thức của chúng ta là =D$9=2

Kết quả cuối cùng là:

Như vậy là chúng ta đã hoàn thành yêu cầu tự động phân biệt ngày chủ nhật và ngày lễ trong bảng chấm công 2 cột mỗi ngày rồi. Chúc các bạn thành công!

Tải về file kết quả tại địa chỉ: http://bit.ly/2t8uipO

Ngoài ra còn rất nhiều kiến thức bổ ích khác nữa mà bạn có thể học được trong khóa học: HCNS101-Trọn bộ kỹ năng Hành chính nhân sự. Đây là khóa học rất hữu ích và giúp bạn nắm bắt được đầy đủ kiến thức, xây dựng những kỹ năng cần thiết để hoàn thành tốt công việc. Chi tiết xem tại: