Excel Forum

สูตรทราบจำนวนชั่วโมงทำงาน (ผม copy มา)
1.P10 ไม่มีคำตอบ
2.P11 ตอบศูนย์ ที่จริงต้อง 4:00:00
ต้องช่วยปรับสูตรด้วยครับ
ขอบคุณครับ

แบบนี้ใช้ได้หรือเปล่าครับ

สูตรทราบจำนวนชั่วโมงทำงาน
ต้องมีวันที่ และพ.ศ มาเกี่ยวข้องด้วยครับ หากงานยังไม่จบ ใช้เวลานานต้องระยะเวลาที่ค้างจากเริ่มถึงปัจจุบัน
เช่น แถว11
เงื่อนไข 1 วัน มี 8ชั่วโมง เวลาเริ่มงาน 8:00-17:00 มี 9ชม. เเละไม่รวมเวลา 12:00-13:00 อีก 1ชม.
ขอบคุณครับ

ประมาณนี้หรือเปล่าที่ P3=IF(B3=0,"ไม่กรอกเวลาเริ่ม",IF(M3=0,"ไม่กรอกเวลาจบงาน",IF(A3=L3,M3-B3,IF(L3>A3,TIME(8,0,0)+M3-B3))))

แล้วแถว A11 ละครับ ไม่ถูกครับ ขอให้ดูโจทย์ผมหน่อยครับ
ขอบคุณครับ

ตามไฟล์ล่าสุด A11 วันที่ 2/2/2558 ถึง 7/9/2559 ต้องเป็นกี่ชม ครับ ถ้าคิดตามปรกติ 2/2/2558 คือ1 วัน เท่ากับ 8 ชม แล้ววันที่ 7/9/2559 8.00-12.00 เท่ากับ 4 ชม รวมเป็น 8+4=12 ชม ไม่ใช่หรือครับ คิดแค่ ส่วนเกินของวันล่าสุด ปรับเป็น =IF(B3=0,"ไม่กรอกเวลาเริ่ม",IF(M3=0,"ไม่กรอกเวลาจบงาน",IF(A3=L3,M3-B3,IF(L3>A3,(L3-A3)*TIME(8,0,0)+M3-B3)))) คำตอบก็จะเป็น 4 ชม นะครับ

คุณเข้าใจผิดนะ ดูจากโจทย์ก็น่ารู้เเล้ว ผมคิดว่าผมตั้งโจทย์ถูกนะครับ
ผมต้องการอย่างนี้ครับเช่น A11 คอลั่ม M11 7/9/2559 เวลา 12:00:00-คอลั่ม B11 2/2/2558 เวลา8:00:00 ลบกันเท่ากับ 1...ปี นะครับ ส่วนเดือนเเละวัน เเละชั่วโมง ลบ ตามโจทย์
ความต้องการ อย่างทราบผลลัพท์จำนวนมันลบเวลาเท่าไร เป็น [h]:mm:ss
ขอบคุณครับ

แล้วสรุป บรรทัดที่ A11 คำตอบเป็นเท่าไหรครับ ถ้า format เป็น [h]:mm:ss จะได้ไม่เกิน 24 ชม.ถ้าตั้งแต่ 24 ชมเป็นต้นไปจะปัดไปเป็นจำนวนวัน และจะแสดงเฉพาะ เวลา ไม่เกิน 24 ชม ถ้าตามที่บอก สูตรนี้ จะถูก นะครับที่เป็น 4 ชม P3=IF(B3=0,"ไม่กรอกเวลาเริ่ม",IF(M3=0,"ไม่กรอกเวลาจบงาน",IF(A3=L3,M3-B3,IF(L3>A3,(L3-A3)*TIME(8,0,0)+M3-B3))))

ได้ครับสูตรนี้เคยใช้มาเเล้ว
=IF(B6=0,"ไม่กรอก",IF((L6+M6)-(A6+B6)<0,"",IF(M6>TIME(17,0,0),TIME(17,0,0),M6)-IF(B6<TIME(8,0,0),TIME(8,0,0),B6)-IF(OR(B6>TIME(12,40,0),M6<TIME(12,0,0)),0,IF(AND(B6<TIME(12,0,0),M6>TIME(12,40,0)),TIME(0,40,0),TIME(12,40,0)-B6))+(L6-A6)*TIME(8,20,0)))
อันนี้สูตรเดิมเค้าคิดได้ ที่ผมต้องการที่ตามโจทย์ครับ

