Cách thiết lập tự động cảnh báo hợp đồng lao động sắp hết hạn trên file Excel
Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách thiết lập tự động cảnh báo hợp đồng lao động sắp hết hạn khi quản lý hợp đồng lao động, quản lý nhân sự trên file Excel. Hay nói cách khác là tạo cảnh báo nhắc nhở trong excel bằng những thao tác hết sức đơn giản
Xem nhanh
Thống nhất về cách thông báo, cảnh báo
Trong công việc hành chính, nhân sự, việc quản lý hợp đồng lao động rất quan trọng. Hợp đồng lao động có 2 loại: loại xác định thời hạn và loại không xác định thời hạn. Đối với loại hợp đồng xác định thời hạn thì đến khi gần hết hạn chúng ta phải biết để ký hợp đồng mới. Muốn máy tính tự động cảnh báo cho chúng ta thì cần phải thống nhất trước về nguyên tắc, cách hoạt động: Khi nào thì cảnh báo? Cách cảnh báo như thế nào? Các mức độ cần thông báo ra sao?
Để tiện cho việc theo dõi bài viết, chúng ta sẽ thống nhất theo những nguyên tắc sau:
Các mức độ cảnh báo:
Để Tạo cảnh báo nhắc nhở trong excel ta cần phải đặt ra các mức độ cảnh báo:
- Mức 1: Còn 1 tháng là hết hạn
- Mức 2: Còn 1 tuần là hết hạn
- Mức 3: Đã hết hạn
Thời gian căn cứ vào thời điểm hiện tại và thời gian ký hợp đồng.
Cách thức cảnh báo: Tương ứng với các mức cảnh báo lần lượt là:
- Mức 1: Tô màu nền vàng nhạt, chữ thường
- Mức 2: Tô màu nền cam, chữ đậm
- Mức 3: Tô màu chữ đỏ, nền xám nhạt, chữ đậm
Công cụ cần sử dụng
Trong Excel đã có sẵn công cụ để làm việc này. Đó là Conditional Formatting – Định dạng theo điều kiện. Khi thỏa mãn điều kiện đưa ra thì Excel sẽ tự động định dạng dữ liệu trong vùng được thiết lập.
Bài này sẽ không nói cụ thể Conditional formatting là gì, cách dùng ra sao, mà sẽ hướng dẫn bạn cách làm trực tiếp luôn. Nếu bạn chưa nắm rõ về công cụ này thì có thể tham khảo bài viết: Cách sử dụng định dạng có điều kiện trong Excel 2010, 2013 và 2016
Xác định ngày hết hạn hợp đồng dựa vào ngày ký và loại hợp đồng
Để tìm hiểu cách xây dựng công thức xác định ngày hết hạn hợp đồng tại cột E, mời các bạn tham khảo bài viết:
Cách lập công thức xác định ngày hết hạn hợp đồng tự động trên Excel
Đây là kết quả sau khi đã áp dụng công thức xác định ngày hết hạn hợp đồng tại cột E:
Cách ứng dụng Conditional Formatting để tự động cảnh báo thời hạn hợp đồng
Dựa trên nguyên tắc tự động cảnh báo đã xây dựng ở bước 1, chúng ta sẽ thiết lập công thức trong Conditional Formatting như sau:
Chọn toàn bộ bảng dữ liệu từ A2:E7, sau đó chọn Conditional Formatting trong thẻ HOME
Trong cửa sổ Conditional Formatting chọn New Rule / Use a formulas… (thiết lập công thức trong Conditional Formatting).
Trước khi viết công thức Tạo cảnh báo nhắc nhở trong excel chúng ta cần trình bày được logic bằng lời sang dạng công thức:
- Nếu Ngày hết hạn là ô trống (không có dữ liệu) thì sẽ không có căn cứ để cảnh báo. Do đó phải loại trường hợp giá trị ở cột E là rỗng
- Nếu ngày ở cột E < Ngày hiện tại – 30 thì sẽ cảnh báo ở mức 3
- Nếu ngày ở cột E < Ngày hiện tại và > Ngày hiện tại – 7 (trong vòng 1 tuần trước ngày hiện tại) thì cảnh báo ở mức 2
- Nếu ngày ở cột E < Ngày hiện tại – 7 và > Ngày hiện tại – 30 (nằm ngoài mức 2 và thỏa mãn mức 1) thì cảnh báo ở mức 1
Khi đó công thức ở các mức lần lượt là:
Mức 1: Công thức là =AND($E2<>””,$E2>=TODAY()-30,$E2<TODAY()-7)
Mức 2: Công thức là =AND($E2<>””,$E2<TODAY(),$E2>=TODAY()-7)
Mức 3: Công thức là =AND($E2<>””,$E2<TODAY()-30)
Chú ý cố định cột E nhưng không cố định hàng 2 để khi xuống các dòng tiếp theo thì Conditional Formatting sẽ tự động thay đổi dòng tương ứng để tính kết quả cho đúng.
Kết quả sau khi áp dụng Conditional Formatting là:
Như vậy kết quả đã đúng như yêu cầu ban đầu của chúng ta đưa ra rồi: tạo cảnh báo nhắc nhở trong excel. Khi đó chỉ cần thay đổi, thêm dữ liệu bất kỳ thì Excel sẽ tự động thay đổi cảnh báo tương ứng theo kết quả tính được trong Conditional Formatting.
Các bạn có thể tải về file mẫu tại địa chỉ: http://bit.ly/2K9IHbM
Ngoài ra Học Excel Online xin giới thiệu tới các bạn khóa học Hành chính nhân sự Online từ A đến Z. Đây là khóa học rất hữu ích cho các bạn trong công việc hành chính – nhân sự, bởi khóa học sẽ giúp bạn nắm được kiến thức một cách có hệ thống, tự tay xây dựng công cụ quản lý công việc trên excel và rất nhiều điều bổ ích khác. Chi tiết xem tại:
