สูตรหา OT ใน Excel: คำนวณชั่วโมงและค่าล่วงเวลาทีละขั้น

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

เข้าใจก่อน: Excel เก็บเวลาเป็นเศษส่วนของวัน

ใน Excel 1 วัน = 1 ดังนั้นเวลา 12:00 คือ 0.5 และ 1 ชั่วโมงคือ 1/24 เมื่อนำเวลาออกลบเวลาเข้า จะได้ผลเป็น "เศษส่วนของวัน" ต้องคูณด้วย 24 เพื่อแปลงเป็นจำนวนชั่วโมงที่นำไปคูณค่าแรงได้

=(C2-B2)*24

ถ้า B2 = 08:30 และ C2 = 17:30 จะได้ 9 ชั่วโมง ถ้าไม่คูณ 24 จะได้ 0.375 ซึ่งเป็นสาเหตุอันดับหนึ่งที่ค่า OT คำนวณผิด

เคล็ดลับการกรอกเวลา: พิมพ์เวลาด้วยเครื่องหมาย : เช่น 8:30 หรือ 17:45 อย่าพิมพ์ 8.30 เพราะ Excel จะเข้าใจว่าเป็นตัวเลข 8.3 ไม่ใช่เวลา

ขั้นที่ 1: คำนวณชั่วโมงทำงานและชั่วโมง OT

ตัวอย่างนี้ใช้เงื่อนไขที่พบบ่อย คือเวลาทำงานปกติวันละ 8 ชั่วโมง พักกลางวัน 1 ชั่วโมง ตารางบันทึกเวลามีคอลัมน์ A = วันที่, B = เวลาเข้า, C = เวลาออก

ชั่วโมงทำงานสุทธิ (คอลัมน์ D)

=MOD(C2-B2, 1)*24 - 1

• MOD(C2-B2, 1) ทำให้สูตรใช้ได้กับกะดึกที่เลิกงานหลังเที่ยงคืน เช่น เข้า 13:00 ออก 01:30 ถ้าลบตรง ๆ จะได้ค่าติดลบและขึ้น ##### แต่ MOD จะได้ 12.5 ชั่วโมงที่ถูกต้อง
• - 1 หักเวลาพัก 1 ชั่วโมง ถ้าบริษัทไม่หักพักหรือพักไม่เท่ากัน ให้ปรับตัวเลขนี้

ชั่วโมง OT (คอลัมน์ E)

=MAX(0, D2-8)

นำชั่วโมงทำงานลบ 8 ชั่วโมงปกติ และใช้ MAX กันไม่ให้ได้ค่าติดลบในวันที่ทำงานไม่ครบ

ปัดชั่วโมง OT ตามระเบียบ (คอลัมน์ F)

หลายบริษัทคิด OT เป็นหน่วยครึ่งชั่วโมง ทำ 2 ชั่วโมง 20 นาทีนับเป็น 2 ชั่วโมง ใช้ FLOOR ปัดลง

=FLOOR(E2, 0.5)

ถ้าคิดเป็นหน่วย 15 นาที ใช้ =FLOOR(E2, 0.25) และถ้าคิดตามนาทีจริงไม่ต้องปัด ให้ใช้ค่าในคอลัมน์ E ได้เลย

ทางเลือก: นับ OT เฉพาะเวลาหลังเลิกงาน

บางบริษัทนับ OT จากเวลาที่อยู่ต่อหลังเวลาเลิกงาน ไม่ใช่จากชั่วโมงรวม เช่น เลิกงาน 17:30 และเริ่มนับ OT หลัง 18:00 (พักก่อน 30 นาที) ใส่เวลาเริ่มนับ OT ไว้ที่เซลล์ $H$1 แล้วใช้

=MAX(0, (C2-$H$1)*24)

ถ้ามีกะที่เลิกหลังเที่ยงคืน ให้เปลี่ยน C2 เป็น IF(C2<B2, C2+1, C2) เพื่อบวก 1 วันให้เวลาออก

