ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค

[want09]

สมมุติว่า
Line หยุดทำงานตั้งแต่เวลา 9:30 น- 10:30 น = ต้องเสียเวลาไป 60 นาที แต่เนื่องจาก 10:00 น-10:10 น เป็นเวลาพักเบรคนั่นเท่ากับว่า เสียเวลาไป 50 นาทีเท่านั้น
ผมจะต้องใช้เขียนสูตรอย่างไร ให้ลบช่วงเวลาเวลาพักเบรคออกไป
นำไปเขียนเป็นสูตรให้ได้ถูกต้อง
ข้างล่างนี้คือเวลาพักเบรคของเเต่ละกะ


กะกลางวัน----------------------กะกลางคืน
8:00 - 8:10-------------------20:00 - 20:10
10:00 - 10:10 ---------------22:00 - 22:10
11:30 - 12:20----------------00:00 - 00:50
15:00 - 15:10 ---------------03:00 - 03:10
16:50 - 17:00 ---------------4:50 - 5:00
17:00 - 17:30 ---------------5:00 - 5:30
19:50 - 20:00 ---------------7:50 - 8:00

ผมใช้สูตรนี้ครับ
=(D3-B3)*1440

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


ขอบคุณล่วงหน้าครับ

[bank9597]

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

ที่ E2 คีย์

Code: Select all

=IF(IF(B2<="20:00:00"+0,1,2)=1,IF($D2<=INDEX($H$3:$H$9,MATCH(1,IF($G$3:$G$9>=$B2,1),0)),$D2-$B2,($D2-$B2)-TIME(0,INDEX($I$3:$I$9,MATCH(1,IF($G$3:$G$9>=$B2,1),0)),0)),IF($D2<=INDEX($K$3:$K$9,MATCH(1,IF($J$3:$J$9>=$B2,1),0)),$D2-$B2,($D2-$B2)-TIME(0,INDEX($L$3:$L$9,MATCH(1,IF($J$3:$J$9>=$B2,1),0)),0)))

กด ctrl+shift+enter คัดลอกลงมา

[want09]

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

ที่ E2 คีย์

Code: Select all

=IF(IF(B2<="20:00:00"+0,1,2)=1,IF($D2<=INDEX($H$3:$H$9,MATCH(1,IF($G$3:$G$9>=$B2,1),0)),$D2-$B2,($D2-$B2)-TIME(0,INDEX($I$3:$I$9,MATCH(1,IF($G$3:$G$9>=$B2,1),0)),0)),IF($D2<=INDEX($K$3:$K$9,MATCH(1,IF($J$3:$J$9>=$B2,1),0)),$D2-$B2,($D2-$B2)-TIME(0,INDEX($L$3:$L$9,MATCH(1,IF($J$3:$J$9>=$B2,1),0)),0)))

กด ctrl+shift+enter คัดลอกลงมา

ขอบคุณครับ

ลองใช้สูตรนี้ดูเเล้วใช้ได้ครับผลครับ
ผมขอเพิ่มเติมอีก ข้อนะครับ
คือใน เวลาทำงานของกะกลางวันไม่มีปัญหาครับ เเต่ถ้าลงเวลาของกะกลางคืนเเล้ว มันขึ้น #N/A ครับ
สมมุติว่า
Line หยุดทำงานตั้งแต่ วันที่9/10/56 เวลา 23:00 น- วันที่ 10-10-56 เวลา1:30 น
ซึ่งเป็นเวลาที่ข้ามวันใหม่ไปเเล้วครับ ต้องทำอย่างไรต่อดี ครับ

[bank9597]

Line หยุดทำงานตั้งแต่ วันที่9/10/56 เวลา 23:00 น- วันที่ 10-10-56 เวลา1:30 น
ซึ่งเป็นเวลาที่ข้ามวันใหม่ไปเเล้วครับ ต้องทำอย่างไรต่อดี ครับ

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


ในกรณีที่หยุดข้ามวัน สูตรย่อมทำงานผิดพลาดแน่นอน เพราะคุณ want09 ไม่ได้ระบุวันที่กำกับมาให้ด้วย

