สูตรการนับชม.ทำงานแบบหักเวลาพัก

[phanorn]

Worksheet คิดชม.ทำงาน OT ของพนักงาน แต่ติดปัญหาว่า บาง หน่วยงานชม.ทำงาน OT ต้องหักเวลาพักงาน
อยากทราบว่าจะมีสูตรใดที่พอเรา คีย์เวลาทำงานเริ่มต้น 8:00:00 เวลาเลิกงาน 21:30:00
เวลาพัก 12:00 - 13:00
พร้อมกับแนบไฟล์ ตย.มาด้วย

[nattasiray]

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

เป็นงานกะหรือไม่ ถ้าใช่ กรุณาระบุเวลาเริ่มงานและเลิกงาน เวลาพักของงานแต่กะด้วย เช่น

• กะเช้าเริ่ม 08:00 ถึง 17:00 พัก 12:00 ถึง 13:00
  กะบ่ายเริ่ม 13:00 ถึง 22:00 พัก 17:00 ถึง 18:00

เป็นต้นครับ

ในการลงเวลาเข้าทำงานก่อนเวลาเริ่มงานจริง คิดเป็น OT หรือไม่ เพราะมีบางองค์กรถือว่าเป็น OT ด้วย เช่น เวลา OT สำหรับ พนักงานปฏิบัติงานกะเช้า หลังเริ่มงาน เริ่มตั้งแต่เริ่มตั้งแต่ เวลา 18:00 น. หรือ ก่อน 08:00 น. ถือว่าเป็น OT ในทำนองเดียวกัน พนักงานปฏิบัติงานกะบ่าย ทำงานก่อน 13:00 และหลัง 23:00 ถือว่าเป็น OT

ระยะเวลาทำงานล่วงเวลาไม่ครบ ๑ ชั่วโมง จ่ายค่าแรงล่วงเวลาหรือไม่

คิดให้เรียบร้อยทีเดียวครับ จบประเด็น จะได้สูตรที่ตรงกับความต้องการ

แฟ้มตัวอย่างที่ให้ ผมขอเพียง 5 - 6 บรรทัด และแสดงคำตอบของผลลัพธ์ที่ต้องการด้วยครับ แฟ้มที่ให้มาไม่สามารถบอกได้ว่าคุณคำนวณถูกต้องหรือไม่

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

[phanorn]

ไม่ได้ทำงานเป็นกะ เวลาทำงานปกติ 8:00 ถึง 17:00
ตย. ถ้าเริ่มทำงาน 7:00 จะได้ OT 1 ชม. ก่อนเวลางาน
ถ้าทำงานต่อเนื่อง หลัง 17 : 00 น. จะคิด OT ต่อเนื่อง
แต่ถ้าเร่ิมทำงาน 8:00 เลิกทำงาน 21:30
บาง side งาน จะต้องหักเวลาพักเที่ยง 1 ชม.

[snasui]

ช่วยระบุว่าไฟล์ที่แนบมานั้นผิดพลาดอย่างไร ต้องการคำตอบที่เซลล์ใดเป็นค่าใดครับ

[phanorn]

ไฟล์ที่แนบมาใหม่ อยากให้สูตรการนับชม.ทำงานครอบคลุมเมื่อมีการทำงานตั้งแต่ 8:00- 17:00 น.
จะมีการหักเวลาพัก ระหว่าง 12:00 -13:00
แต่ถ้าเวลาเริ่มงาน 17:00 เป็นต้นไป จะไม่มีการหักเวลาพักงานใน Worksheet เดียวกัน
เพื่อให้ Side งานคีย์ แค่วันและเวลาเริ่ม-เลิกทำงานเท่านั้น
นอกนั้นสูตรคิดจากฝ่ายบัญชี จะมีการ Locked Cell

[snasui]

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

[nattasiray]

ในคอลัมน์ D และคอลัมน์ E ได้จัดรูปแบบการแสดงวันเดือนปีและเวลา พบว่ามีการกรอกวันและเวลามาแล้ว ดังนั้นให้ทำการตั้งลบทันที โดยต้องใช้เงื่อนไขเวลาเริ่มต้นอยู่ในช่วง 8:00 - 17:00 หรือไม่

สิ่งทีต้องคิดต่อว่า หากเวลาทำงานเริ่มตั้งแต่ 0600 ถึง 2100 มีการคิดค่าล่วงเวลาอย่างไร เช่น ต้องคิด 06-08 1 ครั้ง และคิด 17-21 อีก 1 ครั้ง
หรือคิดแบบนำ 21 ลบ 6 โดยตรงครับ

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

