Snasui Excel Forum

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

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

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 ลงด้านล่าง

parakorn wrote: Wed Jul 17, 2019 1:56 pm เริ่มจากใส่ หัวข้อดังนี้ครับ

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 ลงด้านล่าง

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

เดานะ
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)

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