ดังนั้น ให้ลองกำกับวันที่มาให้ด้วยครับ เดี๋ยวจะปรับสูตรให้อีกครั้งนึง


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

[want09]

Line หยุดทำงานตั้งแต่ วันที่9/10/56 เวลา 23:00 น- วันที่ 10-10-56 เวลา1:30 น
ซึ่งเป็นเวลาที่ข้ามวันใหม่ไปเเล้วครับ ต้องทำอย่างไรต่อดี ครับ

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


ในกรณีที่หยุดข้ามวัน สูตรย่อมทำงานผิดพลาดแน่นอน เพราะคุณ want09 ไม่ได้ระบุวันที่กำกับมาให้ด้วย

ดังนั้น ให้ลองกำกับวันที่มาให้ด้วยครับ เดี๋ยวจะปรับสูตรให้อีกครั้งนึง


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

รับทราบครับ
งั้นเดี๋ยวผมจะลงรายละเอียดที่ต้องใช้จริงๆดู เพราะถ้าผมเอาสูตรแบบครึ่งๆกลางๆ ไปใช้เดี่ยวมันจะไม่ได้สูตรที่ถูกต้อง

[want09]

Line หยุดทำงานตั้งแต่ วันที่9/10/56 เวลา 23:00 น- วันที่ 10-10-56 เวลา1:30 น
ซึ่งเป็นเวลาที่ข้ามวันใหม่ไปเเล้วครับ ต้องทำอย่างไรต่อดี ครับ

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


ในกรณีที่หยุดข้ามวัน สูตรย่อมทำงานผิดพลาดแน่นอน เพราะคุณ want09 ไม่ได้ระบุวันที่กำกับมาให้ด้วย

ดังนั้น ให้ลองกำกับวันที่มาให้ด้วยครับ เดี๋ยวจะปรับสูตรให้อีกครั้งนึง


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

รับทราบครับ
งั้นเดี๋ยวผมจะลงรายละเอียดที่ต้องใช้จริงๆดู เพราะถ้าผมเอาสูตรแบบครึ่งๆกลางๆ ไปใช้เดี่ยวมันจะไม่ได้สูตรที่ถูกต้อง

อันนี้คือรายละเอียดครับ ตามเอกสารข้างล่างนี้ เเทบสีเขียวนี้คือ Sheet ใช้งานจริงครับ ส่วน สีเเดงนั้น Sheet ที่คุณ bank9597 ทำให้ผมลองกรอกข้อมูลดูเเล้วก็ยังพบว่ามีส่วนที่ผิดอยู่เช่น

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

[bank9597]

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

เอาข้อมูลกะกลางคืนมาต่อที่ G10 (20:00น เป็นต้นไป)

ที่ I3 คีย์ =H3-G3 คัดลอกลงมา

ที่ J2 คีย์

Code: Select all

=MIN(B2,IF(MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))="00:00:00"+0,"24:00:00"+0,MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))))

กด ctrl+shift+enter คัดลอกลงมา

ที่ K2 คีย์

Code: Select all

=MAX(IF(AND(D2>="00:00:00"+0,D2<="08:00:00"+0),D2+1,D2),IF(AND(MIN(IF(MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))=$G$3:$G$16,$H$3:$H$16))>="00:00:00"+0,MIN(IF(MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))=$G$3:$G$16,$H$3:$H$16))<="08:00:00"+0),MIN(IF(MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))=$G$3:$G$16,$H$3:$H$16))+1,MIN(IF(MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))=$G$3:$G$16,$H$3:$H$16))))

กด ctrl+shift+enter คัดลอกลงมา

ที่ L2 คีย์

Code: Select all

=IF(MAX(IF($G$3:$G$16<=D2,$G$3:$G$16))=MIN(IF($G$3:$G$16>=B2,$G$3:$G$16)),0,MAX(IF($G$3:$G$16<=D2,$G$3:$G$16)))

กด ctrl+shift+enter คัดลอกลงมา

ที่ M2 คีย์ =IF(L2=0,0,MAX(IF($G$3:$G$16<$K2,$G$3:$G$16))) กด ctrl+shift+enter คัดลอกลงมา