[phanorn]

จะนำ ไฟล์ท่ี Post คำตอบออกมาได้อย่างไร

[snasui]

นำเมาส์ไปชี้แล้วคลิกเพื่อดาวน์โหลดได้เลยครับ

[phanorn]

ตอบ : ถ้าทำงานก่อน 8:00 น ตย. 7:00 - 8:00 จะคิดค่าล่วงเวลา 1 ชม.
และถ้าหลัง 17:00 น. มีการทำงานต่อ จะคิดค่าล่วงเวลาตั้งแต่ 17:00 น. จนถึงเวลาเลิกทำงาน

ได้ลองทำไฟล์ OT_ Solution แล้วได้ผลดีตรงตามความต้องการ
แต่มีปัญหา
ตรงยอดรวมระยะเวลาที่ทำงานไม่รวมยอดให้
และอัตราค่าแรงที่คิดให้กับผู้รับเหมามีหลายอัตรา
ตามตย. ไฟล์ OT_Sample ที่แนบมาให้ค่ะ

[snasui]

ตรงยอดรวมระยะเวลาที่ทำงานไม่รวมยอดให้
และอัตราค่าแรงที่คิดให้กับผู้รับเหมามีหลายอัตรา
ตามตย. ไฟล์ OT_Sample ที่แนบมาให้ค่ะ

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

[phanorn]

ตอบ: อยากให้คอลัมน์ F ตรงบรรทัดสุดท้ายเป็นยอดรวมชม.ทำงานทั้งหมด
พร้อมกันนี้ได้ใส่ปัญหาและคำถามพิเศษเพือเป็นความรู้มาใน ไฟล์ OT sample2 ท่ีแนบมานี้ด้วย
ขอบคุณท่ีให้ความช่วยเหลือและถ่ายทอดความรู้เป็นวิทยาทาน

[phanorn]

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

[phanorn]

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

[snasui]

ยกสูตรนั้นมาถามเลย เพื่อน ๆ จะได้เห็นสูตรไปด้วยว่าเป็นอย่างไรครับ

[phanorn]

สูตร ในคอลัมน์ รวมเป็นเงิน
=ROUND(IF(MOD(F9*24*$G9,0.25)>=0,125,CEILING(F9*$G9,0.25),FLOOR(F9*24*$G9,0.25)),2)
คอลัมน์ F9 = ชม.ทำงาน
คอลัมน์ $G = อัตราบาท / ชม.

[snasui]

สูตรที่ยกมาไม่ถูกต้องครับ

If มีได้แค่ 3 องค์ประกอบเท่านั้น แต่ในสูตรด้านบนมีถึง 4 ส่วนประกอบ คือ

1. MOD(F9*24*$G9,0.25)>=0
2. 125
3. CEILING(F9*$G9,0.25)
4. FLOOR(F9*24*$G9,0.25)

[phanorn]

ขอโทษค่ะ สูตรผิดตรง . เป็น ,
สูตรที่ถูกต้องคือ
=ROUND(IF(MOD(F9*24*$G9,0.25)>0.125,CEILING(F9*24*$G9,0.25),FLOOR(F9*24*$G9,0.25)),2)

ขอบคุณที่ชี้แนะ

[snasui]

จากสูตร

=ROUND(IF(MOD(F9*24*$G9,0.25)>0.125,CEILING(F9*24*$G9,0.25),FLOOR(F9*24*$G9,0.25)),2)

ความหมายคือ

ให้ทำการปัดเศษโดยมีทศนิยม 2 ตำแหน่งโดยมีเงื่อนไขว่า

ถ้า MOD(F9*24*$G9,0.25)>0.125 เป็นจริงแล้ว ให้นำ CEILING(F9*24*$G9,0.25) มาปัดเศษ ถ้าไม่เป็นจริงให้นำ FLOOR(F9*24*$G9,0.25) มาปัดเศษ

ศึกษาเพิ่มเติมได้ที่นี่ครับ

1. Round http://office.microsoft.com/th-th/excel ... aspx?CTT=1
2. Ceiling http://office.microsoft.com/th-th/excel ... aspx?CTT=1
3. Floor http://office.microsoft.com/th-th/share ... aspx?CTT=1

[phanorn]

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