สูตร Excel คำนวณชั่วโมงทำงานจากเวลาเข้า-ออก แยกชั่วโมงปกติ OT และมาสาย

ตอบสั้นๆ ก่อน: Excel เก็บเวลาเป็นเศษส่วนของ 1 วัน ชั่วโมงทำงาน = (เวลาออก − เวลาเข้า) × 24 − ชั่วโมงพัก ถ้าเลิกงานหลังเที่ยงคืนใช้ MOD(ออก−เข้า,1) แทนการลบตรงๆ แล้วแยกชั่วโมงปกติกับ OT ด้วย MIN และ MAX สูตรทั้งหมดอยู่ด้านล่าง ใช้ได้ตั้งแต่ Excel 2016

อัปเดตล่าสุด: ตุลาคม 2569

ตั้งค่าก่อนเขียนสูตร

เซลล์	ค่า	ความหมาย
ตั้งค่า!B1	08:00	เวลาเข้างาน
ตั้งค่า!B2	17:00	เวลาเลิกงาน
ตั้งค่า!B3	1	ชั่วโมงพักเที่ยง
ตั้งค่า!B4	8	ชั่วโมงทำงานปกติต่อวัน

ข้อมูลรายวัน: C5 เวลาเข้า D5 เวลาออก จัดรูปแบบเซลล์เป็นเวลา (hh:mm) ไม่ใช่ข้อความ

สูตรทีละขั้น

E  ชั่วโมงอยู่ที่ทำงาน   =IF(OR(C5="",D5=""),"",MOD(D5-C5,1)*24-ตั้งค่า!$B$3)
F  ชั่วโมงปกติ          =IF(E5="","",MIN(E5,ตั้งค่า!$B$4))
G  ชั่วโมงเกิน (OT)      =IF(E5="","",MAX(0,E5-ตั้งค่า!$B$4))
H  นาทีมาสาย            =IF(C5="","",MAX(0,ROUND((C5-ตั้งค่า!$B$1)*1440,0)))
I  OT ปัดลงทีละ 30 นาที  =IF(G5="","",FLOOR(G5,0.5))

ทำไมต้องคูณ 24 และ 1440

Excel เก็บ 1 วันเป็นเลข 1 เวลา 12:00 คือ 0.5 ผลลบของเวลาจึงเป็นเศษของวัน คูณ 24 ได้ชั่วโมง คูณ 1440 (24 × 60) ได้นาที ถ้าลืมคูณ จะเห็นค่าอย่าง 0.375 แทน 9 ชั่วโมง

เลิกงานหลังเที่ยงคืน

เข้า 18:00 ออก 02:00 ถ้าใช้ D5−C5 จะได้ค่าติดลบ MOD(D5−C5,1) แก้ได้โดยบวก 1 วันให้เอง ได้ 8 ชั่วโมง (ก่อนหักพัก) ถูกต้อง แต่ถ้าทำงานเกิน 24 ชั่วโมงหรือข้ามหลายวัน ต้องเก็บวันที่คู่กับเวลา

จุดที่สูตรชุดนี้ยังไม่ครอบคลุม

1. ไม่รู้ว่าวันไหนเป็นวันหยุด ชั่วโมงในวันหยุดต้องใช้อัตราคนละแบบ ต้องมีปฏิทินบริษัทแล้ว VLOOKUP วันที่ ดู โอที 3 แรงคืออะไร
2. ไม่มีการอนุมัติ OT ทุกนาทีที่อยู่เกินกลายเป็น OT ทั้งที่บางคนแค่ออกช้า
3. มาสายแล้วยังได้ชั่วโมงปกติเต็ม ถ้าเลิกงานช้ากว่าปกติเท่ากับที่สาย ชั่วโมงรวมยังครบ ต้องตัดสินใจว่าจะนับชั่วโมงจากเวลาเข้าจริงหรือเวลาเริ่มงาน และมีเวลาผ่อนผันกี่นาที
4. ลืมตอกบัตรออก สูตรจะว่างหรือผิด ต้องมีช่องเตือน
5. พักก่อนเริ่ม OT กรณี OT เกิน 2 ชั่วโมงต้องพักไม่น้อยกว่า 20 นาที ชั่วโมงพักนี้ไม่นับเป็น OT

ค่า OT จากชั่วโมงที่ได้ ใช้สูตรใน คิดโอที 1.5 รายเดือน ส่วนการรวมเข้าเงินเดือนทั้งเดือน ดู สูตร Excel เงินเดือน

คู่มือเงินเดือนครบทุกเรื่องสำหรับเจ้าของธุรกิจที่ทำเงินเดือนเอง: ทำเงินเดือนด้วย Excel หรือโปรแกรมสำเร็จรูป

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

แหล่งข้อมูล

• Microsoft Support: การบวกหรือลบเวลาใน Excel
• พระราชบัญญัติคุ้มครองแรงงาน พ.ศ. 2541 มาตรา 27 (การพักก่อนทำงานล่วงเวลา)

บทความนี้เป็นข้อมูลทั่วไปเพื่อช่วยสร้างไฟล์คำนวณ