Tính Giờ công tăng ca trong thời gian thử việc và chính thức (1 người xem)

[hoanhnnc]

Mong Mọi người giúp công thức tính giờ thử việc, ngày thường, chủ nhật , lễ trong thời gian thử việc và chính thức , E nghĩ mãi không ra dùng sumif thì tính đến cuối tháng vì lương thử việc và chính thức khác nhau.

 

[quocgiacan]

Mong Mọi người giúp công thức tính giờ thử việc, ngày thường, chủ nhật , lễ trong thời gian thử việc và chính thức , E nghĩ mãi không ra dùng sumif thì tính đến cuối tháng vì lương thử việc và chính thức khác nhau.

Thử:

AN7=SUMPRODUCT($H7:$AL7,(WEEKDAY($H$6:$AL$6)>1)*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=1))
AO7=SUMPRODUCT($H7:$AL7,(WEEKDAY($H$6:$AL$6)>1)*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=2))
AP7=SUMPRODUCT($H7:$AL7,(WEEKDAY($H$6:$AL$6)=1)*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=1))
AQ7=SUMPRODUCT($H7:$AL7,(WEEKDAY($H$6:$AL$6)=1)*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=2))

Chỉ Enter.

Chúc bạn ngày vui.

 

[hoanhnnc]

Dạ thành thật cảm ơn nhiều ạ

 

[bebo021999]

Trước tiên bạn phải tạo vùng chứa ngày lễ trong năm, để nếu ngày làm thêm rơi vào ngày lễ thì tính 300% (tôi tạo mẫu trong cột AW)
Dòng 5, tôi dùng công thức để trả về ngày trong tuần:

H5 copy sang phải:
=CHOOSE(WEEKDAY(H6),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

* Số giờ làm thêm ngày lễ, thời gian chính thức:
AS7:
=SUMPRODUCT(SUMIFS($H7:$AL7,$H6:$AL6,">="&$F7,$H$6:$AL$6,$AW$3:$AW$5))
TG thử việc:
AR7:
=SUMPRODUCT(SUMIFS($H7:$AL7,$H$6:$AL$6,$AW$3:$AW$5))-AS7

* Số giờ làm thêm ngày CN, thời gian chính thức: (Tôi đã trừ đi ngày lễ nếu trùng với CN)
AQ7:
=SUMIFS($H7:$AL7,$H6:$AL6,">="&$F7,$H$5:$AL$5,"Sun")-SUMPRODUCT(SUMIFS($H7:$AL7,$H6:$AL6,">="&$F7,$H$5:$AL$5,"Sun",$H$6:$AL$6,$AW$3:$AW$5))
TG thử việc:
AP7:
=SUMIFS($H7:$AL7,$H6:$AL6,"<"&$F7,$H$5:$AL$5,"Sun")-SUMPRODUCT(SUMIFS($H7:$AL7,$H6:$AL6,"<"&$F7,$H$5:$AL$5,"Sun",$H$6:$AL$6,$AW$3:$AW$5))

Tương tự cho ngày thường.

Xem file đính kèm

 

[bebo021999]

Thử:

AN7=SUMPRODUCT($H7:$AL7,(WEEKDAY($H$6:$AL$6)>1)*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=1))
AO7=SUMPRODUCT($H7:$AL7,(WEEKDAY($H$6:$AL$6)>1)*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=2))
AP7=SUMPRODUCT($H7:$AL7,(WEEKDAY($H$6:$AL$6)=1)*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=1))
AQ7=SUMPRODUCT($H7:$AL7,(WEEKDAY($H$6:$AL$6)=1)*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=2))

Chỉ Enter.

Chúc bạn ngày vui.

Công thức gọn quá anh.
Anh làm thêm ngày lễ cho em học lóm với. CT em hơi dài.
Anh lưu ý trong tính lương, nếu ngày lễ đã hưởng 300% thì ngày đó không tính trong công thức của ngày thường (hoặc CN) nữa nhé

 

[quocgiacan]

Công thức gọn quá anh.
Anh làm thêm ngày lễ cho em học lóm với. CT em hơi dài.
Anh lưu ý trong tính lương, nếu ngày lễ đã hưởng 300% thì ngày đó không tính trong công thức của ngày thường (hoặc CN) nữa nhé

Cảm ơn @bebo021999 đã nhắc nhở, nếu không công thức bài #2 chỉ là 'rác vụn', thật sự đã hơi 'đoảng' chút!

Mình trao đổi giải thuật thôi nha! Cách của @bebo021999 cũng rất gọn rồi.
Tạo thêm sheet các ngày nghỉ lễ: giả sử NgayLe!$A$2:$A$10

AN7=SUMPRODUCT($H7:$AL7,(WEEKDAY($H$6:$AL$6)>1)*NOT(COUNTIF(NgayLe!$A$2:$A$10,$H$6:$AL$6))*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=1))
AO7=SUMPRODUCT($H7:$AL7,(WEEKDAY($H$6:$AL$6)>1)*NOT(COUNTIF(NgayLe!$A$2:$A$10,$H$6:$AL$6))*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=2))
AP7=SUMPRODUCT($H7:$AL7,(WEEKDAY($H$6:$AL$6)=1)*NOT(COUNTIF(NgayLe!$A$2:$A$10,$H$6:$AL$6))*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=1))
AQ7=SUMPRODUCT($H7:$AL7,(WEEKDAY($H$6:$AL$6)=1)*NOT(COUNTIF(NgayLe!$A$2:$A$10,$H$6:$AL$6))*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=2))
AR7=SUMPRODUCT($H7:$AL7,COUNTIF(NgayLe!$A$2:$A$10,$H$6:$AL$6)*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=1))
AS7=SUMPRODUCT($H7:$AL7,COUNTIF(NgayLe!$A$2:$A$10,$H$6:$AL$6)*(MATCH($H6:$AL6,N(OFFSET($D7,,{0,2})),1)=2))

Chỉ Enter.

Chúc @bebo021999 một ngày thiệt vui.

 

[hoanhnnc]

Cám ơn Anh Chị rất nhiều

 

[hoanhnnc]

Anh chị cho hỏi tại sao thay đổi tháng 10 thì báo lỗi #NA

 

[hoanhnnc]

Xin Giúp do tại sao: khi thay đổi tháng thì công thức báo lỗi #N/A ạ