ที่ N1 คีย์ =SUM(IF($G$3:$G$16>MIN(IF($G$3:$G$16>=B2,$G$3:$G$16)),IF($G$3:$G$16<=M2,$I$3:$I$16))) กด ctrl+shift+enter คัดลอกลงมา

ที่ E2 คีย์ =(K2-J2)-MIN(IF(MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))=$G$3:$G$16,$I$3:$I$16))-N2 กด ctrl+shift+enter คัดลอกลงมา

[want09]

ขอบคุณครับ คุณ bank9597 ที่ช่วยสร้างสูตรให้ผม
วันนี้ผมลองโหลดไฟล์สูตรเอาไปใช้ ลองทำดูตั้งเเต่ 9 โมง ผมยังทำไม่ได้เลย แล้วพอใส่ค่าลงไปเเล้ว มีทั้งผิดและถูก ก็ไม่ขึ้นเลยตามภาพข้างล่างพร้อมไฟล์แนบมาด้วยครับ ช่วยเเก้ไขให้ด้วยนะครับ

ไฟล์ก่อนหน้านี้ผิดอันนะครับ ใช้ไฟล้นี้ครับ ขออภัยด้วยครับ

[bank9597]

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

2. เงื่อนไขมีความซับซ้อนมาก ไม่สามารถจะคำนวนโดยใช้สูตรเดียวได้ ต้องมีคอลัมน์อื่นๆมาช่วยแจกแจงด้วย

[want09]

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

2. เงื่อนไขมีความซับซ้อนมาก ไม่สามารถจะคำนวนโดยใช้สูตรเดียวได้ ต้องมีคอลัมน์อื่นๆมาช่วยแจกแจงด้วย

ผมอัพไฟล์ผิดอันครับ ขออภัยด้วย ต้องเป็นไฟล์นี้ครับ

[bank9597]

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

2. เงื่อนไขมีความซับซ้อนมาก ไม่สามารถจะคำนวนโดยใช้สูตรเดียวได้ ต้องมีคอลัมน์อื่นๆมาช่วยแจกแจงด้วย

ผมอัพไฟล์ผิดอันครับ ขออภัยด้วย ต้องเป็นไฟล์นี้ครับ

ผมก็จนปัญญาอยู่เหมือนกันครับ เลยต้องใช้วิธีที่เข้าใจยากและสูตรยาวมากๆ มาใช้ ลองดูครับว่าถูกต้องหรือไม่

ที่ตารางกะกลางคืน ต้องบวก 1 ให้กับเวลาตั้งแต่ 00:00 น. ถึง 08:00 น. ครับ

ที่ P4 คีย์

Code: Select all

=(MAX(O4,IFERROR(INDEX($C$4:$C$17,MATCH(MIN(M4,IFERROR(INDEX($B$4:$B$17,MATCH(1,IF(HOUR(M4)=HOUR($B$4:$B$17),1),0)),IF(HOUR(M4)>=0,IF(HOUR(M4)<8,M4+1,M4)))),$B$4:$B$17,0)),IF(HOUR(O4)>=0,IF(HOUR(O4)<8,O4+1,O4)))))-(MIN(M4,IFERROR(INDEX($B$4:$B$17,MATCH(1,IF(HOUR(M4)=HOUR($B$4:$B$17),1),0)),IF(HOUR(M4)>=0,IF(HOUR(M4)<8,M4+1,M4)))))-((SUM(IF(HOUR(M4)=HOUR($B$4:$B$17),$D$4:$D$17))+SUM(IF(HOUR(O4)<>HOUR(M4),IF(HOUR(O4)<>17,IF(HOUR(O4)<>20,IF(HOUR(O4)<>5,IF(HOUR(O4)=HOUR($B$4:$B$17),$D$4:$D$17)))))))+(SUM(IF(HOUR($B$4:$B$17)>HOUR(M4),IF(HOUR($B$4:$B$17)<HOUR(O4),$D$4:$D$17)))))

กด ctrl+shift+enter คัดลอกลงมา

ส่วนในหัวข้อื่นๆ ให้ปรับชื่อเซลล์ ไปตามนั้นครับ