ขั้นที่ 2: แยกวันทำงานกับวันหยุดอัตโนมัติ

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

=IF(OR(WEEKDAY(A2, 2)>5, COUNTIF(วันหยุด!$A:$A, A2)>0), "วันหยุด", "วันทำงาน")

• WEEKDAY(A2, 2)>5 คือวันเสาร์ (6) หรืออาทิตย์ (7)
• COUNTIF(วันหยุด!$A:$A, A2)>0 คือวันที่อยู่ในรายการวันหยุดนักขัตฤกษ์

ถ้าบริษัททำงานวันเสาร์ เปลี่ยนเงื่อนไขเป็น WEEKDAY(A2, 2)=7 เพื่อให้หยุดเฉพาะวันอาทิตย์ ดูสูตรวันที่อื่น ๆ เพิ่มเติมในรวมสูตรวันที่ใน Excel

ขั้นที่ 3: สูตรคำนวณค่าการทำงานล่วงเวลา

หาค่าจ้างต่อชั่วโมง

สำหรับพนักงานรายเดือน วิธีที่ใช้กันทั่วไปคือ เงินเดือน ÷ 30 ÷ 8 เช่น เงินเดือน 18,000 บาท

=18000/30/8   → 75 บาทต่อชั่วโมง

พนักงานรายวันใช้ ค่าจ้างรายวัน ÷ 8 ใส่ค่าจ้างต่อชั่วโมงไว้ในเซลล์เดียว เช่น $I$1 แล้วอ้างอิงด้วย $ ทุกสูตร

ทำตารางอัตรา OT

ตามพระราชบัญญัติคุ้มครองแรงงาน อัตราขั้นต่ำที่ใช้กันทั่วไปเป็นดังนี้ (ควรตรวจสอบกับกฎหมายฉบับปัจจุบันและระเบียบของบริษัทก่อนใช้จริง)

ประเภท	อัตราของค่าจ้างต่อชั่วโมง
ทำงานล่วงเวลาในวันทำงาน	ไม่น้อยกว่า 1.5 เท่า
ทำงานในวันหยุด ในเวลาทำงานปกติ (พนักงานรายเดือน)	เพิ่มอีกไม่น้อยกว่า 1 เท่า
ทำงานในวันหยุด ในเวลาทำงานปกติ (พนักงานรายวัน)	ไม่น้อยกว่า 2 เท่า
ทำงานล่วงเวลาในวันหยุด	ไม่น้อยกว่า 3 เท่า

ทำตารางนี้ไว้ในชีท "อัตราOT" (A = ประเภท, B = อัตรา) แทนการพิมพ์ตัวเลขลงในสูตร เมื่อระเบียบเปลี่ยนจะได้แก้ที่เดียว

คำนวณค่า OT ต่อรายการ

ในตารางบันทึก OT มีคอลัมน์ ประเภท OT, ชั่วโมง และอัตรา ดึงอัตราจากตารางด้วย VLOOKUP แล้วคูณ

อัตรา:    =VLOOKUP(C2, อัตราOT!$A$2:$B$5, 2, FALSE)
ค่า OT:   =ROUND(D2 * $I$1 * E2, 2)

ตัวอย่างการคำนวณ: OT วันทำงาน 2.5 ชั่วโมง × 75 บาท × 1.5 = 281.25 บาท และ OT วันหยุด 2 ชั่วโมง × 75 × 3 = 450 บาท ถ้ายังไม่คุ้นกับ VLOOKUP อ่านการสร้างสูตร VLOOKUP ข้ามชีท

ขั้นที่ 4: สรุปชั่วโมงและค่า OT รายเดือนของพนักงานแต่ละคน

เมื่อบันทึก OT ของทุกคนไว้ในชีทเดียว (A = วันที่, B = รหัสพนักงาน, C = ประเภท, D = ชั่วโมง, F = ค่า OT) ใช้ SUMIFS รวมตามพนักงานและช่วงเดือน ใส่วันแรกของเดือนไว้ที่ $H$1

ชั่วโมงรวม: =SUMIFS(บันทึกOT!$D:$D, บันทึกOT!$B:$B, A2,
             บันทึกOT!$A:$A, ">="&$H$1, บันทึกOT!$A:$A, "<="&EOMONTH($H$1,0))
ค่า OT รวม: =SUMIFS(บันทึกOT!$F:$F, บันทึกOT!$B:$B, A2,
             บันทึกOT!$A:$A, ">="&$H$1, บันทึกOT!$A:$A, "<="&EOMONTH($H$1,0))

อ่านเทคนิค SUMIFS กับช่วงวันที่เพิ่มเติมได้ที่สูตร SUMIFS และ COUNTIFS หลายเงื่อนไข หรือถ้าต้องการสรุปหลายมุมมอง เช่น ตามแผนกและเดือน ใช้ Pivot Table

ปัญหาที่พบบ่อยในการคำนวณ OT

อาการ	สาเหตุ	วิธีแก้
ได้ชั่วโมงเป็นทศนิยมเล็ก ๆ เช่น 0.104	ลืมคูณ 24	เพิ่ม *24 ท้ายสูตร
ขึ้น ##### ในกะดึก	เวลาออกน้อยกว่าเวลาเข้า ผลติดลบ	ใช้ MOD(C2-B2, 1)
สูตรได้ #VALUE!	เวลาถูกพิมพ์เป็นข้อความ หรือพิมพ์ 8.30 แทน 8:30	พิมพ์ใหม่ด้วย : หรือแปลงด้วย =TIMEVALUE(B2)
ผลรวมเวลาเกิน 24 ชั่วโมงแสดงผิด	รูปแบบเซลล์เป็น h:mm	ใช้ Custom Format [h]:mm หรือแปลงเป็นชั่วโมงด้วย *24 ก่อนรวม
ค่า OT ต่างจากที่คำนวณมือหลักสตางค์	ทศนิยมสะสม	ครอบด้วย ROUND(..., 2)

คำถามที่พบบ่อย

ถ้าเครื่องสแกนนิ้วส่งออกเวลามาเป็นวันที่และเวลาในเซลล์เดียว ทำอย่างไร

ถ้าเป็นค่าวันที่-เวลาจริง (เช่น 13/11/2026 19:45) ลบกันได้เลยโดยไม่ต้องใช้ MOD เพราะมีวันที่กำกับอยู่แล้ว: =(C2-B2)*24 ถ้าต้องการแยกเฉพาะเวลา ใช้ =MOD(C2, 1) และแยกเฉพาะวันที่ใช้ =INT(C2)

แสดงชั่วโมง OT เป็น "2:30" แทน 2.5 ได้ไหม

ได้ ใช้ =E2/24 แล้วจัดรูปแบบเซลล์เป็น [h]:mm แต่การคูณเงินควรใช้ค่าที่เป็นชั่วโมงทศนิยม (2.5) เสมอ

สรุป

หัวใจของสูตรหา OT ใน Excel มี 3 ข้อ คือ ลบเวลาแล้วคูณ 24, ใช้ MOD รองรับกะข้ามเที่ยงคืน และเก็บอัตรา OT ไว้ในตารางแทนการพิมพ์ในสูตร เมื่อต่อด้วย WEEKDAY แยกวันหยุดและ SUMIFS สรุปรายเดือน ฝ่ายบุคคลจะได้ไฟล์คำนวณ OT ที่ใช้ซ้ำได้ทุกเดือน

สูตรเวลา วันที่ VLOOKUP และ SUMIFS สำหรับงานบุคคลแบบนี้ ฝึกได้จริงในหลักสูตรอบรม Excel HR และExcel Advance สำหรับองค์กรที่อยากให้วิทยากรสอน Excel ด้วยไฟล์บันทึกเวลาแบบเดียวกับที่ใช้จริง ดูรายละเอียดอบรม In-house / Online