Công thức tính phép năm trong Excel cực chi tiết

Công việc tính số ngày phép trong năm của toàn bộ nhân sự là một công việc không hề đơn giản, bởi vì quy tắc tính số ngày phép năm khá là phức tạp và còn phụ thuộc vào nhiều yếu tố khác. Vì vậy, hầu như những ai làm về lĩnh vực hành chính – nhân sự đều phải thực hiện các công việc này. Với mục đích có thể giúp các bạn hiểu được cách tính phép năm, bài viết dưới đây sẽ hướng dẫn bạn chi tiết các công thức tính phép năm trong Excel, cùng đón đọc nhé!

QUY ĐỊNH VỀ TÍNH SỐ NGÀY PHÉP NĂM

Phép năm sẽ phụ thuộc vào thời gian tính từ lúc người lao động bắt đầu làm việc, được tính theo nguyên tắc tròn tháng. Quy định này có thể mỗi nơi sẽ 1 khác nhau tuy nhiên đều dựa trên nguyên tắc chung như sau:

  • Căn cứ theo ngày vào làm: ví dụ nếu vào làm trước ngày 16 (từ ngày 1 đến ngày 15) thì sẽ được tính làm tròn thành 1 tháng. Nếu vào sau ngày 16 thì không tính tháng đó (và làm tròn thành 0)
  • Căn cứ theo thâm niên: Nếu vào làm đủ 1 năm (12 tháng) thì sẽ được bắt đầu tính ngày nghỉ phép năm. Mỗi tháng thâm niên tiếp theo người làm sẽ được tính 01 ngày phép (1 năm được 12 ngày nghỉ phép). Mỗi 5 năm làm việc, người làm sẽ được tính thêm 1 ngày nghỉ phép năm. Cùng tham khảo kĩ hơn thời gian nghỉ phép quy định chi tiết trong bảng sau:

XÂY DỰNG CÔNG THỨC TÍNH TRÊN EXCEL

Dựa vào bảng dữ liệu mẫu bên dưới, chúng ta sẽ cùng nhau thực hành xây dựng công thức tính số ngày nghỉ phép năm vào bảng:

A. TÍNH SỐ THÁNG LÀM VIỆC (THÁNG THÂM NIÊN – CỘT E)

Đây được biết là phần khó nhất trong quá trình tính ngày nghỉ phép năm. Bởi vì trong việc này, chúng ta sẽ phải biện luận từng trường hợp khác nhau theo các bước bên dưới:

  • Bước 1: Ngày vào làm sau ngày tính (ô C2) hoặc không có ngày vào làm. Trường hợp này sẽ không tính toán gì hoặc coi như kết quả = 0
  • Bước 2: Làm tròn tháng dựa theo ngày vào làm. Xét phần Ngày trong cột D (ngày vào làm). Nếu Ngày vào làm – Ngày bắt đầu tính (ngày 01 tại ô C2) >=15 thì được làm tròn thành 1 tháng, còn lại tính là 0.
  • Bước 3: Quy đổi số năm về số tháng để xác định số tháng chênh lệch giữa thời điểm tính (C2) với thời điểm vào làm (D4 đến D14)

Công thức tiếp theo được tính như sau:

  • Bước 1: IF(OR(D4>$C$2,D4=””),””,…)    Phần … là nội dung ở bước 2+bước 3
  • Bước 2: IF(DAY($C$2)-DAY(D4))>=15,1,0)
  • Bước 3: (YEAR($C$2)*12+MONTH($C$2))-(YEAR(D4)*12+MONTH(D4))

Bước 2 và bước 3 đều dùng để tính tháng nên có thể cộng lại để tính ra tổng số tháng.

Ô C2 được cố định trong công thức vì tham chiếu tới C2 là 1 vị trí chỉ xuất hiện 1 lần, không đổi.

E4=IF(OR(D4>$C$2,D4=””),””,IF((DAY($C$2)-DAY(D4))>=15,1,0)+(YEAR($C$2)*12+MONTH($C$2))-(YEAR(D4)*12+MONTH(D4)))

Áp dụng tương tự từ E4 tới E14 ta có kết quả như sau:

B. TÍNH SỐ NGÀY NGHỈ PHÉP NĂM (CỘT F)

Sau khi đã tính được số tháng làm việc rồi thì việc tính số ngày nghỉ phép năm trở nên rất đơn giản. Chúng ta chỉ cần đối chiếu số tháng làm việc đã tính được ở cột E với bảng quy định về nghỉ phép năm.

Sử dụng hàm LOOKUP như sau:

  • lookup_value: đối tượng tìm kiếm là số tháng làm việc là cột E, bắt đầu từ E4
  • lookup_vector: vùng chứa đối tượng tìm kiếm, là cột Tháng thâm niên trong bảng Quy tắc tính (cột A, từ A3:A13)
  • result_vector: vùng chứa kết quả tìm kiếm, là cột Số ngày phép 1 năm trong bảng quy tắc tính (cột C, từ C3:C13)

Công thức được viết như sau:

F4=LOOKUP(E4,Quy_tac_tinh!$A$3:$A$13,Quy_tac_tinh!$C$3:$C$13)

Phải cố định vùng A3:A13 và C3:C13 trong bảng quy tắc tính (trong sheet có tên là Quy_tac_tinh)

Kết quả thu được như sau:

Vậy là bạn đã hoàn thành xong công thức tính phép năm trong Excel rồi đấy, chúc bạn thành công!

Nếu bạn đang cần tìm hiểu cách cố định công thức trong Excel, đừng bỏ qua bài viết bên dưới nhé! Khi bạn thực hiện tham chiếu ô trong công thức, địa chỉ ô sẽ tự động điều chỉnh khi bạn di chuyển đến một vùng khác hay sao chép…Xem chi tiết: Làm thế nào để cố định công thức trong Excel?
Bài viết này sẽ hướng dẫn cho bạn chi tiết cách bỏ số 0 trong Excel nhanh chóng và đơn giản. CÁCH GIỮ SỐ 0 ĐẦU DÒNG – FOMAT CELLS Khi bạn muốn giữ số 0 đầu dòng trong Excel, bạn phải định dạng ô tính dưới dạng Text trước khi…Xem chi tiết: Hướng dẫn cách bỏ số 0 trong Excel nhanh chóng
Cùng tìm hiểu chi tiết về cách gõ tiếng việt có dấu trong bài viết bên dưới nhé! TÌM HIỂU VỀ CÁCH GÕ CHỮ Trước khi tìm hiểu về cách gõ tiếng Việt trong Excel, hãy cùng chúng tôi tìm hiểu rõ hơn về các khái niệm Kiểu gõ, bảng mã…Xem chi tiết: Hướng dẫn cách gõ tiếng Việt, dùng gõ tắt và chuyển mã với Unikey
Khi thực hiện việc chia sẻ bảng tính Excel với người khác, bạn thường sẽ không muốn họ xem được một số dòng hoặc cột trong bảng tính. Với bài viết này, chúng tôi sẽ hướng dẫn bạn cách giới hạn vùng làm việc trong Excel một cách nhanh chóng và…Xem chi tiết: Hướng dẫn các cách giới hạn vùng làm việc trong Excel
Theo mặc định, kết quả công thức sẽ hiển thị trong các ô tính của Excel. Bạn chỉ có thể xem công thức trên thanh công thức khi đã thực hiện chọn một ô cụ thể. Nếu áp dụng cách này, việc xem tất cả công thức trong toàn bộ…Xem chi tiết: Hướng dẫn cách hiện công thức trong Excel
Được đăng trong Kiến thức.

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *