สอบถามการคิดระยะวัน/เวลาดำเนินการ โดยมีเงื่อนไขตามเวลาทำการ

[pon@excelforum]

ต้องการหา การคิดระยะวัน/เวลาดำเนินการ โดยมีเงื่อนไขตามเวลาทำการค่ะ
โดย ต้องการผลต่างของเวลา หน่วยเป็น ชั่วโมง:นาที โดยคิดจาก วัน/เวลาที่แก้ไข-วัน/เวลาที่ได้รับเรื่อง
โดยคิดเวลาเฉพาะช่วงเวลาทำงาน โดย 1 วัน มี 8 ชั่วโมงคือ ตั้งแต่ 8:00-12:00 และ 13:00-17:00
ตัวอย่างตามไฟล์แนบค่ะ ขอบคุณนะคะ

[bank9597]

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

[bank9597]

ผมลองคิดออกมาแล้ว ลองดูครับว่าตรงที่ต้องการหรือไม่

เซลล์ G5 คีย์ =(D5+SUBSTITUTE(E5,".",":"))-(B5+SUBSTITUTE(C5,".",":")) คัดลอกลงมา หาเวลารวมทั้งหมด

เซลล์ H5 คีย์ =DAY(G5) คัดลอกลงมา นับจำนวนวัน

เซลล์ I5 คีย์ =(IF(H5=0,0,H5*24-(9*H5))&":"&0)+0 คัดลอกลงมา ใช้นับเวลาทำงานทั้งหมด

เซลล์ J5 คีย์ =IF(HOUR(SUBSTITUTE(C5,".",":"))>12,0,1) คัดลอกลงมา ใช้นับเงื่อนไขที่ 1

เซลล์ K5 คีย์ =IF(HOUR(SUBSTITUTE(E5,".",":"))<=12,0,1) คัดลอกลงมา ใช้นับเงื่อนไขที่ 2

เซลล์ L5 คีย์ =IF(AND(J5=1,K5=1),1,0) คัดลอกลงมา ใช้นับเวลาที่ต้องหัก

เซลล์ F5 คีย์ =(G5-I5)-((H5+L5)&":"&0&":"&0)+0 คัดลอกลงมา เพื่อแสดงผลลัพธ์

[pon@excelforum]

ขอบคุณมากนะคะ อ.สันติพงศ์ และรบกวนสอบถามสำหรับสูตรใน เซลล์ I5 &":"&0)+0 และ เซลล์ F5 &":"&0&":"&0)+0 ว่าใส่เพื่ออะไรคะ

[snasui]

ที่ตอบไปนั้นคือเป็นคุณ bank9597 ต้องขอบคุณคุณ bank9597 ครับ

การกรอกเวลา ให้คั่นด้วยโคล่อน ไม่ควรคั่นด้วยจุด เนื่องจากเป็นคนละหน่วยกันครับ เวลาเป็นเลขฐาน 60 ส่วนเลขธรรมดาเป็นฐาน 100

หากเขียนมาเป็นจุดทศนิยมเราสามารถแปลงเป็นเวลาโดยการเปลี่ยนจุดให้เป็นโคล่อนด้วยฟังก์ชั่น Substitute

สูตรใน I5 เป็นการแปลงตัวเลขให้กลับมาเป็นเวลาโดยการเชื่อม : เข้าไป เพื่อให้ระหว่างชั่วโมงกับนาทีมีลักษณะเป็น h:mm และเมื่อเชื่อมกันแล้วจะกลายเป็น Text ไม่ใช่ Number จึงต้องบวกด้วย 0 เพื่อให้กลับมาเป็น Number ที่เป็นเช่นนี้เพราะ วัน เวลา เป็น Number หรือตัวเลข จะใช้แบบ Text ไม่ได้ครับ

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

[pon@excelforum]

ไม่แน่ใจว่าสูตรครอบคลุมหรือไม่ กรณีที่ต้องหักเวลาพัก ซึ่งที่ คุณ bank9597 ทำไว้ที่ เซลล์ L5 : เช่นกรณี
1. วัน/เวลาที่ได้รับเรื่องและวัน/เวลาที่แก้ไข ต้องลบเวลาพักทั้ง 2 วัน คือ ลบ2 ชั่วโมง
2. กรณีที่ นับจำนวนวัน Day=0 หรือ 1 แต่บางกรณีต้องลบเวลาพัก 1 ชั่วโมง หรือบางกรณ๊ไม่ต้องลบเวลาพัก เช่น
-ได้รับเรื่อง 19 ม.ค. 12 9.04 แก้ไข 19 ม.ค. 12 16.00 (Day =0) ซึ่งต้องลบเวลาพัก 1 ชม.
-ได้รับเรื่อง 1 ก.พ. 12 9.35 แก้ไข 1 ก.พ. 12 10.05 (Day=0) ซึ่งต้องลบเวลาพัก 0 ชม.
คือต้องมีเงื่อนไข check เวลาที่ได้รับเรื่องและ แก้ไขหรือไม่คะว่าต้องลบเวลาพัก หรือไม่ เท่าไร
ขอบคุณมากค่ะ

[snasui]

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

