Công thức tính phép năm

Liên hệ QC
Việc tính ngày nghỉ vì có công việc nặng nhọc và độc hại thì không liên quan đến việc nhân viên đó có thâm niên tính tăng theo mỗi 5 năm hay không, nên theo tôi thấy bạn nên bỏ cột Y, vì thừa, tuy nhiên công thức của bạn đã tính sẵn cho nhân viên đó ngày nghỉ do làm công việc nặng nhọc độc hại theo tháng thực tế rồi, do vậy chuyển công thức đó thay vào công thức cột J.

Bổ sung thêm vào công thức tính thâm niên theo tháng thực tế:
Mã:
J7=ROUND(SUMIF($Z$7:$Z$47,D7,$AA$7)*((14/12-1)*I7),2)
K7=ROUND(INT(DATEDIF(F7,IF(G7,G7,$K$5),"y")/5)*DATEDIF(F7,IF(G7,G7,$K$5),"ym")/12,1)
Enter.

Thân

hi A ,
Theo như file anh gửi E thấy trường hợp 100025 ngày nghỉ việc là 1/1/2020, tức là năm 2020 chỉ làm 1 ngày . vậy tại sao vẫn có 0.7 thâm niên ???
 
hi A ,
Theo như file anh gửi E thấy trường hợp 100025 ngày nghỉ việc là 1/1/2020, tức là năm 2020 chỉ làm 1 ngày . vậy tại sao vẫn có 0.7 thâm niên ???
Có lẽ tôi đã nhầm ý của bạn! nên công thức ra kết quả không đúng.
1/ Thử tóm ý bạn như sau:
  • Thâm niên mỗi 5 năm sẽ được tăng thêm 1 ngày. (hỏi thêm: Có hay không có sự giới hạn: tổng số ngày tăng là bao nhiêu ngày thì không tăng nữa?)
  • Người đã đủ thâm niên:
    • Trường hợp rơi vào năm hiện hành (năm đang tính sổ): tính đến tháng trong năm hiện hành tròn đủ thâm niên mỗi 5 năm, sau đó tính tỉ lệ phần trăm của số các tháng còn lại đến cuối năm của năm hiện hành kể từ tháng đủ thâm niên mỗi 5 năm:
      • 'Số ngày nghỉ theo thâm niên' = 'Số kỳ thâm niên mỗi 5 năm' * 'Số các tháng còn lại' / 12
      • Vd: NV 100025, ký hợp đồng vào ngày 01/05/2010, giả sử năm hiện hành (2020) chưa nghỉ việc (ô G7 rỗng), thì đến ngày 01/05/2020 có 'Số kỳ thâm niên mỗi 5 năm' là 2, tức được thêm 2 ngày nghỉ phép thường niên, nhưng phải tính tỉ lệ theo số tháng còn lại, tức các tháng 5,6,7,8,9,10,11,12= 8 tháng. Vậy:
        • 'Số ngày nghỉ theo thâm niên'= 2 x 8 /12
    • Trường hợp rơi vào năm trước năm hiện hành: thì sẽ ghi nhận trọn 'Số kỳ thâm niên mỗi 5 năm'.
      • Như ví dụ trên, nhưng nếu năm hiện hành là năm 2021, hoặc 2022, 2023 thì sẽ ghi nhận 'Số ngày nghỉ theo thâm niên'=2 ngày
Có đúng như 'tiêu chí' của công y bạn chưa!?

2/ Ngoài ra, tôi để ý thấy bạn ghi tháng trên hàng L5: W5 có ngày 16 theo từng tháng, tức ý bạn muốn so:
  • Các ngày trước khi tính toán phải quy tròn: Nếu ngày nhỏ hơn ngày 16 thì tính từ tháng đó, tức tính từ ngày 01 tây đầu của tháng. Ngược lại, thì tính từ tháng sau, tức tính từ ngày 01 tây đầu tháng sau.
  • Vd: Ngày ký hợp đồng là ngày 12/01/2020, do ngày 12<16, nên nếu có phép sẽ được tính từ tháng 01, kể như bắt đầu tính từ ngày 01/01. Nếu ngày ký hợp đồng là ngày 21/01/2020, do ngày 21>16, nên tính ngày nghỉ phép sẽ bắt đầu tính từ 01/02.
3/ Để tính ngày nghỉ phép do công việc nặng nhọc, độc hại là 14 hay 16 ngày/năm, thay vì bạn ghi giá trị 1 trong danh sách cột Z7: AA47, bạn phân định ra luôn công việc nào thì được thêm 2 (tức: 2+12 =14 ngày), hoặc thêm 4 (tức 4+12 =16 ngày).

Nếu đúng như các ý trên, thì bạn điều chỉnh công thức như sau:
Mã:
J7=ROUND(((SUMIF($Z$7:$Z$47,D7,$AA$7)+12)/12-1)*I7,2)
K7=ROUND(INT(DATEDIF(EOMONTH(F7,(DAY(F7)<=15)*-1)+1,EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"y")/5)*IFERROR(DATEDIF(MAX(LOOKUP(EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,EDATE(EOMONTH(F7,(DAY(F7)<=15)*-1)+1,ROW($1:$10)*60)),$G$5),EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"m"),)/12,2)
Enter, fill xuống.

4/ Cuối cùng, tôi có chút thắc mắc: chức danh "CÔNG NHÂN ĐÓNG HÒM (KHO THÀNH PHẨM)", hiểu thì có hiểu, nhưng có chút áy náy bộ không còn chữ để thay thế hử bạn!? :)

Thân
 

File đính kèm

  • THEO DÕI PHÉP NV.xlsb
    46.4 KB · Đọc: 34
Có lẽ tôi đã nhầm ý của bạn! nên công thức ra kết quả không đúng.
1/ Thử tóm ý bạn như sau:
  • Thâm niên mỗi 5 năm sẽ được tăng thêm 1 ngày. (hỏi thêm: Có hay không có sự giới hạn: tổng số ngày tăng là bao nhiêu ngày thì không tăng nữa?)
  • Người đã đủ thâm niên:
    • Trường hợp rơi vào năm hiện hành (năm đang tính sổ): tính đến tháng trong năm hiện hành tròn đủ thâm niên mỗi 5 năm, sau đó tính tỉ lệ phần trăm của số các tháng còn lại đến cuối năm của năm hiện hành kể từ tháng đủ thâm niên mỗi 5 năm:
      • 'Số ngày nghỉ theo thâm niên' = 'Số kỳ thâm niên mỗi 5 năm' * 'Số các tháng còn lại' / 12
      • Vd: NV 100025, ký hợp đồng vào ngày 01/05/2010, giả sử năm hiện hành (2020) chưa nghỉ việc (ô G7 rỗng), thì đến ngày 01/05/2020 có 'Số kỳ thâm niên mỗi 5 năm' là 2, tức được thêm 2 ngày nghỉ phép thường niên, nhưng phải tính tỉ lệ theo số tháng còn lại, tức các tháng 5,6,7,8,9,10,11,12= 8 tháng. Vậy:
        • 'Số ngày nghỉ theo thâm niên'= 2 x 8 /12
    • Trường hợp rơi vào năm trước năm hiện hành: thì sẽ ghi nhận trọn 'Số kỳ thâm niên mỗi 5 năm'.
      • Như ví dụ trên, nhưng nếu năm hiện hành là năm 2021, hoặc 2022, 2023 thì sẽ ghi nhận 'Số ngày nghỉ theo thâm niên'=2 ngày
Có đúng như 'tiêu chí' của công y bạn chưa!?

2/ Ngoài ra, tôi để ý thấy bạn ghi tháng trên hàng L5: W5 có ngày 16 theo từng tháng, tức ý bạn muốn so:
  • Các ngày trước khi tính toán phải quy tròn: Nếu ngày nhỏ hơn ngày 16 thì tính từ tháng đó, tức tính từ ngày 01 tây đầu của tháng. Ngược lại, thì tính từ tháng sau, tức tính từ ngày 01 tây đầu tháng sau.
  • Vd: Ngày ký hợp đồng là ngày 12/01/2020, do ngày 12<16, nên nếu có phép sẽ được tính từ tháng 01, kể như bắt đầu tính từ ngày 01/01. Nếu ngày ký hợp đồng là ngày 21/01/2020, do ngày 21>16, nên tính ngày nghỉ phép sẽ bắt đầu tính từ 01/02.
3/ Để tính ngày nghỉ phép do công việc nặng nhọc, độc hại là 14 hay 16 ngày/năm, thay vì bạn ghi giá trị 1 trong danh sách cột Z7: AA47, bạn phân định ra luôn công việc nào thì được thêm 2 (tức: 2+12 =14 ngày), hoặc thêm 4 (tức 4+12 =16 ngày).

Nếu đúng như các ý trên, thì bạn điều chỉnh công thức như sau:
Mã:
J7=ROUND(((SUMIF($Z$7:$Z$47,D7,$AA$7)+12)/12-1)*I7,2)
K7=ROUND(INT(DATEDIF(EOMONTH(F7,(DAY(F7)<=15)*-1)+1,EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"y")/5)*IFERROR(DATEDIF(MAX(LOOKUP(EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,EDATE(EOMONTH(F7,(DAY(F7)<=15)*-1)+1,ROW($1:$10)*60)),$G$5),EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"m"),)/12,2)
Enter, fill xuống.

4/ Cuối cùng, tôi có chút thắc mắc: chức danh "CÔNG NHÂN ĐÓNG HÒM (KHO THÀNH PHẨM)", hiểu thì có hiểu, nhưng có chút áy náy bộ không còn chữ để thay thế hử bạn!? :)

Thân

Hi anh Hiệp,
Em đã đọc rất kỹ nội dung của anh, quả thật công thức rất ngắn gọn và rất chính xác.
Tuy nhiên do trình độ excel của em còn hạn hẹp nên chưa thể diễn giải được công thức trong cột I là =SUMPRODUCT(COUNTIF($L$5:$W$5,">"&CHOOSE({1;2},F7,IF(G7,G7,$K$5)))*{1;-1}).

Ngoài ra, cách hiểu của em về cách thức tính phép có khác một chút so với bạn ThuPhương95, em nêu ví dụ ở đây để anh hiểu và set up giúp em công thức với ạ:
Ví dụ: NV 100025, ký hợp đồng vào ngày 01/05/2015, giả sử năm hiện hành (2020) chưa nghỉ việc (ô G7 rỗng), thì đến ngày 01/05/2020 có 'Số kỳ thâm niên mỗi 5 năm' là 1, tức được thêm 01 ngày nghỉ phép thường niên;
- Số ngày phép tiêu chuẩn được hưởng: 14 ngày (lao động nặng nhọc) tính tương ứng tỷ lệ cho 04 tháng 1, 2, 3, 4
- Số ngày phép sau khi được hưởng thâm niên là: 14+1 = 15 ngày tính tương ứng tỷ lệ cho 08 tháng 5, 6, 7, 8, 9, 10, 11, 12
- Do đó số ngay phép được hưởng sẽ là:
Phép năm = round(14/12*3,0) + round((14+1)/12*8,0) = round(4.7,0) + round(10,0) = 5 + 10 =
15 ngày

Cơ sở tính này là em dựa vào quy định tại Điều 7 Nghị định 45/2013/NĐ-CP về cách tính số ngày ngày hàng năm đối với trường hợp làm không đủ năm: "Số ngày nghỉ hằng năm theo Khoản 2 Điều 114 của Bộ luật lao động được tính như sau: lấy số ngày nghỉ hằng năm cộng với số ngày được nghỉ tăng thêm theo thâm niên (nếu có), chia cho 12 tháng, nhân với số tháng làm việc thực tế trong năm để tính thành số ngày được nghỉ hằng năm; kết quả phép tính lấy tròn số hàng đơn vị, nếu phần thập phân lớn hơn hoặc bằng 0,5 thì làm tròn lên 01 đơn vị. "
Và công văn của Bộ LĐTBXH trả lời Công ty Alena Việt Nam về cách tính ngày phép, hộ có làm tròn từng phép tính, chứ không làm tròn tổng cuối cùng (CV tham khảo đính kèm theo).

Còn các tiêu chí còn lại cũng tương tự ạ:
- Các ngày trước khi tính toán phải quy tròn: Nếu ngày nhỏ hơn ngày 16 thì tính từ tháng đó, tức tính từ ngày 01 tây đầu của tháng. Ngược lại, thì tính từ tháng sau, tức tính từ ngày 01 tây đầu tháng sau.
Vd: Ngày ký hợp đồng là ngày 12/01/2020, do ngày 12<16, nên nếu có phép sẽ được tính từ tháng 01, kể như bắt đầu tính từ ngày 01/01. Nếu ngày ký hợp đồng là ngày 21/01/2020, do ngày 21>16, nên tính ngày nghỉ phép sẽ bắt đầu tính từ 01/02.
- Để tính ngày nghỉ phép do công việc nặng nhọc, độc hại là 14 hay 16 ngày/năm, em sẽ để ngày phép tiêu chuẩn của họ là 12 14 16 luôn (không để ngày phép tiêu chuẩn là 12 và +2 hoặc +4 nếu là công việc nặng nhọc)

Em cảm ơn sự giúp đỡ của anh.
 

File đính kèm

  • Can cu phap ly.png
    Can cu phap ly.png
    894.3 KB · Đọc: 14
Lần chỉnh sửa cuối:
a có thể giải thích rõ hơn cho em về công thức tính bên dưới được không ạ , e đang không hiểu lắm.
=SUMPRODUCT(COUNTIF($L$5:$W$5,">"&CHOOSE({1;2},F7,IF(G7,G7,$K$5)))*{1;-1})
Hi anh Hiệp,
Em đã đọc rất kỹ nội dung của anh, quả thật công thức rất ngắn gọn và rất chính xác.
Tuy nhiên do trình độ excel của em còn hạn hẹp nên chưa thể diễn giải được công thức trong cột I là =SUMPRODUCT(COUNTIF($L$5:$W$5,">"&CHOOSE({1;2},F7,IF(G7,G7,$K$5)))*{1;-1}).
1/ Trả lời chung cho hai bạn: @ThuPhương95@Minh.Tùng.Trần về công thức các bạn hỏi:
I7=SUMPRODUCT(COUNTIF($L$5:$W$5,">"&CHOOSE({1;2},F7,IF(G7,G7,$K$5)))*{1;-1})​
Do tôi hay thích thử công thức xử lý: 'gom các vùng riêng biệt vào một mảng', nên tôi thích ghi công thức đó để trải nghiệm. Thực tế, bạn có thể dùng công thức bình thường như dưới đây sẽ đơn giản và dễ hiểu hơn:​
I7= COUNTIF($L$5:$W$5,">"&F7) - COUNTIF($L$5:$W$5,">"&IF(G7,G7,$K$5))
Nhằm rút gọn lại công thức của bạn @ThuPhương95 trong file đã đưa lên diễn đàn: (các bạn xem công thức rút gọn với sự tương ứng màu của nó)
I7=​
IF($F7<L$5,1,0) + IF($F7<M$5,1,0) + IF($F7<N$5,1,0) + IF($F7<O$5,1,0) + IF($F7<P$5,1,0) + IF($F7<Q$5,1,0) + IF($F7<R$5,1,0) + IF($F7<S$5,1,0) + IF($F7<T$5,1,0) + IF($F7<U$5,1,0) + IF($F7<V$5,1,0) + IF($F7<W$5,1,0) -
(IF($G7<L$5,1,0) + IF($G7<M$5,1,0) + (IF($G7<N$5,1,0) + IF($G7<O$5,1,0) + IF($G7<P$5,1,0) + IF($G7<Q$5,1,0) + IF($G7<R$5,1,0) + IF($G7<S$5,1,0) + IF($G7<T$5,1,0) + IF($G7<U$5,1,0) + IF($G7<V$5,1,0) + IF($G7<W$5,1,0)))

2/ Điều chỉnh công thức theo yêu cầu của bạn @Minh.Tùng.Trần tại bài #44:
Tôi sẽ xem cột K là cột "Tổng cộng phép năm" mà nhân viên được hưởng trong niên độ hiện hành tính theo tiêu chí của công ty bạn:
Mã:
K7=SUMPRODUCT(ROUND(((INT(DATEDIF(EOMONTH(F7,(DAY(F7)<=15)*-1)+1,EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"y")/5)-{1,0})+SUM(I7:J7))*ABS({12,0}-IFERROR(DATEDIF(MAX(LOOKUP(EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,EDATE(EOMONTH(F7,(DAY(F7)<=15)*-1)+1,(ROW($1:$11)-1)*60)),$G$5),EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"m"),))/12,2))
Chỉ Enter, fill xuống.
Tôi làm tròn đến 2 đơn vị số lẻ để bạn dễ hình dung vấn đề và kiểm tra kết quả tính toán bằng tay. Nếu bạn muốn làm tròn như ý bạn yêu cầu, thì chỉnh lại số 2 ở vị trí cuối cùng của công thức thành số 0.

3/ Tôi tuy không có làm phần hành Nhân sự: tính công, tính phép, nhưng nhớ mang máng có quy định về giới hạn ngày tăng trong Nghị định, Thông tư nào đó. Nếu đúng vậy, thì các bạn phải điều chỉnh thêm hàm Min(<công thức>, 'Số giới hạn') vào công thức.

4/ Nói vui, Việt Nam có một 'rừng luật', nhưng mỗi doanh nghiệp, mỗi người... lại diễn đạt theo cách mình hiểu, nên lại đẻ ra thêm nhiều thứ, cuối cùng nhiều khi bị xử theo 'luật rừng'.

Thân
 

File đính kèm

  • THEO DÕI PHÉP NV.xlsb
    62.4 KB · Đọc: 27
Lần chỉnh sửa cuối:
. . . . . .
4/ Nói vui, Việt Nam có một 'rừng luật', nhưng mỗi doanh nghiệp, mỗi người... lại diễn đạt theo cách mình hiểu, nên lại đẻ ra thêm nhiều thứ, cuối cùng nhiều khi bị xử theo 'luật rừng'.

Bộ Luật LĐ (BL) cho phép người LĐ & người sử dụng thỏa thuận khác 1 khi người LĐ được hưởng những quyền lợi cao hơn BL qui định mà.
Giả dụ trong BL có ghi về phép năm là 10 ngày cho 5 năm đầu tiên
Nhưng Thỏa ước ghi 3 năm đầu tiên được 10 ngày, sau đó cứ mỗi năm làm việc được tăng 1 ngày phép thì OK mà!
(Chỉ có điều, chỉ 15 ngày nghỉ phép trong năm là có lương thôi, chẵng hạn)
 
2/ Điều chỉnh công thức theo yêu cầu của bạn @Minh.Tùng.Trần tại bài #44:
Tôi sẽ xem cột K là cột "Tổng cộng phép năm" mà nhân viên được hưởng trong niên độ hiện hành tính theo tiêu chí của công ty bạn:
Mã:
K7=SUMPRODUCT(ROUND(((INT(DATEDIF(EOMONTH(F7,(DAY(F7)<=15)*-1)+1,EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"y")/5)-{1,0})+SUM(I7:J7))*ABS({12,0}-IFERROR(DATEDIF(MAX(LOOKUP(EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,EDATE(EOMONTH(F7,(DAY(F7)<=15)*-1)+1,(ROW($1:$11)-1)*60)),$G$5),EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"m"),))/12,2))
Chỉ Enter, fill xuống.
Tôi làm tròn đến 2 đơn vị số lẻ để bạn dễ hình dung vấn đề và kiểm tra kết quả tính toán bằng tay. Nếu bạn muốn làm tròn như ý bạn yêu cầu, thì chỉnh lại số 2 ở vị trí cuối cùng của công thức thành số 0.

Hi anh,
Em cảm ơn anh đã cài đặt giúp và giải thích cặn kẽ.
Em cũng đã thử nhiều trường hợp ngày nghỉ thì hầu hết các trường hợp từ sau 16/04/2020 trở đi là đúng còn các trường hợp ngày nghỉ việc đến ngày 15/04/2020 thì tổng ngày hưởng phép ở cột K lại nhỏ hơn ngày phép thực tế được hưởng.
Em lấy ví dụ:
CN mã 100025 Lê Thị Hồng (row 7); nếu nghỉ ngày 16/02/2020 thì thực tế sẽ được 02 ngày phép (I7=2 đúng; J7=0.33 đúng) nhưng K7 thì =1.5; ngày phép tính < ngày phép thực tế được hưởng.
Tương tự với ngày nghỉ việc đến 15/04/2020 thì thực tế ngày phép hưởng là 3,5 làm tròn thành 4 ngày phép (I7 = 3 đúng; J7 = 0.5 đúng) nhưng K7 thì =2.67 < 4.
Em chưa hiểu cách công thức ở cột K7 hoạt động như thế nào, anh có thể giải thích giúp em được không ạ. Em cảm ơn anh.
Trân trọng./
 
Em chưa hiểu cách công thức ở cột K7 hoạt động như thế nào, anh có thể giải thích giúp em được không ạ. Em cảm ơn anh.
Trân trọng./
Điều chỉnh lại 1 chút:
Mã:
K7=SUMPRODUCT(ROUND(((INT(DATEDIF(EOMONTH(F7,(DAY(F7)<=15)*-1)+1,EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"y")/5)-{1,0})+(SUMIF($Z$7:$Z$47,D7,$AA$7)+12))*ABS(IF({1,0},DATEDIF(EOMONTH(MAX(F7,$G$5),(DAY(MAX(F7,$G$5))<=15)*-1)+1,EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"m"),0)-DATEDIF(MAX(LOOKUP(EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,EDATE(EOMONTH(F7,(DAY(F7)<=15)*-1)+1,(ROW($1:$11)-1)*60)),$G$5),EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"m"))/12,2))
Enter. fill xuống.

Trước khi giải thích, tôi đã phân ra từng đoạn công thức và gán vào Name, công thức sẽ còn lại:
Mã:
K7=SUMPRODUCT(ROUND(((SoKy5nam-{1,0})+(NgayPhepChuan))*ABS(IF({1,0},DATEDIF(NgayDauNienDo,NgayCuoi,"m"),)-DATEDIF(MAX(LOOKUP(NgayCuoi,EDATE(NgayVao,(ROW($1:$11)-1)*60)),$G$5),NgayCuoi,"m"))/12,2))
Enter. fill xuống.

