รบกวนพี่ๆและอาจารย์ ช่วยคิดระยะเวลาทำงาน หักเวลาพักและนอกเวลาทำงานครับ (มีไฟล์แนบ)

[nickname]

สวัสดีครับ

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

ผมแนบไฟล์ตัวอย่างมานะครับ
Column A จะเป็นวันเวลาเริ่มต้น
Column B จะเป็นวันเวลาสิ้นสุด
Column C เป็นส่วนที่ผมทำมาแล้ว คือระยะเวลาทั้งหมดหักวันหยุดที่ผมได้กำหนดขึ้นมา

สิ่งที่ผมอยากได้คือ จำนวนระยะเวลาทั้งหมดหักวันหยุด(Column C) หักเวลาพัก 12.00 - 13.00 และหักนอกเวลางาน 17.00 - 8.00

ยกตัวอย่างสมมุตินะครับ
เริ่มต้นคือ 21/06/2017 เวลา 23.00
สิ้นสุดคือ 22/06/2017 เวลา 14.00

ในกรณีนี้จะคิดเวลาเริ่มจาก 22/06/2017 8.00 - 12.00 และ 13.00 - 14.00 ระยะเวลาที่ใช้จะเท่ากับ 5 ชม.

ขอบคุณครับ

[nickname]

ผมได้ลองหาข้อมูลมาแล้วจากกระทู้เก่าๆ
และได้เอาworksheet มาลองทำดู ทำได้ในระดับนึง แต่ข้อมูลบางส่วนยังไม่ถูกต้องครับ

ผมเขียนสิ่งที่ควรจะเป็น ผลลัพธ์และเหตุผลในไฟล์แนบครับ

โดยข้อมูลทั่วไปสำหรับไฟล์นี้คือ
เวลาทำงาน 8.00 - 17.00
พักเที่ยง 12.00 - 13.00
นอกเหนือเวลางาน คือตั้งแต่ 17.00 - 8.00
วันหยุด เสาร์ อาทิตย์ วันหยุดอื่นมีตารางในไฟล์ครับ
ถ้าเวลาเริ่มต้นเกิดนอกเวลางานแหรือวันหยุด ปัดเป็น 8.00 ของวันทำงานถัดไป
ถ้าเวลาเริ่มต้นเกิดระหว่างพัก ปัดเป็น 13.00
ถ้าเวลาสิ้นสุดเกิดนอกเวลางานหรือวันหยุด ปัดเป็น สิ้นสุด 8.00 ของวันทำงานถัดไป
ถ้าเวลาสิ้นสุดเกิดระหว่างพัก ปัดเป็น 13.00

รบกวนอาจารย์และพี่ๆช่วยแก้สูตรให้ผมหน่อยครับ

[snasui]

ค่อย ๆ ถามตอบกันไปครับ

ผมตอบเป็นแนวทางเอาไว้ก่อนลองทำมาดู ติดตรงไหนค่อยถามกันต่อครับ

เพิ่มคอลัมน์ ดังนี้ครับ

1. เวลาทำงานทั้งหมดของวันเริ่ม
2. เวลาพักของวันเริ่ม
3. เวลาทำงานทั้งหมดของวันสิ้นสุด
4. เวลาพักของวันสิ้นสุด
5. วันหยุดทั้งหมด
   สูตรสำหรับวันหยุดทั้งหมดของบรรทัดที่ 4 คือ =SUMPRODUCT(--ISNUMBER(MATCH(วันหยุดนักขัตฤกษ์!$B$2:$B$18,ROW(INDIRECT(C4&":"&E4)),0)))

สำหรับข้อ 2 และ 3 จะมีได้ก็ต่อเมื่อวันเริ่มและวันสิ้นสุดเป็นคนละวัน

จากนั้นแนบไฟล์มาใหม่ สำหรับเวลาทั้งหมดนั้นจะทำการคำนวณใหม่ สูตรเดิมมีความซับซ้อนและไม่สะดวกต่อการตรวจสอบ แก้ไข ของผู้ใช้งานครับ