[bank9597]

ที่ตอบไปนั้นคือเป็นคุณ bank9597 ต้องขอบคุณคุณ bank9597 ครับ

การกรอกเวลา ให้คั่นด้วยโคล่อน ไม่ควรคั่นด้วยจุด เนื่องจากเป็นคนละหน่วยกันครับ เวลาเป็นเลขฐาน 60 ส่วนเลขธรรมดาเป็นฐาน 100

หากเขียนมาเป็นจุดทศนิยมเราสามารถแปลงเป็นเวลาโดยการเปลี่ยนจุดให้เป็นโคล่อนด้วยฟังก์ชั่น Substitute

สูตรใน I5 เป็นการแปลงตัวเลขให้กลับมาเป็นเวลาโดยการเชื่อม : เข้าไป เพื่อให้ระหว่างชั่วโมงกับนาทีมีลักษณะเป็น h:mm และเมื่อเชื่อมกันแล้วจะกลายเป็น Text ไม่ใช่ Number จึงต้องบวกด้วย 0 เพื่อให้กลับมาเป็น Number ที่เป็นเช่นนี้เพราะ วัน เวลา เป็น Number หรือตัวเลข จะใช้แบบ Text ไม่ได้ครับ

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

ขอบคุณมากครับ อาจารย์

[bank9597]

ไม่แน่ใจว่าสูตรครอบคลุมหรือไม่ กรณีที่ต้องหักเวลาพัก ซึ่งที่ คุณ bank9597 ทำไว้ที่ เซลล์ L5 : เช่นกรณี
1. วัน/เวลาที่ได้รับเรื่องและวัน/เวลาที่แก้ไข ต้องลบเวลาพักทั้ง 2 วัน คือ ลบ2 ชั่วโมง
2. กรณีที่ นับจำนวนวัน Day=0 หรือ 1 แต่บางกรณีต้องลบเวลาพัก 1 ชั่วโมง หรือบางกรณ๊ไม่ต้องลบเวลาพัก เช่น
-ได้รับเรื่อง 19 ม.ค. 12 9.04 แก้ไข 19 ม.ค. 12 16.00 (Day =0) ซึ่งต้องลบเวลาพัก 1 ชม.
-ได้รับเรื่อง 1 ก.พ. 12 9.35 แก้ไข 1 ก.พ. 12 10.05 (Day=0) ซึ่งต้องลบเวลาพัก 0 ชม.
คือต้องมีเงื่อนไข check เวลาที่ได้รับเรื่องและ แก้ไขหรือไม่คะว่าต้องลบเวลาพัก หรือไม่ เท่าไร
ขอบคุณมากค่ะ

ผมลองเช็คดูแล้ว คิดว่าไม่น่าผิดครับ หากผิดอย่างไร ลองแจ้งมาครับ

[pon@excelforum]

ลอง manual คิดดูได้ตามไฟล์แนบใหม่ค่ะ ซึ่งบางข้อจะไม่ตรงกับที่คุณ Bank ทำไว้ค่ะ รบกวนช่วยดูให้ด้วยนะคะ (และถ้าถูกต้องจริงๆ แล้ว ต้องเอามาลบวันหยุดด้วย แต่ค่อยไปลบทีหลังแบบ manual ก็ได้ค่ะ หรือexcel สามารถไม่นับเวลาวันหยุดตามปฏิทินได้หรือไม่คะ ถ้ามีทำอย่างไรคะ) ขอบคุณมากค่ะ

[pon@excelforum]

ปรับไฟล์ใหม่ตามแนบค่ะ ไฟล์เดิมมีพิมพ์ผิดค่ะ

[bank9597]

โจทย์บอกว่า

ทำงานจันทร์-ศุกร์ ระหว่างเวลา 8.00 - 17.00 น.

แล้วทำไมถึงรับงานวันหยุดด้วย ลองดูในไฟล์ของคุณ B7 เป็นวันที่ 11/02/55 ซึ่งเป็นวันหยุด ลองอธิบายมาให้ผมฟังหน่อยครับ ว่าทำไม

[pon@excelforum]

คือหน่วยงานส่งปัญหาทำงาน 24 ชั่วโมงค่ะ แต่หน่วยงานที่รับ/แก้ไขปัญหา ทำงานเฉพาะ วัน/เวลาทำการค่ะ หน่วยงานปลายทางจึงขอคิดเวลาเฉพาะวัน/เวลาทำการค่ะ

[BaIZu]

คือหน่วยงานส่งปัญหาทำงาน 24 ชั่วโมงค่ะ แต่หน่วยงานที่รับ/แก้ไขปัญหา ทำงานเฉพาะ วัน/เวลาทำการค่ะ หน่วยงานปลายทางจึงขอคิดเวลาเฉพาะวัน/เวลาทำการค่ะ

ทำไม ลำดับที่ 30 ทำไมถึงมีการแก้ไขงานวันอาทิตย์ด้วยครับ

[bank9597]

ลองดูตามไฟล์แนบก่อน ว่าถูกต้องไหม หากถูกต้องแล้วสงสัยอย่างไรค่อยถามมาอีกทีครับ