Với:
  • NgayPhepChuan = SUMIF($Z$7:$Z$47,D7,$AA$7)+12
    • Tùy theo nhân viên mà NgayPhepChuan sẽ là 12, hoặc 14, hoặc 16
  • NgayVao = EOMONTH(F7,(DAY(F7)<=15)*-1)+1 -->Quy đổi theo so sánh trước hay sau ngày 16
    • Vd: Vào 12/04/2020 thì quy thành 01/04/2020. Vào 25/06/2015 quy thành 01/07/2015
  • NgayCuoi = EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1 -->Quy đổi theo so sánh trước hay sau ngày 16
  • NgayDauNienDo = EOMONTH(MAX(F7,$G$5),(DAY(MAX(F7,$G$5))<=15)*-1)+1 -->Quy đổi theo so sánh trước hay sau ngày 16
  • SoKy5nam = INT(NgayVao , NgayCuoi , "y")/5)
  • 'Số ngày phép chuẩn': phân thành 2 đoạn trước và sau tính đến thời điểm tháng đã tròn kỳ mỗi 5 năm
    • = SoKy5nam-{1,0})+(NgayPhepChuan)
    • Vd: Nhân viên 100025: (2 ô AI7: AJ7) = {14 ngày,15 ngày} --> Xem công thức và kết quả tại AI7: AJ102 file đính kèm để biết thêm chi tiết theo từng nhân viên.
  • 'Số tháng thực': trong niên độ phân thành 2 đoạn trước và sau tính đến thời điểm tháng đã tròn kỳ mỗi 5 năm
    • =ABS(IF({1,0},DATEDIF(NgayDauNienDo,NgayCuoi,"m"),)-DATEDIF(MAX(LOOKUP(NgayCuoi,EDATE(NgayVao,(ROW($1:$11)-1)*60)),$G$5),NgayCuoi,"m"))
    • Vd: Nhân viên 100025: (2 ô AP7: AQ7) = {4 tháng,1 tháng} --> Xem công thức và kết quả tại AP7: AQ102 file đính kèm để biết thêm chi tiết theo từng nhân viên.
  • Tổng phép được hưởng:
    • K7=SUMPRODUCT( Round( 'Số ngày phép chuẩn' * 'Số tháng thực' / 12 , 2 ) )
    • Vd: K7= SUMPRODUCT( Round( {14,15} * {4,1} / 12 , 2 ) ) = Sumproduct( Round( {14*4/12, 15*1/12} , 2) ) = Sumproduct( {4.67 , 1.25} ) = 5.92
Thân
 

File đính kèm

  • THEO DÕI PHÉP NV.xlsb
    82.9 KB · Đọc: 66
Điều chỉnh lại 1 chút:
Mã:
K7=SUMPRODUCT(ROUND(((INT(DATEDIF(EOMONTH(F7,(DAY(F7)<=15)*-1)+1,EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"y")/5)-{1,0})+(SUMIF($Z$7:$Z$47,D7,$AA$7)+12))*ABS(IF({1,0},DATEDIF(EOMONTH(MAX(F7,$G$5),(DAY(MAX(F7,$G$5))<=15)*-1)+1,EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"m"),0)-DATEDIF(MAX(LOOKUP(EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,EDATE(EOMONTH(F7,(DAY(F7)<=15)*-1)+1,(ROW($1:$11)-1)*60)),$G$5),EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1,"m"))/12,2))
Enter. fill xuống.

Trước khi giải thích, tôi đã phân ra từng đoạn công thức và gán vào Name, công thức sẽ còn lại:
Mã:
K7=SUMPRODUCT(ROUND(((SoKy5nam-{1,0})+(NgayPhepChuan))*ABS(IF({1,0},DATEDIF(NgayDauNienDo,NgayCuoi,"m"),)-DATEDIF(MAX(LOOKUP(NgayCuoi,EDATE(NgayVao,(ROW($1:$11)-1)*60)),$G$5),NgayCuoi,"m"))/12,2))
Enter. fill xuống.

