ฟังก์ชั่นในการคำนวณโอทีวันหยุด

[ruthaithip]

รบกวนสอบถามฟังก์ชั่นในการคำนวณโอทีวันหยุดค่ะ
ทำงาน จันทร์-เสาร์ 08:00 - 17:00 น.
โดย OT วันหยุดแบ่งออกเป็นตามตารางไฟล์แนบค่ะ
รบกวนด้วยนะคะ ไม่รู่จะใช้สูตรอะไรในการคำนวณเลยค่ะ
ขอบคุณมากค่ะ

[snasui]

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

การคิดค่าล่วงเวลาจะต้องระบุให้ชัดว่าแต่ละวันเข้างานเวลาใดบ้าง ออกงานเวลาใดบ้าง เข้าจริงเวลาใด ออกจริงเวลาใด ถ้ามีเป็นกะก็ต้องระบุว่ากะนั้น ๆ เข้าเวลาใด ออกเวลาใด ฯลฯ ลองดูตัวอย่างเกี่ยวกับเรื่องค่าล่วงเวลาที่นี่ครับ ค่าล่วงเวลา

[ruthaithip]

ขออภัยค่ะอาจารย์สันติพงษ์
งั้นขอตั้งคำถามใหม่ในไฟล์แนบนะคะ เหมือนจะงงกับตัวเองเหมือนกันค่ะ

[snasui]

ช่วยเขียนคำถามในฟอรัมนี้ด้วยเพื่อความสะดวกในการค้นหาของเพื่อน ๆ ครับ

สำหรับที่ถามมานั้น ลองตามด้านล่าง

1. เซลล์ N5 คีย์
   =ROUND((M5-L5+(M5<L5))*48,0)/48
   Enter > Copy ลงด้านล่าง
2. เซลล์ S10 คีย์
   =MIN(N10,"2:30"+0)
   Enter > Copy ไปยังเซลล์ที่เกี่ยวข้อง
3. ที่เซลล์ R10 คีย์
   =MAX(0,N10-S10)
   Enter > Copy ไปยังเซลล์ที่เกี่ยวข้อง

[ruthaithip]

