คำนวณเวลาการทำงานนอกเวลา

[shevy]

- การคำนวณเวลาในวันหยุดถ้าทำครบ 8 ชั่วโมงให้แสดงที่ ช่อง 1 เท่าสูงสุดที่ 8 ชั่วโมง ถ้าน้อยกว่าให้แสดงตามที่ได้ ส่วนที่เกิน 8 ชั่วโมงให้แสดงผลที่ช่อง 3 เท่าคะ
- การแสดงผลชั่วโมงหลังเวลา 24.00 - 8.00 ในวันศุกร์ให้แสดงผลที่ช่อง 3 เท่าคะ

[snasui]

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

เซลล์ N3 คีย์

=IF(C3=$C$14,MIN(8/24,MIN(17/24,E3)-8/24-I3),0)

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

ส่วนคอลัมน์ O ให้เขียนคำตอบที่ถูกต้องลงมาในเซลล์แทนการเขียนเป็นสูตรพร้อมอธิบายว่าค่านั้น ๆ คิดมาได้อย่างไรจะได้สะดวกในการทำความเข้าใจครับ

[shevy]

ขอบคุณค่ะ ในคอลัมน์ O กรณีที่เริ่มทำตั้งแต่เวลา 24.00 เป็นต้นไป จำนวนชั่วโมงจะไม่แสดงที่ช่อง 1 เท่า แต่จะต้องไปแสดงที่ช่อง 3 เท่าค่ะ จะอ้างอิงจาก คอลัมน์ C3 เวลาเลือก เป็น Holiday ค่ะ

[snasui]

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ N3 คีย์
   =IF(AND($C3=$C$14,$D3="00:00"),0,IF(C3=$C$14,MIN(8/24,MIN(17/24,E3)-8/24-I3),0))
   Enter > Copy ลงด้านล่าง
2. ที่ O3 คีย์
   =IF(C3=$C$14,L3-N3,0)
   Enter > Copy ลงด้านล่าง

[shevy]

ขอบคุณค่ะ ในคอลัมน์ N5 กรณีที่เลือกคอลัมน์ C5 เป็น Holiday แล้วใส่เวลาจะคำนวณไม่ถูกต้องค่ะ และในคอลัมน์ O5 จะขึ้น ERROR : ######## ค่ะ

[snasui]

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

[shevy]

เงื่อนไขตามนี้ค่ะ
1.คอลัมน์ M5 ค่าที่ได้มาจากคอลัมน์ H2 กับ J2 ก่อนเวลาเข้างาน และหลังเวลาออกงานและอ้างอิง กับคอลัมน์ C5=C15 เช่น ถ้าคอลัมน์ D5 เริ่มที่ 6:00 จะได้ 2:30 ชั่วโมง และคอลัมน์ E5 สิ้นสุดที่เวลา 20:30 จะได้ 3 ชั่วโมง เมื่อรวมกันแล้ว ที่คอลัมน์ M5 จะได้เท่ากับ 5:30 ชั่วโมง แต่ถ้าเลือกพักที่คอลัมน์ J5 จะเหลือ 5 ชั่วโมง แต่ถ้าเลือกเวลาเริ่มที่ 17:30 - 20:00 แล้วไม่พักจะได้ 2:30 ชั่วโมง ถ้าเป็นช่วงเช้าจะไม่มีหักเวลาพัก เช่น เริ่ม 6:00 - 8:30 จะได้เท่ากับ 2:30 ชั่วโมง

2.คอลัมน์ N5 ค่าที่ได้มาจากเวลา เข้าและเวลาออกโดยอ้างอิงจาก กับคอลัมน์ C5=C16 เช่น กะเริ่ม 8:30 ถึง 17:30 จะได้ 8 ชั่วโมง ถ้าเข้าก่อนเวลาเข้าและทำเลยเวลาออกข้อมูลจะไปแสดงที่คอลัมน์ O5 แต่ถ้าอยู่ในช่วงเวลาเข้าและออก เช่น เข้า 10:00 - 16:00 จะได้ 5 ชั่วโมง แต่ถ้าเลือกไม่พักที่ คอลัมน์ H5 จะได้ 6 ชั่วโมง

