ขอความช่วยเหลือเรื่องสูตรคำนวนตามช่วงเวลา และเรื่องเวลากะค่ะ

[bowjung]

อยากทราบสูตรexel ในการคำนวณเรื่องเวลาค่ะ
1.กรณีผลิตงานตามช่วงเวลา ผลิตงานหลายช่วงค่ะ เริ่มตั้งแต่ 07.00 - 08.30 , 10.00 - 14.00 , 15.00-17.00 , 16.00 - 17.00 , 17.30 -20.30 ประมาณนี้ค่ะ
ตัวอย่าง
8:00 17:00 (ลบช่วงพัก หนึ่งชั่วโมง) 8 ชั่วโมง
13:00 17:00 (ผลิตงานหลังบ่ายโมง ไม่ต้องลบ 1 ชั่วโมง) 4 ชั่วโมง
13:00 - 19:00 (จะพักเบรค 30 นาที ตอนช่วงเวลา 17:00 - 17:30 ต้องหัก 30 นาทีออกค่ะ) ก็จะเหลือ 5 ชั่วโมงครึ่ง
17:30 - 20:30 (ไม่ต้องหัก 30 นาทีออกนะคะ)
กรณีนี้หมายถึงเวลาเดินเครื่องจักรค่ะ ว่าใช้เวลาผลิตกี่ชั่วโมง เพียงแต่ช่วง 12.00 – 13.00 และ 17.00 – 17.30 เป็นเวลาพักเบรก ซึ่งต้องหักเวลานี้ออกไป เนื่องจากคนพัก เครื่องจักรก็เดินเครื่องไม่ได้ ไม่ได้กำหนดช่วง OT. ค่ะ ต้องการแค่เวลาทำงานของเครื่องจักร โดยใส่เวลาเดินเครื่องจักรเข้าไป เช่น
เดินเครื่อง 07.00 – 09.30 ใช้เวลา 2 ชั่วโมงครึ่ง
10.00 – 14.00 ใช้เวลา 3 ชั่วโมง
13.00 – 16.00 ใช้เวลา 3 ชั่วโมง
16.00 – 20.30 ใช้เวลา 4 ชั่วโมง
รบกวนช่วยหน่อยนะคะ จะคิด Capacity คน และ เครื่องจักร


2.=IF(OR(ISBLANK(B11),ISBLANK(C11)),0,MIN(H$3,C11)-MAX(G$3,B11)-IF(C11<=TIME(12,0,0),0,(1/24))) กรณีทำกะเช้าใช้สูตรนี้ค่ะ
แต่พอมีการเข้ากะดึก คือช่วงเวลา 20.00 -05.00 ทำไม่ได้ค่ะ ทำ OT ช่วง 05.30 - 08.30 ค่ะ
ใช้สูตรอย่างไรคะอาจารย์ ให้ทำทั้งกะเช้า กะดึก ได้ค่ะ

รายละเอียดตามไฟล์แนบค่ะ

[snasui]

ลองตามนี้ครับ

ที่เซลล์ I18 คีย์

=IF(OR(I16="",I17=""),0,I17-I16-(IF(OR(AND(I16<12/24,I17<12/24),AND(I16>12/24,I17>12/24)),0,1/24)+IF(I17>=17.5/24,0.5/24,0)))

Enter > Copy ไปทางขวา

[bowjung]

ลองตามนี้ครับ

ที่เซลล์ I18 คีย์

=IF(OR(I16="",I17=""),0,I17-I16-(IF(OR(AND(I16<12/24,I17<12/24),AND(I16>12/24,I17>12/24)),0,1/24)+IF(I17>=17.5/24,0.5/24,0)))

Enter > Copy ไปทางขวา

ก่อนอื่นต้องขอบคุณอาจารย์คนควนมาก ๆ ค่ะ
เวลาอื่น OK หมดค่ะ

ติดอยู่อย่างเดียวค่ะ คือ พอใส่เวลา 17:30 เป็นต้นไป จะหัก 30 นาที ออกทุกครั้งเลยค่ะ
เช่น 17:30 - 19:30 ผลลัพธ์ที่ได้คือ 1:30 ค่ะ ซึ่งผลลัพธ์จะต้องได้ 2:00 ค่ะ
รบกวนอาจารย์ดูให้หน่อยนะคะ

