สูตรการคิดค่าล่วงเวลา OT Solution

[filippo]

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

คำถาม

1. สูตรในช่องสีแดง G4, G8 นั้นถูกต้องแล้วหรือไม่ เพราะถ้าหากไม่มีการกรอกเวลา ในช่อง G4 นี้จะโชว์ค่าเป็น -1
2. ต้องการให้แปลงจากจำนวนชั่วโมงที่เป็น x.3 หรือ x.7 เป็น x.5 ตามช่องสีเหลือง H5, H6, H7, I11 เพื่อให้นำไปคำนวณต่อได้สะดวก (x แทนตัวเลขใดๆ)
3. ได้ลองทำการลงเวลาด้วย colon (:) มาแล้ว แต่ยังไม่สามารถทำได้สำเร็จ เพราะต้องการให้เป็นหน่วย เช่น 2.5 ชั่วโมง แต่ที่ได้คือ 2.3 ชั่วโมง ซึ่งไม่สามารถนำไปใช้คำนวนค่าล่วงเวลาได้ทันที

ขอบคุณค่ะ

[snasui]

ให้คีย์เวลาเป็น : หรือแนบไฟล์ที่ลองคีย์คั่นเวลาด้วย : แล้วติดปัญหามาถามครับ

[filippo]

ลองใช้ colon แล้วค่ะ ไปกันใหญ่ เลยค่ะ

หรือ มันมีการเขียนสูตรที่ทำให้ครึ่งชั่วโมงแสดงเป็น 0.5 ชั่วโมงได้หรือไม่คะเนี่ย

[snasui]

ช่วยแจ้งเงื่อนไขมาทั้งหมดเกี่ยวกับ OT ในวันธรรมดา วันหยุด วันหยุดนักขัตฤกษ์ ว่า 1 เท่า, 1.5 เท่า และ 3 เท่า มีเงื่อนไขว่าอย่างไรครับ

[filippo]

วันจันทร์-เสาร์ เป็น working days หลัง 17.00 น. คิดเป็นล่วงเวลา โดยใช้เรท 1.5 เท่า

วันอาทิตย์ และวันหยุดนักขัตฤกษ์ เป็น holidays 8.00 - 17.00 น. คิดเป็นเรท 1.0 เท่า และหลังจาก 17.00 น. คิดเป็น เรท 3.0 เท่า ค่ะ

ในการนับจำนวนชั่วโมงจะไม่มีการปัดเศษของชั่วโมงขึ้นหรือลงนะคะ และปกติจะลงเวลาเป็นต้นชั่วโมงกับครึ่งชั่วโมง

ในบางทีก็มีกรณีที่ทำ OT เกิน 24.00 น. และก่อน 8.00 น. ด้วยค่ะ

[snasui]

ลองดูตัวอย่างคำตอบตามไฟล์แนบซึ่งผมได้ทำลำดับข้อเอาไว้ให้สำหรับแนวคิดว่าต้องทำอย่างไรก่อนหลัง

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

[nattasiray]

ลองใช้ colon แล้วค่ะ ไปกันใหญ่ เลยค่ะ

หรือ มันมีการเขียนสูตรที่ทำให้ครึ่งชั่วโมงแสดงเป็น 0.5 ชั่วโมงได้หรือไม่คะเนี่ย

ที่คุณคำนวณไม่ได้เพราะคุณไม่ทราบเรื่องข้อกำหนดของการใช้วันและเวลาที่ถูกต้อง

จากหนังสือ จัดการฐานข้อมูลด้วยไมโครซอฟต์เอ็กเซล 2010 หน้า 587 - 588 ที่ผมเขียน ข้อกำหนดการใช้วันและเวลา มีดังนี้

1. วันเวลาในระบบปฏิบัติการไมโครซอฟต์วินโดวส์เริ่มนับตั้งแต่วันที่ 1 มกราคม 1900 เวลา 00:00 น.
2. ระยะเวลาใน 1 วัน เริ่มนับตั้งแต่เวลา 00:00 น. ถึง 23:59 น. สามารถเขียนอีกอยู่ในรูปเป็นกี่เท่าของ 1 วัน โดยจำนวนเท่ามีค่าตั้งแต่ 0.00 – 0.9999 หรือเขียนในรูปของเศษส่วนของวัน เช่น
00:00 น. 0.0000 0
06:00 น. 0.2500 6/24 หรือ 1/4
08:00 น. 0.3333 8/24 หรือ 1/3
12:00 น. 0.5000 12/24 หรือ 1/2

3. เมื่อครบ 1 วันเวลาจะเริ่มที่ 00:00 ใหม่ ส่วนเลข 1 จะนำไปรวมกับเลขอนุกรมวัน
4. การป้อนข้อมูลวันเดือนปีต้องใช้เครื่องหมาย / ใช้คั่นระหว่างตัวเลขวัน ตัวเลขเดือน ตัวเลขปี เท่านั้น และต้องใช้ปีคริสตศักราช
5. การป้อนข้อมูลเวลาลงเซลล์ ต้องใช้เครื่องหมายทวิภาคหรือ Colon (:) ใช้คั่นระหว่างตัวเลขชั่วโมง ตัวเลขนาที ตัวเลขวินาที เท่านั้น
6. รูปแบบการกรอกวันเดือนปีขึ้นกับรูปแบบที่กำหนดในค่าขอบเขตประเทศ (Region and Language)
7. หากต้องการกรอกวันเดือนปีและเวลาลงไปในเซลล์ ให้กรอกในรูปของ d/m/yyyy h:mm เช่น 2/3/2011 12:25

การเปรียบเทียบเวลา กับเลขชั่วโมง ห้ามเปรียบเทียบทันทีโดยเด็ดขาด ต้องใช้ฟังก์ชัน HOUR แปลงค่าเป็นค่าเลขชั่วโมง ก่อน ครับ

ผมจำได้ว่ามีคำถามนี้มาครั้งหนึ่งแล้วนะครับ ลองดุูตัวอย่างเก่า ๆ ที่ผมตอบไว้ครับ

ลองหาหนังสือ จัดการฐานข้อมูลด้วยไมโครซอฟต์เอ็กเซล 2010 ที่ผมเขียน มาอ่านประกอบด้วยครับ มีตัวอย่างการคำนวณวันและเวลา ที่เกี่่ยวข้องกับงานฝ่ายบุคคลด้วยครับ

เสริมเกี่ยวกับแบบอักษรเริ่มต้น

แฟ้มของคุณยังไม่ใช้แบบอักษร Angsana New ขนาด 16 พอยน์ เป็นแบบอักษรเริ่มต้น เพราะทันทีที่คุณแทรกเวิร์คชีตใหม่ ยังเป็น Tahoma ขนาด 11 พอยน์ อยู่

การกำหนดแบบอักษรเริ่มต้นให้กับแฟ้ม
แท็บ Home (หน้าหลัก)
คลิกปุ่ม สไตล์เซลล์ (Cells Styles)
คลิกขวาที่ปกติ (Normal) เลือกปรับเปลี่ยน (Modify...)
คลิกปุ่มรูปแบบ (format...) เพื่อเรียกกรอบโต้ตอบรูปแบบ (Format)
คลิกแท็บแบบอักษร (Font)
กำหนดที่ช่องแบบอักษร Angsana New
กำหนดขนาด 16
คลิกปุ่มตกลง (OK) ตามกรอบโต้ตอบต่าง ๆ

ส่วนการกำหนดแบบอักษรเริ่มต้นให้กับโปรแกรม ศึกษาที่

http://www.youtube.com/watch?v=uPHiIaYVWRA

[nattasiray]

สอบถามเรื่อง เวลาเริ่มทำงานล่วงเวลาหลัง 17:00 น. จะต้องเริ่มกี่โมงครับ

[filippo]

สอบถามเรื่อง เวลาเริ่มทำงานล่วงเวลาหลัง 17:00 น. จะต้องเริ่มกี่โมงครับ

เริ่มทำโอทีได้ตั้งแต่ 17:00 น.เลยค่ะ ในวันทำการปกติ คือ จันทร์ ถึง เสาร์ นะคะ ส่วนวันอาทิตย์และวันหยุดนักขัตฤกษ์ เริ่มได้ตั้งแต่ 8:00 น.ค่ะ

[filippo]

ลองใช้ colon แล้วค่ะ ไปกันใหญ่ เลยค่ะ

หรือ มันมีการเขียนสูตรที่ทำให้ครึ่งชั่วโมงแสดงเป็น 0.5 ชั่วโมงได้หรือไม่คะเนี่ย

ที่คุณคำนวณไม่ได้เพราะคุณไม่ทราบเรื่องข้อกำหนดของการใช้วันและเวลาที่ถูกต้อง

ขอบคุณสำหรับข้อมูลค่ะ

[filippo]

ลองดูตัวอย่างคำตอบตามไฟล์แนบซึ่งผมได้ทำลำดับข้อเอาไว้ให้สำหรับแนวคิดว่าต้องทำอย่างไรก่อนหลัง

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

ขอบคุณมากๆ เลยค่ะ วันนี้นั่งศึกษาทั้งวันเลยมาขอบคุณช้า

[nattasiray]

ลองดูตารางทีผมปรับปรุงมาให้ใหม่นะครับ

ผมใช้สูตร IF ISNA MATCH OR NOT และคุณลักษณะ Conditional Formatting กับ Data Validation เพื่อสร้างตารางคำนวณหาอัตราค่าจ้างล่วงเวลา

มีการ Protect Sheet ไว้ หากต้องการดูข้อกำหนดของ Conditional Formatting และ Data Validation ให้คลิกขวาที่แท็บเวิร์คชีต Solution แล้วคลิก Unprotect Worksheet

Conditional Formatting ผมใช้เน้นบรรทัดที่เป็นวันเดือนปีที่เป็นวันหยุดนักขัตฤกษ์ ส่วน Data Validation ใช้ตรวจสอบว่าวันเดือนปีที่ผู้ใช้กรอกใช้ปีคริสตศักราชหรือไม่ กรอกเวลาโดยจำนวนนาทีเท่ากับ 0 นาที หรือ 30 นาที หรือไม่

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

[filippo]

ลองดูตารางทีผมปรับปรุงมาให้ใหม่นะครับ

ผมใช้สูตร IF ISNA MATCH OR NOT และคุณลักษณะ Conditional Formatting กับ Data Validation เพื่อสร้างตารางคำนวณหาอัตราค่าจ้างล่วงเวลา

มีการ Protect Sheet ไว้ หากต้องการดูข้อกำหนดของ Conditional Formatting และ Data Validation ให้คลิกขวาที่แท็บเวิร์คชีต Solution แล้วคลิก Unprotect Worksheet

Conditional Formatting ผมใช้เน้นบรรทัดที่เป็นวันเดือนปีที่เป็นวันหยุดนักขัตฤกษ์ ส่วน Data Validation ใช้ตรวจสอบว่าวันเดือนปีที่ผู้ใช้กรอกใช้ปีคริสตศักราชหรือไม่ กรอกเวลาโดยจำนวนนาทีเท่ากับ 0 นาที หรือ 30 นาที หรือไม่

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

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