Nhật Phương
Nhật Phương
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 120 lượt xem

Hướng dẫn cách tính số ngày nghỉ phép năm trên Excel

Nhật Phương
Nhật Phương
Sep 17 2021

Trong công việc chấm công – tính lương, việc theo dõi số ngày nghỉ phép năm của người lao động là rất cần thiết. Để hỗ trợ cho việc tính ngày nghỉ phép năm thì các HR không thể không ứng dụng đến phần mềm Excel. Nếu bạn gặp khó khăn khi không biết xây dựng công thức tính trên Excel như thế nào cho đầu việc này thì hãy cùng Gitiho.com tìm hiểu cách lập quy tắc và công thức tính trong bài viết này nhé!

Xem thêm: Cách tính công theo ký hiệu kép trong bảng chấm công Excel

Xây dựng bảng quy tắc tính ngày nghỉ phép năm

Quy định về nghỉ phép năm có thể không giống nhau, tuy nhiên có thể dựa trên một nguyên tắc chung như sau:

1. Cứ làm đủ 12 tháng (tính từ ngày bắt đầu làm việc đến khi đủ 12 tháng) thì bắt đầu được tính phép năm. Cứ mỗi tháng thâm niên tiếp theo sẽ được tính 01 ngày phép.

2. Nếu ngày vào làm là trước ngày 16 thì sẽ được làm tròn lên thành 1 tháng làm việc.

3. Sau mỗi 05 năm làm việc (tương đương 60 tháng) thì sẽ cộng thêm 1 ngày phép trong 1 năm.

4. Số ngày phép dùng không hết trong năm sẽ được công ty trả lương như ngày làm việc thông thường (hoặc trả theo hình thức khác tùy quy định của công ty) chứ không cộng dồn sang năm sau, chỉ sử dụng trong năm đó mà thôi.

Dựa trên nguyên tắc này chúng ta có thể diễn giải cụ thể thành 1 bảng như sau:


Xem thêm: Ứng dụng Excel trong kế toán tiền lương, nhân sự

Xây dựng công thức tính ngày nghỉ phép năm trên Excel

Dựa vào bảng dữ liệu mẫu dưới đây chúng ta sẽ 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:


Tính số tháng làm việc (tháng thâm niên – cột E)

Đây là phần khó nhất trong việc tính ngày nghỉ phép năm. Bởi trong việc này chúng ta phải biện luận nhiều trường hợp xảy ra theo từng bước:

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 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:


Xem thêm: Công thức tính giờ làm việc giữa giờ vào, giờ ra và giờ nghỉ

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:


Như vậy chúng ta đã hoàn thành được bảng theo dõi ngày phép năm cho mỗi nhân viên trong công ty rồi.

Xem thêm: Hướng dẫn tính số ngày nghỉ phép có điều kiện trên Excel

Kiến thức nâng cao về tính ngày nghỉ phép năm

Để giúp các bạn nâng cao kiến thức trong công việc quản lý nhân sự thì mình có 1 vài gợi ý để các bạn tìm hiểu như sau:

Trong danh sách nhân sự mà bạn quản lý bao gồm cả nhân sự đang làm việc và đã nghỉ việc. Do đó chúng ta phải phân biệt những nhân viên đã nghỉ việc để không đưa vào bảng theo dõi ngày phép. Việc này làm thế nào?

Việc sử dụng ngày phép trong năm của mỗi nhân sự được theo dõi thế nào? Ở đâu?

Làm cách nào để tính được số ngày nghỉ phép còn lại chưa sử dụng của mỗi nhân viên?

Công việc hành chính – nhân sự đòi hỏi bạn cần nắm được rất nhiều kiến thức và kỹ năng, đặc biệt là sử dụng Word, Excel. Công việc này đòi hỏi bạn phải ứng dụng tốt các công cụ word, excel để:

Soạn thảo văn bản hiệu quả, thẩm mỹ, đúng chuẩn

Quản lý công văn, giấy tờ, hợp đồng lao động, quản lý nhân sự

Quản lý cấp phát đồ dùng, văn phòng phẩm

Chấm công, tính lương, làm bảo hiểm, tính thuế thu nhập cá nhân…

Xem thêm: Hướng dẫn theo dõi đi làm muộn từ dữ liệu máy chấm công

Tổng kết

Như vậy, chúng ta đã cùng tìm hiểu những kiến thức hữu ích về việc tính số ngày nghỉ phép năm cho nhân sự bằng phần mềm Excel. Mong là bạn đã thông qua bài viết này để thu thập thêm những hiểu biết và kĩ năng cần thiết để hỗ trợ cho công việc quản lý nhân sự. Để khám phá thêm nhiều bài học bổ ích và có tính ứng dụng cao cho nghề Hành chính - Nhân sự, Gitiho xin giới thiệu tới bạn khóa học HCNSG02-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:

HCNSG02 - KHÓA HỌC KỸ NĂNG NGHỀ HÀNH CHÍNH NHÂN SỰ TỔNG HỢP


Tài liệu kèm theo bài viết

Gitiho.com Nền tảng học online duy nhất tại Việt Nam tập trung vào phát triển kỹ năng làm việc dành cho người đi làm
Với sứ mệnh: Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người”, đội ngũ phát triển Gitiho.com đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án...
Gitiho tự hào khi được đồng hành cùng:
  • 50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
  • 100.000+ học viên trên khắp Việt Nam

Tìm hiểu ngay các khóa học của Gitiho TẠI ĐÂY



Thảo luận 0 câu trả lời
Lượt xem 120 lượt xem
Vỗ tay vỗ tay

0 Bình luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội