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
Excel được ứng dụng nhiều trong công việc Hành chính – Nhân sự. Một trong những việc thường gặp là theo dõi, quản lý thời hạn của hợp đồng lao động. Làm cách nào để tự động tính ra được ngày hết hạn của hợp đồng lao động dựa vào Loại hợp đồng và Ngày ký? Đây là băn khoăn của hầu hết những ai đang làm công việc quản lý nhân sự trên Excel. Trong bài viết này Học Excel Online sẽ giúp bạn biế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.
Để làm rõ nội dung này, chúng ta cùng xét ví dụ sau:
Giả sử bạn đang quản lý 6 nhân viên, tương ứng với các thông tin về hợp đồng lao động cụ thể như sau:
Yêu cầu đặt ra là phải xác định ngày hết hạn hợp đồng vào cột E bằng công thức.
Xem nhanh
Cách thực hiện
Xác định hàm có thể sử dụng
Trong bài viết này Học Excel Online xin giới thiệu với các bạn cách sử dụng hàm Date để xác định ngày hết hạn của hợp đồng.
Hàm DATE là hàm giúp tạo ra 1 giá trị ngày tháng dựa trên việc xác định các đối tượng: YEAR (năm), MONTH (tháng) và DAY (ngày). Theo logic thì:
Ngày hết hạn = Ngày ký + Số thời gian hợp đồng.
Tuy nhiên Số thời gian hợp đồng phải thống nhất là Năm hay Tháng (quy về 1 loại), và yêu cầu là phải dùng để tính toán được.
Lập bảng phụ cho Loại hợp đồng
Do có nhiều loại hợp đồng và tên loại hợp đồng là dữ liệu dạng chuỗi (text), do đó không thể dùng trực tiếp trong công thức để tính.
Chúng ta có thể lập 1 bảng phụ để xác định số thời gian tương ứng với các loại hợp đồng như sau:
Trong bảng này quy về đơn vị là Năm. Do đó số liệu nhập trong cột Số năm sẽ nhập theo dạng Số. Với loại hợp đồng vô thời hạn có thể coi như số năm bằng 40 (khoảng thời gian tối đa 1 người tham gia lao động tại 1 đơn vị), bởi nếu không có số năm sẽ gây ra lỗi ở công thức (có thể bẫy lỗi này nhưng việc quy định là 40 sẽ dễ hơn khi tính toán và quản lý trên Excel)
Khi đó để xác định loại hợp đồng ở cột D tương ứng với bao nhiêu năm, chúng ta chỉ cần sử dụng hàm VLOOKUP để tham chiếu.
Công thức xác định ngày hết hạn hợp đồng
Tại ô E2 chúng ta kết hợp hàm DATE với các hàm YEAR, VLOOKUP, MONTH, DAY như sau:
E2=DATE(YEAR(C2)+VLOOKUP(D2,$G$2:$H$4,2,0),MONTH(C2),DAY(C2))
Trong đó:
- Số năm được xác định = Số năm của Ngày ký HĐ cộng với số năm tương ứng của Loại hợp đồng
- Số tháng được xác định = Số tháng của Ngày ký HĐ
- Số ngày được xác định = Số ngày của Ngày ký HĐ
FillDown công thức từ E2 xuống tới E7 chúng ta thu được kết quả như sau:
Các bạn có thể tải về file mẫu tại địa chỉ: http://bit.ly/2K9IHbM
Xem thêm: 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
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:
