รบกวนขอสูตร คำนวณค่ากะ ค่ะ

[MDzboom]

เรียน ทุกท่าน
รบกวนขอสูตรคำนวณค่ากะ ค่ะ
เพราะมีเงื่อนไขเยอะ ปัจจุบันต้องคำนวณเองข้างนอก
เลยทำให้ค่อนข้างใช้เวลานาน
รบกวนพี่ๆ ช่วยดูสูตรให้หน่อยค่ะ
ขอบคุณค่ะ
แนบไฟล์ตัวอย่างมาให้ค่ะ

[parakorn]

เริ่มจากใส่ หัวข้อดังนี้ครับ

1. ที่ K1 คีย์
   Start
2. ที่ L1 คีย์
   End
3. ที่ M1 คีย์
   Hour
4. ที่ N1 คีย์
   On_Work
5. ที่ O1 คีย์
   Hour_OT
6. ที่ P1 คีย์
   Pays_FullTime
7. ที่ Q1 คีย์
   Pays_OT

แก้ J5 จาก 8.00-16.00 เป็น 08.00-16.00

แล้วใส่สูตรดังนี้ครับ

1. ที่ K2 คีย์
   =(E2&" "&TEXT(IF(MATCH(SUBSTITUTE(SUBSTITUTE(D2," ",""),":","."),$J$15:$J$16,0)=2,IF(--(F2)<0.833333333333333,0.833333333333333,--(F2)),IF(MATCH(SUBSTITUTE(SUBSTITUTE(D2," ",""),":","."),$J$15:$J$16,0)=1,IF(--(F2)<0.333333333333333,0.333333333333333,--(F2)),)),"hh:mm"))+0
   Enter > Copy ลงด้านล่าง
2. ที่ L2 คีย์
   =(G2&" "&TEXT(H2,"hh:mm"))-0
   Enter > Copy ลงด้านล่าง
3. ที่ M2 คีย์
   =L2-K2
   Enter > Copy ลงด้านล่าง
4. ที่ N2 คีย์
   0.333333333333333
   Enter > Copy ลงด้านล่าง
5. ที่ O2 คีย์
   =IF(M2-N2<=0,0,M2-N2)
   Enter > Copy ลงด้านล่าง
6. ที่ P2 คีย์
   =IF(MATCH(SUBSTITUTE(SUBSTITUTE(D2," ",""),":","."),$J$15:$J$16,0)=2,IF(AND(K2<=(--(E2&" "&TEXT(0.833333333333333,"hh:mm"))),L2>=(--(G2&" "&TEXT(0.166666666666667,"hh:mm")))),40,20),IF(MATCH(SUBSTITUTE(SUBSTITUTE(D2," ",""),":","."),$J$15:$J$16,0)=1,0))
   Enter > Copy ลงด้านล่าง
7. ที่ Q2 คีย์
   =IF(O2<0.145821759259259,0,IF(MATCH(SUBSTITUTE(SUBSTITUTE(D2," ",""),":","."),$J$15:$J$16,0)=2,IF(AND(K2<=(--(E2&" "&TEXT(0.833333333333333,"hh:mm"))),L2>=(--(G2&" "&TEXT(0.166666666666667,"hh:mm")))),20,10),IF(MATCH(SUBSTITUTE(SUBSTITUTE(D2," ",""),":","."),$J$15:$J$16,0)=1,IF(AND(K2<=(--(E2&" "&TEXT(0.333333333333333,"hh:mm"))),L2>=(--(G2&" "&TEXT(0.666666666666667,"hh:mm")))),20,10))))
   Enter > Copy ลงด้านล่าง

[MDzboom]

เริ่มจากใส่ หัวข้อดังนี้ครับ

1. ที่ K1 คีย์
   Start
2. ที่ L1 คีย์
   End
3. ที่ M1 คีย์
   Hour
4. ที่ N1 คีย์
   On_Work
5. ที่ O1 คีย์
   Hour_OT
6. ที่ P1 คีย์
   Pays_FullTime
7. ที่ Q1 คีย์
   Pays_OT

แก้ J5 จาก 8.00-16.00 เป็น 08.00-16.00

แล้วใส่สูตรดังนี้ครับ

1. ที่ K2 คีย์
   =(E2&" "&TEXT(IF(MATCH(SUBSTITUTE(SUBSTITUTE(D2," ",""),":","."),$J$15:$J$16,0)=2,IF(--(F2)<0.833333333333333,0.833333333333333,--(F2)),IF(MATCH(SUBSTITUTE(SUBSTITUTE(D2," ",""),":","."),$J$15:$J$16,0)=1,IF(--(F2)<0.333333333333333,0.333333333333333,--(F2)),)),"hh:mm"))+0
   Enter > Copy ลงด้านล่าง
2. ที่ L2 คีย์
   =(G2&" "&TEXT(H2,"hh:mm"))-0
   Enter > Copy ลงด้านล่าง
3. ที่ M2 คีย์
   =L2-K2
   Enter > Copy ลงด้านล่าง
4. ที่ N2 คีย์
   0.333333333333333
   Enter > Copy ลงด้านล่าง
5. ที่ O2 คีย์
   =IF(M2-N2<=0,0,M2-N2)
   Enter > Copy ลงด้านล่าง
6. ที่ P2 คีย์
   =IF(MATCH(SUBSTITUTE(SUBSTITUTE(D2," ",""),":","."),$J$15:$J$16,0)=2,IF(AND(K2<=(--(E2&" "&TEXT(0.833333333333333,"hh:mm"))),L2>=(--(G2&" "&TEXT(0.166666666666667,"hh:mm")))),40,20),IF(MATCH(SUBSTITUTE(SUBSTITUTE(D2," ",""),":","."),$J$15:$J$16,0)=1,0))
   Enter > Copy ลงด้านล่าง
7. ที่ Q2 คีย์
   =IF(O2<0.145821759259259,0,IF(MATCH(SUBSTITUTE(SUBSTITUTE(D2," ",""),":","."),$J$15:$J$16,0)=2,IF(AND(K2<=(--(E2&" "&TEXT(0.833333333333333,"hh:mm"))),L2>=(--(G2&" "&TEXT(0.166666666666667,"hh:mm")))),20,10),IF(MATCH(SUBSTITUTE(SUBSTITUTE(D2," ",""),":","."),$J$15:$J$16,0)=1,IF(AND(K2<=(--(E2&" "&TEXT(0.333333333333333,"hh:mm"))),L2>=(--(G2&" "&TEXT(0.666666666666667,"hh:mm")))),20,10))))
   Enter > Copy ลงด้านล่าง

ขอบคุณนะค่ะ เดี๋ยวใส่สูตรตามที่พี่ทำให้ ถ้าไม่ได้ยังไงอาจจะขอรบกวนอีกนะค่ะ
ูขอบคุณมากๆค่ะ

[Bo_ry]

เดานะ
I2
=((MATCH(MIN((D2>"2")+RIGHT(D2,5),G2-E2+H2)-MAX(F2,LEFT(D2,5)),{0,1,7.5}/24)+MATCH(G2-E2+H2-((D2>"2")+RIGHT(D2,5)),{-9,3.5,7.5}/24)-2)*20)

[menem]

ผมเขียนสูตรแบบช่องเดียวจบไม่ได้ เลยใช้ช่องอื่น ๆ ช่วยในการคำนวณแต่ละขั้นแทนครับ