[want09]

ที่บอกว่าให้ ตารางกะกลางคืน ต้องบวก 1 ให้กับเวลาตั้งแต่ 00:00 น. ถึง 08:00 น.
เขียนยังไงครับ ผมลองเขียนเเล้วมันไม่ขึ้นเลย

เเล้วอีกเรื่องครับที่ คอลัมน์ P ก็ยังไม่ถูกครับ ผมไม่เก่งเรื่องสูตรครับ ยิ่งยาวๆๆอย่างนี้ มึนเลยครับผม แต่ผมก็ขอบคุณมากๆๆ ที่ช่วยผม

[snasui]

จากไฟล์แนบด้านบน ช่วยอธิบายวิธีคำนวณที่ Q25, Q27 คำตอบที่ถูกต้องคือ 2:20:00 และ 3:20:00 ตามลำดับ มีวิธีคิดอย่างไรถึงได้ค่าเท่านั้นครับ

[bank9597]

ที่บอกว่าให้ ตารางกะกลางคืน ต้องบวก 1 ให้กับเวลาตั้งแต่ 00:00 น. ถึง 08:00 น.
เขียนยังไงครับ ผมลองเขียนเเล้วมันไม่ขึ้นเลย

แม้จะบวก 1 เข้าไปแล้ว ค่าที่แสดงก็จะเป็นเท่าเดิมครับ เมื่อเราบวก 1 แล้ว เอาเมาส์ไปคลิ๊กที่เวลา 00:00 น แล้วดูที่แถบสูตร ก็จะเห็นค่าเป็น 1/1/1900 00:00:00 ซึ่งเท่ากับเลยมา 1 วันแล้วครับ

เเล้วอีกเรื่องครับที่ คอลัมน์ P ก็ยังไม่ถูกครับ

ผมเช็คตามเงื่อนไขที่คุณให้มาแล้ว ค่อนข้างมั่นใจว่าถูกต้องแล้วครับ

หากข้อไหนยังไม่ถูก ลองให้ตัวอย่างคำตอบที่ถูกต้องมาดูอีกทีครับ

[want09]

จากไฟล์แนบด้านบน ช่วยอธิบายวิธีคำนวณที่ Q25, Q27 คำตอบที่ถูกต้องคือ 2:20:00 และ 3:20:00 ตามลำดับ มีวิธีคิดอย่างไรถึงได้ค่าเท่านั้นครับ

ตามภาพข้างล่างนี้เลยครับอาจารย์

[want09]

ที่บอกว่าให้ ตารางกะกลางคืน ต้องบวก 1 ให้กับเวลาตั้งแต่ 00:00 น. ถึง 08:00 น.
เขียนยังไงครับ ผมลองเขียนเเล้วมันไม่ขึ้นเลย

แม้จะบวก 1 เข้าไปแล้ว ค่าที่แสดงก็จะเป็นเท่าเดิมครับ เมื่อเราบวก 1 แล้ว เอาเมาส์ไปคลิ๊กที่เวลา 00:00 น แล้วดูที่แถบสูตร ก็จะเห็นค่าเป็น 1/1/1900 00:00:00 ซึ่งเท่ากับเลยมา 1 วันแล้วครับ

เเล้วอีกเรื่องครับที่ คอลัมน์ P ก็ยังไม่ถูกครับ

ผมเช็คตามเงื่อนไขที่คุณให้มาแล้ว ค่อนข้างมั่นใจว่าถูกต้องแล้วครับ

หากข้อไหนยังไม่ถูก ลองให้ตัวอย่างคำตอบที่ถูกต้องมาดูอีกทีครับ

ลองกรอกข้อมูลเพิ่มไปดู ยังแปลกๆๆอยู่ครับ

[want09]

ที่บอกว่าให้ ตารางกะกลางคืน ต้องบวก 1 ให้กับเวลาตั้งแต่ 00:00 น. ถึง 08:00 น.
เขียนยังไงครับ ผมลองเขียนเเล้วมันไม่ขึ้นเลย

