อยากทราบ สูตรการคำนวณ OT โดยคิดจากจำนวนชั่วโมงเวลาการทำงาน แบบมีเงื่อนไข

[kimhunzx]

จากไฟล์แนบ ผมอยากทราบสูตรคำนวณชั่วโมงทำ OT ในช่องสีฟ้า ครับ โดยนำเงื่อนไขในช่องสีเหลืองมาคำนวณคือ
1. เวลาที่ขอ OT ก่อน/หลัง เวลาทำงาน (ในวันทำงานปรกติ) จะได้ OT 1.5
2. เวลาที่ขอ OT อยู่ในช่วงเวลาทำงาน (ทำงานในวันหยุด) จะได้ OT 2
3. เวลาที่ขอ OT ก่อน/หลัง เวลาทำงาน (ทำงานในวันหยุด) จะได้ OT 3 แต่จะต้องทำ OT ในช่วงเวลาทำงานตามข้อ 2 ด้วยจึงจะได้ OT 3

โดยจำนวนชั่วโมงในคอลัมน์ K,L,M ที่เป็นสีฟ้า จะต้องเป็นสูตรที่ได้มาจากการคำนวณจากเงื่อนไขของ คอลัมน์ E,G,H ครับ ที่ช่องสีเหลืองครับ
สมมุติว่า นาย A คอลัมน์ E เป็น W-วันทำงาน คอลัมน์ G เป็นเวลาเข้า 12:00 และ คอลัมน์ H เป็นเวลาออก 22:00
เวลารูดเข้า OT คือ 22:00
เวลาออก OT คือ เที่ยงคืน 00:00
ในช่องจำนวนชั่วโมง OT ที่เป็นสีฟ้าจะต้อง คำนวณจาก เวลาเข้า OT และ เวลาออก OT โดยมีเงื่อนไขในคอลัมน์ ของ E,G,H มาคำนวณด้วยครับ และถ้าขอ OT ในวันหยุด แบบเต็มวัน จะต้องลบเวลาพักออกด้วย 1 ชม. ครับ
ถ้าเป็น W-วันทำงาน ให้สูตรหย่อนลงในคอลัมน์ K
ถ้าเป็น H-วันหยุด ให้สูตรหย่อนลงในคอลัมน์ L
ถ้าเป็น T-วันหยุดประเพณี ให้สูตรหย่อนลงในคอลัมน์ M

ประมาณนี้ครับ หากคอลัมน์ไหน ไม่สามารถนำมาคำนวณหรือไม่สามารถนำมาทำเป็นเงื่อนไขได้ รบกวนขอทราบแนวทางการแก้ไขด้วยครับ
ขอบคุณครับ

เพิ่งโพสต์ครั้งแรก หากผิดพลาดประการใด ต้องขออภัยมา ณ ที่นี้ ด้วยครับ

OT_Test_2560.xlsx

[puriwutpokin]

ที่ K6=IF(MATCH($E6,{"W-วันทำงาน","H-วันหยุด","T-หยุดประเพณี"},0)=COLUMNS($K$6:K$6),$J6-$I6+($J6<$I6),"") คัดลอกไปขวาและล่างครับ

[kimhunzx]

ได้แล้วครับ ขอบคุณมากครับ

สอบถามเพิ่มเติม ในกรณีที่ขอ OT เต็มวัน อยากให้สูตร Check เงื่อนไข ลบเวลาพักด้วย 1 ชม. ครับ กรณีนี้เพิ่มในสูตรนี้ได้เลยหรือไม่ครับ

[puriwutpokin]

ปรับเป็น=IF(MATCH($E6,{"W-วันทำงาน","H-วันหยุด","T-หยุดประเพณี"},0)=COLUMNS($K$6:K$6),IF($J6-$I6+($J6<$I6)>"9:00"+0,$J6-$I6+($J6<$I6)-"1:00"+0,$J6-$I6+($J6<$I6)),"")

[kimhunzx]

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

เพิ่มเติม ครับ หากต้องการใส่ ISERROR เพื่อให้ไม่ให้ แสดง N/A ออกมา โดยเปลี่ยนเป็น 0 หรือ - แทน ผมลองใส่สูตรด้านล่างแล้ว ไม่ได้อ่ะครับ ไม่แน่ใจว่าต้องแก้ต้องไหนเพิ่มหรือเปล่าครับ

=IF(ISERROR(MATCH($E10,{"W-วันทำงาน","H-วันหยุด","T-หยุดประเพณี"},0)=COLUMNS($K$6:K$6),IF($J10-$I10+($J10<$I10)>"9:00"+0,$J10-$I10+($J10<$I10)-"1:00"+0,$J10-$I10+($J10<$I10)),""),0,MATCH($E10,{"W-วันทำงาน","H-วันหยุด","T-หยุดประเพณี"},0)=COLUMNS($K$6:K$6),IF($J10-$I10+($J10<$I10)>"9:00"+0,$J10-$I10+($J10<$I10)-"1:00"+0,$J10-$I10+($J10<$I10)),""))

[puriwutpokin]

ใช้เป็น ที่ K6=IFERROR(IF(MATCH($E6,{"W-วันทำงาน","H-วันหยุด","T-หยุดประเพณี"},0)=COLUMNS($K$6:K$6),IF($J6-$I6+($J6<$I6)>"9:00"+0,$J6-$I6+($J6<$I6)-"1:00"+0,$J6-$I6+($J6<$I6)),""),0)

[kimhunzx]

ได้แล้วครับ ขอบคุณมากครับ

[kimhunzx]

สอบถามเพิ่มเติม ครับ ในกรณี วันหยุด หากต้องการเพิ่มเงื่อนไข ได้ OT 2 ช่อง ต้องเขียนสูตรยังไงครับ

ตัวอย่างในแถวที่ 10
เวลาเข้า 08:00 เวลาออก 18:00 โดย Check จากเงื่อนไข H-วันหยุด
เวลารูดบัตรเข้า 08:00 เวลารูดบัตรออก 21:00
ซึ่งกรณีนี้ จะต้องได้ OT 2 ช่อง คือ ในเวลาทำงาน ช่วง 08:00 - 18:00 จะได้ 1 เท่าและหลัง 18:00 จนถึง 21:00 จะได้ 3 เท่า ซึ่ง คอลัมน์ L จะปรากฏ 9:00 ชั่วโมง โดยหักเวลาพักแล้ว และ ช่องคอลัมน์ M จะปรากฏ อีก 3:00 ชั่วโมงครับ รบกวนอีกครั้งด้วยครับ

ขอบคุณครับ

OT_Test_2560.xlsx

[kimhunzx]

ในกรณีเคสเพิ่มเติมดังกล่าว สามารถทำได้หรือไม่ครับ

[snasui]

คุณ kimhunzx ลองศึกษาการคำนวณค่าล่วงเวลาจากไฟล์แนบตามกระทู้นี้ viewtopic.php?t=2358#p15538 น่าจะพอช่วยให้เขียนสูตรได้ง่ายลงครับ

[kimhunzx]

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