Với:
  • NgayPhepChuan = SUMIF($Z$7:$Z$47,D7,$AA$7)+12
    • Tùy theo nhân viên mà NgayPhepChuan sẽ là 12, hoặc 14, hoặc 16
  • NgayVao = EOMONTH(F7,(DAY(F7)<=15)*-1)+1 -->Quy đổi theo so sánh trước hay sau ngày 16
    • Vd: Vào 12/04/2020 thì quy thành 01/04/2020. Vào 25/06/2015 quy thành 01/07/2015
  • NgayCuoi = EOMONTH(IF(G7,G7,$K$5),(DAY(IF(G7,G7,$K$5))<=15)*-1)+1 -->Quy đổi theo so sánh trước hay sau ngày 16
  • NgayDauNienDo = EOMONTH(MAX(F7,$G$5),(DAY(MAX(F7,$G$5))<=15)*-1)+1 -->Quy đổi theo so sánh trước hay sau ngày 16
  • SoKy5nam = INT(NgayVao , NgayCuoi , "y")/5)
  • 'Số ngày phép chuẩn': phân thành 2 đoạn trước và sau tính đến thời điểm tháng đã tròn kỳ mỗi 5 năm
    • = SoKy5nam-{1,0})+(NgayPhepChuan)
    • Vd: Nhân viên 100025: (2 ô AI7: AJ7) = {14 ngày,15 ngày} --> Xem công thức và kết quả tại AI7: AJ102 file đính kèm để biết thêm chi tiết theo từng nhân viên.
  • 'Số tháng thực': trong niên độ phân thành 2 đoạn trước và sau tính đến thời điểm tháng đã tròn kỳ mỗi 5 năm
    • =ABS(IF({1,0},DATEDIF(NgayDauNienDo,NgayCuoi,"m"),)-DATEDIF(MAX(LOOKUP(NgayCuoi,EDATE(NgayVao,(ROW($1:$11)-1)*60)),$G$5),NgayCuoi,"m"))
    • Vd: Nhân viên 100025: (2 ô AP7: AQ7) = {4 tháng,1 tháng} --> Xem công thức và kết quả tại AP7: AQ102 file đính kèm để biết thêm chi tiết theo từng nhân viên.
  • Tổng phép được hưởng:
    • K7=SUMPRODUCT( Round( 'Số ngày phép chuẩn' * 'Số tháng thực' / 12 , 2 ) )
    • Vd: K7= SUMPRODUCT( Round( {14,15} * {4,1} / 12 , 2 ) ) = Sumproduct( Round( {14*4/12, 15*1/12} , 2) ) = Sumproduct( {4.67 , 1.25} ) = 5.92
Thân
Dear anh Hiệp,
Em cảm ơn anh rất nhiều, thực sự anh là một người rất tâm, sẵn sàng chia sẻ với mọi người.
Công thức anh setup giúp, em có điều chỉnh lại một chút cho phù hợp và giờ nó đang chạy rất ok.
 
Dear anh Hiệp,
Em cảm ơn anh rất nhiều, thực sự anh là một người rất tâm, sẵn sàng chia sẻ với mọi người.
Công thức anh setup giúp, em có điều chỉnh lại một chút cho phù hợp và giờ nó đang chạy rất ok.
anh phan thế hiệp là lão sư phụ rồi đó nha bạn. không phải <U50 đâu .
 
Sao mình dùng hàm tính số tháng nó ra ko đúng kết quả nhỉ: = DATEDIF( 01/01/2020, 31/12/2020,M) - kết quả là 11 tháng
ai biết mình sai chỗ nào ko nhỉ
 
Chào các anh chị !
Nhờ các anh chị làm dùm em cái công thức tính phép này với ạ, trong file kèm là cách tính cũ trước đây là tính tỉ lệ theo ngày, bây giờ luật mới thay đổi cách tính phép nên thay đổi tính theo tháng, cách tính như sau:
- Làm đủ năm thì tính 12 ngày, thâm niên cứ 5 năm cộng thêm 1 ngày, nặng nhọc độc hại thì đc cộng thêm 1 hoặc 2 ngày tùy TC công việc, tính tỉ lệ phụ thuộc vào số tháng làm việc, nếu số tiếng ra số lẻ thì >=0.5 thì làm tròn thành 1, <0.49 thì làm tròn thành 0.
- Nhân viên mới vào xưởng trong tháng từ ngày 1~16 thì tính 1 ngày của tháng đó, từ ngày 17 trở về sau thì k đc tính, nghỉ việc trong tháng từ ngày 14 trở về sau thì đc tính 1 ngày và ngược lại.
- Nghỉ việc riêng+ không phép trong 1 năm cộng dồn trên 26 ngày sẽ không được tính phép năm.
Em cảm ơn nhiều ạ.!
 

File đính kèm

  • Bang thong ke phep.xlsx
    77.1 KB · Đọc: 19
Web KT
Back
Top Bottom