สวัสดีค่ะ
ขอบคุณมากค่ะทำได้แล้ว ว่าแต่ยังมีข้อสงสัยอยู่ค่ะ....*_*'''
ช่อง R10 กับ S10 ต้องใช้สูตรอะไรคะในการคำนวณ
เพราะช่อง R10 จะต้องคำนวณได้ไม่เกิน 8 ชั่วโมง
การคำนวณจำนวนชั่วโมงจะต้องอยู่ในช่วงเวลา 08:00-17:00 เท่านั้นค่ะ

กรณีสมมุติ เข้างาน 6:15-14:20
ช่อง R10=5:30 เพราะถ้าทำงานเกินเวลา 13:01 น. จะถูกหักเวลาพักทันที 1 ชั่วโมง
ช่อง S10=2:00

และ
กรณีสมมุติ เข้างาน 5:20-21:15
ช่อง R10=8:00
ช่อง S10=6:30

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

[snasui]

และ
กรณีสมมุติ เข้างาน 5:20-21:15
ช่อง R10=8:00
ช่อง S10=6:30

มีเวลาพักหรือไม่ครับ หากมีเวลาเกินเท่าไรถึงจะถือว่ามีเวลาพักครับ

[ruthaithip]

เวลาพักมีเวลาเดียวค่ะ คือช่วงเวลางาน 8.00 - 17.00น. พัก 1 ชั่วโมง วันทำงานไม่มีปัญหาค่ะเพราะต้องทำเต็มเวลาค่ะ
แต่วันหยุดมี 2 กรณีค่ะ (ตั้งแต่เวลา 13:01น. เป็นต้นไปจะหักเวลาพัก 1 ชั่วโมง)
1. ตั้งแต่เวลา 8.00 - 13.00 ให้เป็นจำนวนโอทีทั้งหมดไม่หักเวลาพักเพราะถือว่าเกินเที่ยงไปแค่ชั่วโมงเดียวค่ะ
2. ตั้งแต่เวลา 8.00 - 13.01 เป็นต้นไป ให้หักเวลาพัก 1 ชั่วโมงค่ะ

ขอบคุณค่ะ

[snasui]

ปรับ Worksheet และแนบมาใหม่โดยเพิ่มคอลัมน์ที่แสดงเฉพาะเวลาทำงานหลังหักเวลาพักแล้ว กรณีไม่ทราบว่าต้องใช้สูตรไหนก็ให้คีย์มือมาก่อน เพื่อจะใช้คำนวณหาล่วงเวลาต่อไปครับ

[nattasiray]

เวลาพักมีเวลาเดียวค่ะ คือช่วงเวลางาน 8.00 - 17.00น. พัก 1 ชั่วโมง วันทำงานไม่มีปัญหาค่ะเพราะต้องทำเต็มเวลาค่ะ
แต่วันหยุดมี 2 กรณีค่ะ (ตั้งแต่เวลา 13:01น. เป็นต้นไปจะหักเวลาพัก 1 ชั่วโมง)
1. ตั้งแต่เวลา 8.00 - 13.00 ให้เป็นจำนวนโอทีทั้งหมดไม่หักเวลาพักเพราะถือว่าเกินเที่ยงไปแค่ชั่วโมงเดียวค่ะ
2. ตั้งแต่เวลา 8.00 - 13.01 เป็นต้นไป ให้หักเวลาพัก 1 ชั่วโมงค่ะ

ขอบคุณค่ะ

ระยะเวลาในเงื่อนไขข้อ ๑ กับข้อ ๒ มันขัดแย้งกันนะครับ ผมอ่านแล้วยังงงอยู่

น่าจะเป็น

Code: Select all

ถ้าวันทำงานเป็นวันธรรมดาแล้ว
     ระยะเวลาทำงานล่วงเวลา = (ภาคเช้าก่อน 08:00 - 08:00) + (ภาคบ่าย-18:00)
แต่ถ้าไม่ใช่
   ถ้าเวลาเลิกทำงานล่วงเวลาไม่เกิน 13:00 แล้ว 
       ระยะเวลาทำงานล่วงเวลา = เวลาสิ้นสุด-เวลาเริ่มต้น
   แต่ถ้าไม่ใช่
       ระยะเวลาทำงานล่วงเวลา = เวลาสิ้นสุด-เวลาเริ่มต้น-หักช่ั่วโมงพัก ๑ ชั่วโมง
   จบการคำนวณ
จบการคำนวณ

ผมแนะนำให้คุณเขียนแบบที่ผมเขียนครับ อ่านแล้วเข้าใจกว่าเยอะ

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

[ruthaithip]

ขอบคุณค่ะคุณณัฐศิระ ตอนนี้เริ่มเข้าลึกขึ้นและงงเพิ่มขึ้นมาก ๆ เลยค่ะ
แล้วจะลองทำวิธีที่คุณแนะนำดูนะคะ ได้ไม่ได้ยังไงจะแจ้งผลกลับนะคะ

[ruthaithip]

เรียน อาจารย์สันติพงษ์

ที่อาจารย์ให้ทำเพิ่ม หมายถึงช่องสีเหลืองตามที่แนบมานี่หรือเปล่าคะ และใช่ทำแบบนี้หรือเปล่าคะ

ขอบคุณมากค่ะ

[nattasiray]

ถามว่าเกณฑ์การให้ค่าล่วงเวลาที่ระบุเป็นจำนวนเท่านี้ มีวิธีการคิดอย่างไรครับ

[ruthaithip]

เรียนคุณณัฐศิระ

ที่ให้เกณฑ์เท่านี้ คิดจากการทำงานช่วงเวลา 8.00-17.00 น.เท่านั้น จะมีเวลาพัก 1 ชั่วโมง
นอกนั้นจะให้เป็นชั่วโมงโอทีทั้งหมดในระหว่างที่ปฎิบัติงานยังไม่เสร็จภาระกิจ (นอกเหนือเวลานี้จะไม่ไม่หักเวลาพัก)
ไม่ว่าจะเป็นวันทำงานปรกติหรือวันหยุดต่าง ๆ เพราะเกณฑ์นี้จะให้เฉพาะพนักงานขับรถเท่านั้น เป็นกรณีพิเศษค่ะ
ถึงได้คำณวณยาก ไม่รู้จะใช้สูตรอะไรคำนวณ เพราะดันไม่เหมือนชาวบ้านเขาเอาซะเลยค่ะบอกได้คำเดียว มึนตึ๊บ ค่ะ

[nattasiray]

ขอโทษครับที่คำถามไม่ชัดเจน

อัตราจำนวนเท่าของค่าจ้างล่วงเวลาที่ผมถามถึงนั้น มีการให้อย่างไร เช่น ทำงาน ไม่เกิน ๑ ชั่วโมง ให้ ๑ เท่า ทำงานไม่เกิน ๓ ชั่วโมง ให้ ๑.๕ เท่า ถ้ามากกว่า ๓ ชั่วโมงให้ ๒ เท่า เป็นตัน ช่วยระบุด้วยครับ

[nattasiray]

ขอโทษครับที่คำถามไม่ชัดเจน

อัตราจำนวนเท่าของค่าจ้างล่วงเวลาที่ผมถามถึงนั้น มีการให้อย่างไร เช่น ทำงาน ไม่เกิน ๑ ชั่วโมง ให้ ๑ เท่า ทำงานไม่เกิน ๓ ชั่วโมง ให้ ๑.๕ เท่า ถ้ามากกว่า ๓ ชั่วโมงให้ ๒ เท่า เป็นตัน ช่วยระบุด้วยครับ

สรุปกรณีก่อนนะครับ

กรณีที่ ๑ การคิดระยะเวลาทำงานล่วงเวลาในวันทำงานวันธรรมดา

• ทำงานก่อน ๘ โมง เลิกงานหลัง ๑๗:๐๐ คิดก่อน ๘ โมง กับ คิดหลัง ๘โมงเย็น แต่ในช่วง ๘ - ๑๗ ไม่นำมาคิด เพราะถึอเป็นเวลางาน และไม่หักเวลาพัก

กรณีที่ ๒ การคิดค่าระยะเวลาทำงานล่วงเวลาในวันเสาร์ วันอาทิตย์ วันหยุดนักขัตฤกษ์ต่าง ๆ

• กรณีที่ ๒.๑ ทำงานก่อน ๘ โมง เลิกงานไม่เกิน ๑๓:๐๐ ระยะเวลาทำงานล่วงเวลาเท่ากับ เวลาสิ้นสุด - เวลาเริ่มต้น ถ้าไม่ใช่ ให้ไปใช้กรณีที่ ๒.๒

• ๒.๒ ทำงานก่อน ๘ โมง เลิกงานหลัง ๑๓:๐๐ ระยะเวลาทำงานล่วงเวลาเท่ากับ เวลาสิ้นสุด - เวลาเริ่มต้น-เวลาพัก 1 ชั่วโมง

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

ปัญหาที่คุณงงในการคำนวณมีสาเหตุมาจากคุณไม่ได้เขียนผังการคำนวณลงในกระดาษในรูปแบบข้อความก่อนว่า มีเงื่อนไขอะไรบ้าง เงื่อนไขดังกล่าวส่งผลการทำงานอย่างไร ถ้าเขียนเสร็จตั้งแต่แรก มาสร้างการคำนวณก็เสร็จ เป็นทำงานแบบอัจฉริยะ (Work smart) แต่ถ้าไม่ทำ เป็นการทำงานหนักเอาการครับ (Work hard) T_T

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

ถ้าคำใดที่รุนแรงไปผมก็ขออภัยด้วย แต่เป็นคำติเพื่อนำไปให้คุณได้ปรับปรุงแก้ไขครับ

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

ด้วยความปรารถนาดี

[ruthaithip]

ขอโทษครับที่คำถามไม่ชัดเจน
อัตราจำนวนเท่าของค่าจ้างล่วงเวลาที่ผมถามถึงนั้น มีการให้อย่างไร เช่น ทำงาน ไม่เกิน ๑ ชั่วโมง ให้ ๑ เท่า ทำงานไม่เกิน ๓ ชั่วโมง ให้ ๑.๕ เท่า ถ้ามากกว่า ๓ ชั่วโมงให้ ๒ เท่า เป็นตัน ช่วยระบุด้วยครับ
อัตราจำนวนเท่าของค่าจ้างล่วงเวลาที่คุณถามถึงนั้น หมายถึงประมาณนี้หรือเปล่าคะ
วันธรรมดา : จะให้ 1.5 เท่า เท่านั้น ไม่จำกัดจำนวนชั่วโมง โดย (ถ้าทำงานเกิน 20 นาที คิดเป็น 30 นาที)/(ถ้าทำงานเกิน 50 นาที คิดเป็น 1 ชั่วโมง)
วันหยุดต่าง ๆ : จะมี 2 เกณฑ์ คือ
ช่วงเวลา 8.00-17.00 จะเป็น 1 เท่า แต่ถ้าทำเกินเวลา 13.01 น. เป็นต้นไปจนถึง 5 โมงเย็น จะต้องถูกหักเวลาพักด้วย 1 ชั่วโมง
นอกเวลา 8.00-17.00 จะเป็น 3 เท่าทั้งหมดและไม่หักเวลาพักค่ะ

สรุปกรณีก่อนนะครับ
กรณีที่ ๑ การคิดระยะเวลาทำงานล่วงเวลาในวันทำงานวันธรรมดา

• ทำงานก่อน ๘ โมง เลิกงานหลัง ๑๗:๐๐ คิดก่อน ๘ โมง กับ คิดหลัง ๘โมงเย็น แต่ในช่วง ๘ - ๑๗ ไม่นำมาคิด เพราะถึอเป็นเวลางาน และไม่หักเวลาพัก

กรณีที่ ๒ การคิดค่าระยะเวลาทำงานล่วงเวลาในวันเสาร์ วันอาทิตย์ วันหยุดนักขัตฤกษ์ต่าง ๆ

• กรณีที่ ๒.๑ ทำงานก่อน ๘ โมง เลิกงานไม่เกิน ๑๓:๐๐ ระยะเวลาทำงานล่วงเวลาเท่ากับ เวลาสิ้นสุด - เวลาเริ่มต้น ถ้าไม่ใช่ ให้ไปใช้กรณีที่ ๒.๒

• ๒.๒ ทำงานก่อน ๘ โมง เลิกงานหลัง ๑๓:๐๐ ระยะเวลาทำงานล่วงเวลาเท่ากับ เวลาสิ้นสุด - เวลาเริ่มต้น-เวลาพัก 1 ชั่วโมง

อย่างนี้ชัดเจนและถูกต้องไหมครับ ถ้าชัดเจนและถูกต้อง ผมจะได้สร้างการคำนวณให้ถูกต้อง
กรณีที่ 1 ใช่ค่ะ
กรณีที่ 2.1 และ 2.2 ใช่ค่ะ แต่อย่างที่แจ้งข้างต้นจะเป็นโอที 1 เท่า และ 3 เท่า เท่านั้นค่ะ (ข้อความสีชมพูด้านบนค่ะ)
ไม่แน่ใชว่าตอบตรงคำถามที่ต้องการข้อมูลหรือเปล่าคะ

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

OT.xls

[nattasiray]

ผมขอสรุปสิ่งที่ต้องแก้ไขเกี่ยวกับตารางคำนวณของคุณก่อนครับ

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

• เนื่องจากงานของคุณมีลักษณะเป็นฐานข้อมูล ดังนั้นคุณต้องเปลี่ยนตารางบันทึกข้อมูลจากตารางออกรายงานให้เป็นตารางฐานข้อมูลครับ และเพิ่มคอลัมน์จำนวนเท่าของ OT คอลัมน์จำนวนเงินไว้ด้วย

• การบันทึกข้อมูลวันเดือนปีที่ทำงาน ให้คุณบันทึกวันเดือนปีด้วยปีคริสตศักราช ต่อจากนั้นจัดรูปแบบการแสดงผลตัวเลข (Format Number) ด้วยรหัส ววว d ดดด bbbb เพื่อแสดงวันเดือนปี พร้อมชื่อวัน

• ใช้คำสั่ง Conditional Formatting ทำแถบสีให้กับเซลล์ที่เป็นวันเสาร์วันอาทิตย์ วันนักขัตฤกษ์เพื่อตรวจสอบการคำนวณ

• ใช้คำสั่ง Conditional Formatting ซ่อนข้อความหากผู้ใช้ลืมกรอกเวลาเริ่มต้นทำงานหรือเวลาสิ้นสุดทำงาน

• ใช้ Data Validation ตรวจสอบการกรอกวันเดือนปีของผู้ใช้ว่าเผลอกรอกวันเดือนปีโดยใช้ปีพุทธศักราชหรือไม่

ในการคำนวณหาระยะเวลาและจำนวนเท่าของ OT ต้องใช้สูตรต่อไปนี้

• ISBLANK เพื่อตรวจสอบว่าลืมป้อนข้อมูลจุดใดจุดหนึ่งหรือไม่ ถ้าลืมไม่แสดง

• MATCH ใช้ตรวจสอบว่าวันเดือนปีที่มาทำงานนั้นเป็นวันหยุดนักขัตฤกษ์จากตาราง Holiday หรือไม่ ถ้าใช่ จะบอกค่าตำแหน่งมา ถ้าไม่ใช่จะส่งค่า #N/A! เพื่อแจ้งว่าไม่พบค่าดังกล่าว ดังนั้นใช้สูตร ISNA ตรวจสอบซ้ำอีกครั้งหนึี่ง ซึ่งถ้าค่าที่ส่งออกจาก MATCH มาเป็น #N/A! ISNA จะแจ้งค่าเป็น TRUE เพื่อยืนยันว่าไม่พบจริง ๆ

• WEEKDAY ใช้ตรวจสอบว่าวันเดือนปีที่มาทำงานนั้นเป็นวันเสาร์หรือวันอาทิตย์หรือไม่ ถ้าเป็นวันเสาร์ WEEKDAY จะให้ค่าเลข 7 ส่วนวันอาทิตย์ให้ค่าเลข 1

• MIN ใช้ตรวจสอบว่าเวลาเริ่มต้นทำงานในวันธรรมดานั้นอยู่ก่อนเวลา 08:00 หรือไม่ ถ้าใช่ ให้เอาเวลานั้นมาคิด OT ครับ

จำนวนเท่าของ OT ไม่ต้องหักชั่วโมงพักเพราะดูจากชื่อวัน และเวลาที่เริ่มทำงาน

• เมื่อสร้างตารางบันทึกข้อมูลเสร็จแล้วต้องสร้างตารางคำนวณสรุปผลต่างหาก โดยใช้ฟังก์ชัน SUMIF หาผลรวมอย่างมีเงื่อนไข

• สุดท้าย ต้องป้องกันเซลล์ที่มีสูตรอนุญาตให้เซลล์ที่เป็นตัวแปร ซึ่งได้แก่ เซลล์เวลาเริ่มทำงาน เซลล์เวลาเลิกงาน เซลล์กรอกข้อมูลวันหยุดประจำแห่งปี

ศึกษาเรื่องการใช้ Conditional Formatting กับ Data Validation จาก Youtube ครับ ใช้คำที่ผมแนะนำให้ไปสืบค้น

ลองหาหนังสือจัดการฐานข้อมูลด้วยไมโครซอฟต์เอ็กเซล ๒๐๑๐ มาอ่านด้วยครับ มีเนื้อหาสอนการใช้ไมโครซอฟต์เอ็กเซลเลียนแบบฐานข้อมูล และมีคำอธิบายของการคำนวณหา OT สูตรที่เกี่ยวข้อง ครับ

เห็นสูตรแล้วอย่าเพิ่งตกใจนะครับว่าทำไมยาวยืดเยื้อ และอย่าเพิ่งท้อใจครับ

ทำใจครับ ระบบการศึกษาไทย สอนแบบพื้นมาก ๆ ไม่ได้เจาะประเด็น ทำให้ผู้ใช้ใช้วิธีสามัญแก้ปัญหา ส่งผลให้ทำงานเสร็จช้า

สำหรับการ Format Number ให้มีข้อความ รวมด้วย กระทำโดย
คลิกขวาที่เซลล์เลือก Format Cells...
คลิกแท็บ Number
คลิก Custom ที่อยู่ในกรอบ Category
ที่ช่อง Type พิมพ์ รหัส พร้อมข้อความภายใต้เครื่องหมายอัญประกาศ เช่น h" ชั่วโมง "mm" นาที "
คลิก OK

ดูจากแฟ้มแนบ

[nattasiray]

เพิ่งมาเห็นว่า

ถ้าทำงานเกิน 50 นาที ปัดเป็น 1 ชั่วโมง
ถ้าทำงานเกิน 20 นาที ปัดเป็น 30 นาที
ถ้าทำงานน้อยกว่า 20 นาที ปัดทิ้ง

แล้วกรณีที่อยู่ในช่วง 30 - 49 ปัดเป็นค่าอะไรครับ รวมถึง เท่ากับ 20 นาที เท่ากับ 50 นาที ด้วยครับว่าจะปัดเป็นอย่างไร

เขียนเงื่อนไขต้องเขียนให้ครบถ้วนครับ มิฉะนั้นจะได้ผลลัพธ์การคำนวณที่ไม่ถูกต้อง

[ruthaithip]

เรียนอาจารย์สันติพงษ์

เอ... ทำไมพักนี้อาจารย์เงียบไปเลยคะ เป็นอะไรไปหรือเปล่าคะ

[snasui]

ผมกลับบ้านที่ใต้มาครับ ชื่อผมที่ถูกต้อง คือ สันติพงศ์ ครับ กรณีต้องการสนทนาปราศรัยให้ไปที่หมวด Talk ครับ