[snasui]

ผมปรับมาให้ใหม่ครับ ลองแทนสูตรเดิมด้วยสูตรด้านล่าง ทดสอบเปลี่ยนค่า สังเกตดูผลครับ

=IF(OR(I16="",I17=""),0,I17-I16-(IF(OR(AND(I16<12/24,I17<12/24),AND(I16>12/24,I17>12/24)),0,1/24)+IF(OR(AND(I16>=17.5/24,I17>=17.5/24),AND(I16<=17.5/24,I17<=17.5/24)),0,0.5/24)))

Enter

[bowjung]

ผมปรับมาให้ใหม่ครับ ลองแทนสูตรเดิมด้วยสูตรด้านล่าง ทดสอบเปลี่ยนค่า สังเกตดูผลครับ

=IF(OR(I16="",I17=""),0,I17-I16-(IF(OR(AND(I16<12/24,I17<12/24),AND(I16>12/24,I17>12/24)),0,1/24)+IF(OR(AND(I16>=17.5/24,I17>=17.5/24),AND(I16<=17.5/24,I17<=17.5/24)),0,0.5/24)))

Enter

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

[bowjung]

ผมปรับมาให้ใหม่ครับ ลองแทนสูตรเดิมด้วยสูตรด้านล่าง ทดสอบเปลี่ยนค่า สังเกตดูผลครับ

=IF(OR(I16="",I17=""),0,I17-I16-(IF(OR(AND(I16<12/24,I17<12/24),AND(I16>12/24,I17>12/24)),0,1/24)+IF(OR(AND(I16>=17.5/24,I17>=17.5/24),AND(I16<=17.5/24,I17<=17.5/24)),0,0.5/24)))

Enter

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

อาจารย์พอดีได้นำสูตรไปใช้แล้วค่ะ โดยให้ลงเวลาการทำงานของเครื่องจักร แต่ติดที่ ถ้าใส่เวลาช่วงเช้าเวลาใดก็ตาม ถึงระหว่างเวลา 12:00 - 12:59
เช่น 09:00 - 12:00 ผลลัพธ์ที่ได้คือ 2:00 ค่ะ
10:00 - 12:10 ผลลัพธ์ที่ได้คือ 1:10 ค่ะ
คือสูตรจะหัก 1 ชั่วโมงทันที ถ้ากรณีนี้ต้องปรับสูตรยังไงคะ
รบกวนเรื่องเก่ามาเล่าใหม่ค่ะอาจารย์

[snasui]

กรณีคีย์เป็น 10:00 - 12:10 ผลลัพธ์ที่ต้องการให้แสดง 2:10 หรือ 2:00 ครับ

[bowjung]

กรณีคีย์เป็น 10:00 - 12:10 ผลลัพธ์ที่ต้องการให้แสดง 2:10 หรือ 2:00 ครับ

02:10 ค่ะอาจารย์

[snasui]

ลองทดสอบสูตรตามด้านล่างครับ

ที่ I18 คีย์

=IF(OR(I16="",I17=""),0,I17-I16-(IF(OR(AND(I16<13/24,I17<13/24),AND(I16>=13/24,I17>=13/24)),0,1/24)+IF(OR(AND(I16>=17.5/24,I17>=17.5/24),AND(I16<=17.5/24,I17<=17.5/24)),0,0.5/24)))

Enter > Copy ไปด้านขวา

[bowjung]

ลองทดสอบสูตรตามด้านล่างครับ

ที่ I18 คีย์

=IF(OR(I16="",I17=""),0,I17-I16-(IF(OR(AND(I16<13/24,I17<13/24),AND(I16>=13/24,I17>=13/24)),0,1/24)+IF(OR(AND(I16>=17.5/24,I17>=17.5/24),AND(I16<=17.5/24,I17<=17.5/24)),0,0.5/24)))

Enter > Copy ไปด้านขวา

ขอบคุณค่ะอาจารย์ ทดลองใช้แล้วได้ทุกกรณีของเวลาแล้วค่ะ