คำนวณเวลา โดยไม่นับเวลาที่อยู่นอกเวลาทำงาน และ ไม่นับวันหยุด

[tommy_jerry]

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

เวลาทำงานของบริษัทแห่งหนึ่ง เริ่มงานเวลา 8:30 น. ถึงเวลา 17:30 น. หยุดวันเสาร์ อาทิตย์ และวันหยุดนักขัตฤกษ์
พนักงานแผนกอื่นจะส่งออร์เดอร์ให้ทำงานชิ้นหนึ่งมาให้หน่วยงานนี้ดำเนินการ ซึ่งโจทย์ก็คือต้องการการนับจำนวนเวลาที่ใช้ไปทั้งหมดต่อออร์เดอร์แต่ละชิ้นโดยเริ่มนับเวลาตั้งแต่เวลาที่ออร์เดอร์นั้นถูกเปิดจนกระทั่งถึงเวลาที่ออร์เดอร์นั้นๆ เสร็จสิ้น ซึ่งไม่ต้องการนับเวลาที่อยู่นอกเวลาทำการ และ ไม่นับเวลาของวันหยุดเสาร์ อาทิตย์ และวันหยุดนักขัตฤกษ์

ตัวอย่าง

วันที่เปิดออร์เดอร์ วันที่เสร็จสิ้น รวมเวลาที่ใช้ไปทั้งหมด (นาที)
20/06/2014 13:30 20/06/2014 15:30 120
20/06/2014 14:00 20/06/2014 17:00 180
20/06/2014 15:40 20/06/2014 17:40 110 (หักลบไป 10 นาที จาก 17:30-17:40)
20/06/2014 17:00 23/06/2014 09:00 60 (วันที่ 21 และ 22 ไม่นับ เพราะเป็นวันเสาร์ อาทิตย์)

จากตัวอย่างนี้ มีวิธีการเขียนสูตรใน Excel อย่างไรครับ เพื่อให้คำนวณ "รวมเวลาที่ใช้ไป (นาที)"

ขอขอบพระคุณเป็นอย่างสูงครับ

ปล. ผมตั้งกระทู้ผ่านเครื่อง Ipad จึงไม่สามารถแนบไฟล์มาให้ได้นะครับ

[snasui]

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

[tommy_jerry]

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

[snasui]

เพิ่มข้อมูลวันหยุดนักขัตฤกษ์เข้ามาด้วย โปรแกรมจะได้ตรวจสอบได้ว่าวันใดเป็นวันนักขัตฤกษ์ครับ

[tommy_jerry]

เรียนอาจารย์ครับ

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

ขอบพระคุณมากครับ

[snasui]

ดูตัวอย่างสูตรในคอลัมน์ H:I ตามไฟล์แนบครับ

[tommy_jerry]

เรียน อาจารย์ที่เคารพ

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

ด้วยควาามเคารพ
ทอม

[tommy_jerry]

เรียน อาจารย์ครับ

ผมพบปัญหาเล็กน้อย กรณีที่ Date Dif = 2 มันจะคำนวณเวลาไม่ได้ ตามไฟล์แนบครับ

[snasui]

ที่เซลล์ M2 ลองปรับสูตรเป็นตามด้านล่างครับ

=TEXT(IF(L2=0,MIN(K2,"17:30"+0)-MAX("8:30"+0,J2),IF(L2=1,"17:30"-MAX("8:30"+0,J2)+MIN(K2,"17:30"+0)-"8:30",IF(L2=2,"17:30"-MAX("8:30"+0,J2)+MIN(K2,"17:30"+0)-"8:30"+9/24-(ISNUMBER(MATCH(H2+1,วันหยุดนักขัตฤกษ์!$B$2:$B$124,0)))*9/24,IF(L2>=3,"17:30"-MAX("8:30"+0,J2)+MIN(K2,"17:30"+0)-"8:30"+(L2-1)*9/24-SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(H2+1&":"&I2-1)),วันหยุดนักขัตฤกษ์!$B$2:$B$124,0)))*9/24)))),"[m]")+0

[tommy_jerry]

อาจารย์ครับ

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

ผมรบกวนอาจารย์ด้วยนะครับ
ขอบพระคุณมากๆ ครับ

[snasui]

เพื่อไม่ให้ยุ่งยาก กรณีที่เวลาเลยจากเวลาเลิก เช่น เข้า 25/6/2014 19:54 ให้คีย์วันเริ่มงานเป็นวันใหม่ คือ 26/6/2014 8:30 แทน ไม่เช่นนั้นจะมีเงื่อนไขการคำนวณที่ซับซ้อนขึ้นมากครับ

[tommy_jerry]

อาจารย์ครับ Open Date ที่ได้มานั้น พนักงานรับเรื่องจะทำการ Key ข้อมูลเข้าระบบครับ ซึ่งการสั่งออร์เดอร์นั้นจะสามารถบันทึกได้ทั้งวัน แต่หน่วยงานที่ดำเนินการจะทำงานแค่ 8.30 น. ถึง 17.30 น. ครับ เลยทำให้ไม่สามารถทำอย่างที่อาจารย์เสนอมาได้ล่ะครับ

ขอรบกวนท่านอาจารย์ด้วยนะครับ จักเป็นพระคุณอย่างสูง

[snasui]

ลองตามด้านล่างนี้เพื่อแปลงวัน เวลา ให้เป็นตามที่ควรจะเป็นครับ

1. เซลล์ H2 คีย์
   =INT(D2)+(MOD(D2,1)>"17:30"+0)
   Enter > Copy ลงด้านล่าง
2. เซลล์ J2 คีย์
   =IF(MOD(D2,1)>"17:30"+0,"8:30"+0,D2-H2)
   Enter > Copy ลงด้านล่าง

[tommy_jerry]

โอเคเลยครับ ขอบคุณมากๆ ครับอาจารย์