Chia sẻ công thức Excel quy đổi công chuẩn theo ngày vào làm và ngày nghỉ

 Trong doanh nghiệp đều áp dụng quy định tính ngày công chuẩn. Số ngày công chuẩn này được tính toán phụ thuộc vào các yếu tố như:

- Nghỉ ngày CN hay cả ngảy T7, CN? Có những doanh nghiệp có cả 2 loại công này

- Tháng đó có ngày nghỉ lễ không?

(Lưu ý: Các công thức dưới đây chỉ áp dụng với Office 2013 trở lên - có hàm NETWORKDAYS.INTL)

Công thức tính số ngày công chuẩn sử dụng hàm NETWORKDAYS.INTL như sau:

- Đặc tính: Tham số thứ 3 của hàm NETWORKDAYS.INTL: Nếu là 1 thì không tính thứ 7 và chủ nhật, nếu là 11 thì chỉ nghỉ ngày Chủ nhật. Khi đó sẽ sử dụng chính số 1 và 11 này cho cột Loại nghỉ để phục vụ cho tính toán => Cột C

- Công thức tính công chuẩn cho cột D: 

=NETWORKDAYS.INTL(DATE($B$2,$B$1,1),DATE($B$2,$B$1+1,0),C4)

Trong đó: B1 và B2 là giá trị tháng năm của kỳ cần xét công chuẩn

Tuy nhiên, trong thực tế có nhiều trường hợp nhân sự đang làm và nghỉ giữa tháng hoặc nhân sự mới vào làm từ ngày nào đó trong tháng (không phải đầu tháng) thì:

- Những nhân sự vào làm việc trước (Ví dụ: trước tháng 10/2021) thì tính công chuẩn từ ngày 1/10/2021. Những nhân sự nào vào làm trong tháng 10/2021 thì tính từ thời điểm vào làm

- Những nhân sự chưa nghỉ việc thì tính đến hết tháng, ngược lại thì tính đến trước thời điểm nghỉ việc 1 ngày

Công thức quy đổi công chuẩn theo ngày vào làm và ngày nghỉ tại cột E là

=NETWORKDAYS.INTL(MAX(DATE($B$2,$B$1,1),A4),IF(B4="",DATE($B$2,$B$1+1,0),B4-1),C4)

Trong đó:

- MAX(DATE($B$2,$B$1,1),A4) => So sánh với ngày vào làm để lấy ngày lớn hơn

- IF(B4="",DATE($B$2,$B$1+1,0),B4-1) => Nếu ngày nghỉ để trống thì lấy ngày cuối tháng, ngược lại thì lấy ngày nghỉ việc -1

Công thức quy đổi công chuẩn tính làm nửa ngày Thứ 7 và nghỉ này Chủ nhật

=NETWORKDAYS.INTL(MAX(DATE($B$2,$B$1,1),A4),IF(B4="",DATE($B$2,$B$1+1,0),B4-1),11)+NETWORKDAYS.INTL(MAX(DATE($B$2,$B$1,1),A4),IF(B4="",DATE($B$2,$B$1+1,0),B4-1),"1111101")/2

Công thức quy đổi công chuẩn loại trừ ngày nghỉ lễ

Cột G để khai báo các ngày nghỉ lễ trong năm. Phần này mỗi năm sẽ một khác do quy định nghỉ bù của nhà nước theo từng năm, hoặc theo nội bộ công ty. Ví dụ từ $G$1:$G$4

- Công thức tính công chuẩn trừ nghỉ lễ

=NETWORKDAYS.INTL(DATE($B$2,$B$1,1),DATE($B$2,$B$1+1,0),C4,$G$1:$G$4)

- Công thức quy đổi công chuẩn theo ngày vào làm, ngày nghỉ và loại nghỉ lễ

=NETWORKDAYS.INTL(MAX(DATE($B$2,$B$1,1),A4),IF(B4="",DATE($B$2,$B$1+1,0),B4-1),C4,$G$1:$G$4)

- Công thức quy đổi công chuẩn làm nửa ngày T7, Nghỉ chủ nhật và nghỉ lễ

=NETWORKDAYS.INTL(MAX(DATE($B$2,$B$1,1),A4),IF(B4="",DATE($B$2,$B$1+1,0),B4-1),11,$G$1:$G$4)-NETWORKDAYS.INTL(MAX(DATE($B$2,$B$1,1),A4),IF(B4="",DATE($B$2,$B$1+1,0),B4-1),"1111101",$G$1:$G$4)/2

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334