ขอโทษขอเอาใหม่
เดิมสูตรนี้ใช้คำนวนเวลา ใน 1วัน มีเวลาทำงาน 8:20:00 ชั่วโมง
=ช่วงเช้า 8:00:00 ถึง 12:00:00
=ช่วงบ่าย 12:40:00 ถึง 17:00:00
ใน 1วัน มีเวลาทำงาน 8:20:00 ชั่วโมง
คือสูตรเดิมที่ต้องการปรับ =IF(B6="","ไม่กรอก",IF(OR((($F6+$G6)-($A6+$B6))<0,COUNT(A6,$F6:$G6)=0),"",IF($A6=$F6,(IF(AND($G6>="12:00:00"+0,$G6<="12:40:00"+0),"12:00:00",IF($G6<"12:00:00"+0,$G6,$G6-"00:40:00"))-IF(AND($B6>="12:00:00"+0,$B6<="12:40:00"+0),"12:00:00",IF($B6<"12:00:00"+0,$B6,$B6-"00:40:00"))),SUM(IF(AND($B6>="08:00:00"+0,$B6<="12:00:00"+0),("12:00:00"-$B6)+"04:20:00",IF(AND($B6>="12:40:00"+0,$B6<="17:00:00"+0),"17:00:00"-$B6,IF(AND($B6>"12:00:00"+0,$B6<"12:40:00"+0),"04:20:00"+0,0))),IF(AND($G6>="08:00:00"+0,$G6<="12:00:00"+0),$G6-"08:00:00",IF(AND($G6>="12:40:00"+0,$G6<="17:00:00"+0),($G6-"12:40:00")+"04:00:00",IF(AND($G6>"12:00:00"+0,$G6<"12:40:00"+0),"04:00:00"+0,0))),"08:20:00"*(IF((($F6)-($A6))=0,0,(($F6)-($A6))-1))))))
1.สูตรที่ต้องการปรับแก้จากเดิมนี้คือ ดังนั้น ใน 1วัน มีเวลาทำงาน 8:00:00 ชั่วโมง
=ช่วงเช้า 8:00:00 ถึง 12:00:00
=ช่วงบ่าย 12:00:00 ถึง 17:00:00
2.ผมลองแก้แบบผม คือแก้สูตรเวลาให้เป็น หน่วย 00 ทั้งหมด เช่น 12:40:00 ให้เป็น 12:00:00 และเข้าใจสูตรเองว่า หน่วย 00:40:00 เป็นหน่วยช่วงพักเวลา
เเล้วแก้เป็นสูตรตรง 00:400:00 ให้เป็น 00:60:00 ในคำตอบ L6 คำตอบต้อง 16:00:00 (17:00:00 อันนี้ผิด เพราะ2วัน 8X2=16 )
ขอบพระคุณยิ่ง

จากตัวอย่างไฟล์ที่แนบมาช่วยกรอกตัวอย่างคำตอบทีถูกต้องมาทุกเซลล์ จะได้คำนวณเปรียบเทียบได้ครับ

1.คำตอบที่ถูกต้องคือ คอลัมท์ O
2.ดังนั้นสูตรช่อง L สูตรการคิดมี 20นาที(8:20:00ต่อวัน หัก 40นาที) ต้องการปรับสูตรให้คิดเหลือเท่ากับ (8:00:00ต่อวันหัก 60นาที ) ผมคิดตามหลักการเพื่อได้คำตอบ แต่อยากปรับสูตรตามข้อ 2 ไม่ต้องคิดหลายชั้น

ลบค่าในเซลล์ O4 ทิ้งไปแล้วปรับสูตรที่ M6 เป็นด้านล่างครับ

=IF(ISNUMBER(L6),L6-N6*"8:00",L6)

Enter > Copy ลงด้านล่าง

ผมลองเเล้ว ยังไม่ได้
ใน คอลัมท์ L ยังคิด 8:20:00
ขอบคุณครับ

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

สวัสดีครับ
ขอปรับเป็นดังนี้
1.P10:Q10-M10:N10=เวลา
เงื่อนวันมี 8 ชั่วโมง หักช้วงพัก 1ชม. (12:00-13:00)
ผมทดลองดู แต่ไม่ได้ที่ต้องการ
ขอบคุณครับ

เท่าที่ดู เหมือนว่าเครื่องบันทึกเวลา จะไม่สามารถเข้าถึงได้ในวันหยุด
ดังนั้น กรณีที่ทำงานคร่อมวันหยุด จึงไม่มีการบันทึกเวลาออก แต่จะ
ไปบันทึกเวลาออกของวันทำงานถัดไปแทน (โดยไม่มีการบันทึกเวลาเข้า)
ใช่ไหมครับ ?

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

ลองดูในไฟล์ที่ผมเขียนนะครับ ในนั้นจะอาศัยสมมุติฐานว่า
กรณีมีวันหยุดคร่อม จะหักชั่วโมงของวันหยุดให้ โดยถือว่า
วันทำงานถัดไปเริ่มต้นที่ 8:00 น.ทันที


** กรณีวันหยุดมีคร่อมช่วงได้ ให้เปลี่ยนสูตร J5 เป็น
=IF(D5>B5,COUNTIFS(ปฏิทิน!D:D,">"&B5,ปฏิทิน!D:D,"<"&D5)+TIME(15,0,0),0)

1.ลองลองใช้ดู มันยังไม่ตรง เเถวที่ 12กับ19 เเถว12 คำตอบต้อง G12=9:00:00 เเถว19 G19=32:00:00
2.ใช้เงื่อนไข holiday จำนวนช่องสูตรจะน้อยลงได้ไหมครับ
ขอบคุณครับ

ในไฟล์ที่แนบมา แถวที่ 12 Col I,J สูตรกลับไปอ้างอิงข้อมูลจากแถวที่ 19 ครับ
น่าจะมีการ Move Cell นะครับ ( ถ้าเอาข้อมูลไปกรอกในไฟล์เดิมที่
ผมทำไว้ จะคำนวณได้ 9:00:00 ตามที่แจ้งมา )

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

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

สำหรับการรวมสูตรให้อยู่ใน Cell เดียวของแต่ละบรรทัด (รวมถึงการแจ้งกรณี
กรอกวันหยุดมานั้น ค่อยทำหลังจากที่ผลการคำนวณถูกต้องตามที่ต้องการจะดีกว่าครับ)

เริ่มมี Confuse กรณีทำงานหลายวัน ช่วงเวลา 17:00-8:00 ของอีกวัน
นับเป็นเวลาทำงานหรือไม่ครับ ? (กรณีไม่เป็นวันหยุด)