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.
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ỉ: //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:
Việc xác định thời điểm hết hạn, đến hạn của 1 hợp đồng trên Excel đôi khi khiến chúng ta gặp nhiều khó khăn. Bởi không phải lúc nào bản hợp đồng cũng tính theo nguyên tắc tròn năm. Có những bản hợp đồng còn xác định cụ thể theo từng năm, từng tháng, từng ngày. Khi đó chúng ta sẽ xác định thời điểm hết hạn của hợp đồng đó bằng cách nào? Sau đây Học Excel Online sẽ giới thiệu tới các bạn 3 cách xác định hết thời hạn hợp đồng trên Excel. Hãy cùng tìm hiểu nhé:
Ví dụ: Xác định ngày hết hạn của 5 hợp đồng có ngày ký và hiệu lực như sau:
Cách 1: Tính tương đối bằng các phép tính cơ bản
Cách này dựa trên nguyên tắc: 1 năm quy ước có 365 ngày, 1 tháng quy ước có 30 ngày
Bởi đôi khi có thể dùng quy ước như vậy để tính nhanh, hoặc khi hợp đồng cho phép tính theo quy ước, tính tương đối mà không yêu cầu tính chính xác đến chi tiết, hoặc có thể áp dụng khi bạn chưa hiểu rõ cách dùng các hàm trong Excel để tính một cách chính xác.
Trường hợp này tính như sau:
Ngày hết hạn = Ngày ký + (Số năm*365 + Số tháng*12 + Số ngày)
Kết quả như sau:
Cách 2: Dùng chuỗi hàm DATE, YEAR, MONTH, DAY
Mục tiêu chính của cách này là dùng hàm DATE. Nhưng để có thể xác định đúng các thành phần trong hàm này thì phải kết hợp các hàm YEAR, MONTH, DAY
Để hiểu rõ cấu trúc của các hàm này, mời các bạn tham khảo bài viết:
Cách xác định Ngày Tháng Năm bằng các hàm thời gian trong Excel có bài tập
Nguyên tắc của cách làm này:
- Xác định ngày hết hạn bằng hàm DATE
- Giá trị năm của hàm DATE được xác định: Năm của ngày ký (ô A3) + Số năm (ô E3)
- Giá trị tháng của hàm DATE được xác định: Tháng của ngày ký (ô A3) + Số tháng (ô D3)
- Giá trị ngày của hàm DATE được xác định: Ngày của ngày ký (ô A3) + Số ngày (ô C3)
Kết quả của hàm Date như sau:
Cách này có ưu điểm: Xác định được chính xác thời điểm ngày một cách cụ thể. Khắc phục được sai lệch ngày như ở cách 1
Cách 3: Dùng hàm EDATE
Hàm EDATE là hàm xác định ngày kết thúc (End Date) dựa theo điều kiện ngày bắt đầu và số tháng thay đổi tính từ ngày bắt đầu. Tuy nhiên có 1 nhược điểm là hàm EDATE xác định thời điểm kết thúc dựa vào số tháng, trong khi đó chúng ta còn có yếu tố Ngày.
Nguyên tắc của cách làm này là:
- Xác định ngày kết thúc dựa theo tháng và năm tính từ ngày bắt đầu (không bao gồm số ngày) bằng hàm EDATE . Số tháng = D3+E3*12 (nguyên tắc 1 năm có 12 tháng)
- Xác định ngày kết thúc hợp đồng một cách chính xác bằng việc lấy kết quả hàm EDATE cộng thêm số ngày ở cột C
Kết quả của hàm như sau:
Cách dùng hàm EDATE này ra kết quả tương tự như với hàm DATE và đều chính xác tuyệt đối.
Các bạn có thể tham khảo thêm một số nội dung sau:
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
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 với các bạn Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để giúp bạn làm việc trên Excel được tốt hơn, hiệu quả hơn. Chi tiết xem tại: