Trang chủ / Đời Sống / Cách lập công thức tính ngày nghỉ phép năm trên Excel – Học Excel Online Miễn Phí

Cách lập công thức tính ngày nghỉ phép năm trên Excel – Học Excel Online Miễn Phí

Tính số ngày phép trong năm của hàng loạt nhân sự là một việc làm không hề đơn thuần, bởi quy tắc tính số ngày phép năm khá phức tạp và phụ thuộc vào vào nhiều yếu tố. Hầu như những ai làm về hành chính – nhân sự đều phải triển khai việc làm này. Do đó để giúp các bạn hiểu được cách tính phép năm, tất cả chúng ta hãy cùng Học Excel Online khám phá cách lập công thức tính ngày nghỉ phép năm trên Excel .

Quy định về tính số ngày phép năm

Phép năm phụ thuộc vào vào thời hạn tính từ khi người lao động mở màn thao tác và được tính theo nguyên tắc tròn tháng. Quy định hoàn toàn có thể mỗi nơi 1 khác nhau, nhưng dựa trên nguyên tắc chung như sau :

  • Căn cứ theo ngày vào làm: vào làm trước ngày 16 (từ ngày 1 đến ngày 15) thì được tính làm tròn thành 1 tháng. Sau ngày 16 thì không tính tháng đó (làm tròn thành 0)
  • Căn cứ theo thâm niên: vào làm đủ 1 năm (12 tháng) thì bắt đầu được tính ngày nghỉ phép năm. Mỗi tháng thâm niên tiếp theo 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 được tính thêm 1 ngày nghỉ phép năm. Chúng ta có thể mô tả quy định trên dưới dạng bảng như 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 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:

a. 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 tất cả 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 hoàn toàn có thể cộng lại để tính ra tổng số tháng .
Ô C2 được cố định và thắt chặt trong công thức vì tham chiếu tới C2 là 1 vị trí chỉ Open 1 lần, không đổi .
E4 = IF ( OR ( D4 > USD C USD 2, D4 = ” ” ), ” ”, IF ( ( DAY ( USD C USD 2 ) – DAY ( D4 ) ) > = 15,1,0 ) + ( YEAR ( USD C USD 2 ) * 12 + MONTH ( USD C USD 2 ) ) – ( YEAR ( D4 ) * 12 + MONTH ( D4 ) ) )
Áp dụng tựa như từ E4 tới E14 ta có tác dụng 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 : USD A $ 13, Quy_tac_tinh ! USD C USD 3 : USD C $ 13 )
Phải cố định và thắt chặt 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 tất cả chúng ta đã hoàn thành xong được bảng theo dõi ngày phép năm cho mỗi nhân viên cấp dưới trong công ty rồi .
Các bạn hoàn toàn có thể tải về file Excel mẫu tại địa chỉ : http://bit.ly/2H9gkYI

Xem thêm: Hướng dẫn cách theo dõi số ngày phép còn lại trong năm trên Excel

Kiến thức nâng cao

Để giúp các bạn nâng cao kỹ năng và kiến thức trong việc làm quản trị nhân sự thì mình có 1 vài gợi ý để các bạn tìm hiểu và khám phá 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ự yên cầu bạn cần nắm được rất nhiều kiến thức và kỹ năng và kiến thức và kỹ năng, đặc biệt quan trọng là sử dụng Word, Excel. Công việc này yên cầu 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…

Học Excel Online xin ra mắt tới bạn khóa học HCNS101-Trọn bộ kỹ năng và kiến thức Hành chính nhân sự. Đây là khóa học rất có ích và giúp bạn chớp lấy được không thiếu kiến thức và kỹ năng, kiến thiết xây dựng những kiến thức và kỹ năng thiết yếu để triển khai xong tốt việc làm. Chi tiết xem tại :

Bài viết liên quan