แม้จะบวก 1 เข้าไปแล้ว ค่าที่แสดงก็จะเป็นเท่าเดิมครับ เมื่อเราบวก 1 แล้ว เอาเมาส์ไปคลิ๊กที่เวลา 00:00 น แล้วดูที่แถบสูตร ก็จะเห็นค่าเป็น 1/1/1900 00:00:00 ซึ่งเท่ากับเลยมา 1 วันแล้วครับ

เเล้วอีกเรื่องครับที่ คอลัมน์ P ก็ยังไม่ถูกครับ

ผมเช็คตามเงื่อนไขที่คุณให้มาแล้ว ค่อนข้างมั่นใจว่าถูกต้องแล้วครับ

หากข้อไหนยังไม่ถูก ลองให้ตัวอย่างคำตอบที่ถูกต้องมาดูอีกทีครับ

ผมลองกรอกใส่ข้อมูลเวลาเพิ่มไปอีกที รู้สึกว่ายังแปลกๆๆ อยู่ครับ

[snasui]

ลองดูตัวอย่างคำตอบที่คอลัมน์ R ของชีท Report ส่วนชีท Config เป็นการทำตารางเวลาพักโดยนำข้อมูลในตารางด้านขวามาสรุปใส่ตารางด้านซ้ายเพื่อทำเป็นเวลาพักสะสมแล้วค่อย Lookup ไปใช้ครับ

[want09]

ลองดูตัวอย่างคำตอบที่คอลัมน์ R ของชีท Report ส่วนชีท Config เป็นการทำตารางเวลาพักโดยนำข้อมูลในตารางด้านขวามาสรุปใส่ตารางด้านซ้ายเพื่อทำเป็นเวลาพักสะสมแล้วค่อย Lookup ไปใช้ครับ

อีกนิดนะครับ
ข้อ1
ในช่อง K กรณี ไม่มีข้อมูลลง มันขึ้น เป็น #N/A ครับ
ผมต้องการให้มันขึ้น - หรือไม่ขึ้นเลยก็ได้ครับเป็นช่องว่างๆ เพราะผมต้อง Sum ข้อมูลทั้งหมดในช่องสีเหลือง
เอาไปใช้งานครับ
ข้อ2
ที่ช่อง G5-G35 อยากให้เป็นอย่างนี้ครับ เวลาผมลงข้อมูลที่ BP1 เมื่อลงเเล้วต้องการให้ วันในช่องG5-G35เป็นไปตามรอบของปฎิทินที่ระบุ วันจันทร์-อาทิตย์ด้วยครับ
ช่วยเขียนสูตรให้ด้วยครับ
ข้อ3
ถ้าวันไหนตรงกับวันอาทิตย์ ทำอย่างไรให้กลายเป็นสีเดียวกันทั้งแถวครับ

ขอบคุณครับ

[snasui]

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

1. การซ่อนค่า Error สามารถคีย์สูตรที่ K5 เป็นด้านล่าง
   =If(I5="","",J5-H5+(J5<H5)-IF(AND(J5>H5,INDEX(Config!$B$3:$B$16,MATCH(1,INDEX(1/(Config!$B$3:$B$16>=H5),0),0))>=J5),0,INDEX(Config!$C$3:$P$16,MATCH(1,INDEX(1/(H5<=Config!$B$3:$B$16),0),0),MATCH(1,INDEX(1/(J5>=Config!$C$2:$P$2),0)))))
   Enter > Copy ลงด้านล่าง
2. การ Run ค่าวันที่จากเซลล์ BP1 ที่เซลล์ G5 คีย์สูตร
   =$BP$1+ROWS(G$5:G5)-1
   Enter > Copy ลงด้านล่าง
3. การจัด Format วันอาทิตย์
   คลุม F5:BO35 > เข้าเมนู Home > Conditoinal Formatting > Manage Rule > เลือก Rule ที่ต้องการ > Edit Rule > เลือก Use a formula to determine whice cells to format > ในช่อง Format values where this formula is true: คีย์สูตร =$F5="วันอาทิตย์" > OK > ตรง Appies to กำหนดช่วงเป็น =$F$5:$BO$35 > Apply > OK