[Bo_ry]

ถูกไหม

M5
=(C5="Normal")*(IF(E5<$H$2,E5-D5,IF(D5>$J$2,E5-D5,MAX($H$2-D5,0)+MAX(E5-$J$2,0)))-(J5="พัก")*"0:30")

N5
=(C5="Holiday")*MIN(MAX(MIN($J$2,E5)-MAX($H$2,D5),0)-(H5="พัก")*"1:00","8:00")

O5
=(C5="Holiday")*(IF(E5<$H$2,E5-D5,IF(D5>$J$2,E5-D5,MAX($H$2-D5,0)+MAX(E5-$J$2,0)))-(J5="พัก")*"0:30")

[shevy]

- ที่คอลัมน์ O สามารถสามารถเลือกพักได้ 2 ช่วงค่ะ คือ 1:00 ชั่วโมง หรือ 0:30 ค่ะ
- ที่คอลัมน์ O ถ้าเลือกพัก 1:00 ชั่วโมง เวลา 0:00-8:00 จะขึ้น Error : ##### ที่คอลัมน์ N ค่ะ ขอบคุณค่ะ

[snasui]

ลองดูตัวอย่างไฟล์แนบ ผมได้เพิ่มคอลัมน์ L:N มาช่วยคำนวณเพื่อลดความซับซ้อนของสูตร

คอลัมน์ L ซึ่งเป็นคอลัมน์รวมการทำงานที่ไม่รวมล่วงเวลาจะพิจารณาเวลาพักเที่ยงและพักหลังเลิกงานด้วย
คอลัมน์ M คือล่วงเวลาก่อนเข้างาน
คอลัมน์ N คือล่วงเวลาหลังเลิกงาน

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

[shevy]

ที่คอลัมน์ P ค่า Max สุดจะไม่เกิน 8 ชั่วโมงค่ะ ถ้าเกินจะปัดไปที่ คอลัมน์ Q โดยอ้างอิงตามเวลาเข้า-ออกที่ คอลัมน์ H2 กับ J2 แต่ถ้าไม่เกิน 8 ชั่วโมงก็ยึดตามเวลาที่ คอลัมน์ D กับ E ค่ะ

[snasui]

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ L5 คีย์
   =MAX(0,IF(D5<6/24,E5-D5,MIN(E5,$J$2)-MAX($H$2,D5))-I5)
   Enter > Copy ลงด้านล่าง
2. ที่ O5 คีย์
   =IF(C5=$C$15,M5+N5-K5,0)
   Enter > Copy ลงด้านล่าง
3. ที่ P5 คีย์
   =IF(AND(D5>=6/24,C5=$C$16),MIN(8/24,L5),0)
   Enter > Copy ลงด้านล่าง
4. ที่ Q5 คีย์
   =IF(C5=$C$16,M5+N5-I5+MAX(0,L5-8/24),0)
   Enter > Copy ลงด้านล่าง

[shevy]

- คอลัมน์ Q ถ้าเลือกพักจะแสดงผลไม่ถูกต้องค่ะ
- คอลัมน์ Q สามารถเลือกพักได้ทั้ง 1 ชั่วโมง และ 30 นาทีค่ะ

[snasui]

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

[Bo_ry]

P26

=(K26="Normal")*(IF(M26<$H$2,M26-L26,IF(M26>$J$2,M26-MAX(L26,$J$2),MAX($H$2-L26,0)+MAX(M26-$J$2,0)))-(O26="Yes")*"0:30")

N35
=M35-L35-(O35="yes")*"1:00"-(P35="Yes")*"0:30"

Q35
=MAX((K35="Holiday")*MIN(MAX(MIN($J$2,M35)-MAX($H$2,L35),0)-(O35="yes")*"1:00","8:00"),0)

R35
=N35-Q35

[shevy]

ได้ตรงตามที่ต้องการแล้วค่ะ ขออนุญาตเพิ่มที่คอลัมน์ Q ถ้ากำหนดเวลาน้อยกว่า 4 ชั่วโมงให้แสดงค่าที่น้อยแต่ถ้ามากกว่า 4 ชั่วโมงสามารถเลือกพักได้ค่ะ ขอบคุณค่ะ