การคำนวณชั่วโมงทำงานจากเวลาเข้า-ออกใน Excel มีจุดที่พลาดง่ายอยู่ 2 เรื่อง คือ Excel เก็บเวลาเป็น “เศษของวัน” และกะดึกที่ข้ามเที่ยงคืนจะได้ผลติดลบ บทความนี้รวมสูตรครบ ตั้งแต่คิดชั่วโมงต่อวัน หักพักเที่ยง รวมทั้งสัปดาห์ แยก OT ไปจนถึงคิดเป็นเงิน
🎬 ดูวิดีโอสอน (1 นาที 25 วินาที) บน Excel จริง หรืออ่านขั้นตอนละเอียดด้านล่างได้เลย
พื้นฐาน: เวลาใน Excel คือเศษของวัน
08:00 = 8/24 = 0.333… เวลา 1 วันเต็ม = 1 การเอาเวลาออกลบเวลาเข้าจะได้ “เศษของวัน” ต้องจัดรูปแบบเซลล์ผลลัพธ์เป็น [h]:mm (วงเล็บเหลี่ยมทำให้แสดงเกิน 24 ชม. ได้) หรือคูณ 24 เพื่อให้เป็นชั่วโมงแบบทศนิยม
ตัวอย่างที่ 1: ชั่วโมงทำงานต่อวัน
คอลัมน์ D จัดรูปแบบเป็น [h]:mm ส่วนคอลัมน์ E ใช้ =(C2-B2)*24 ได้ 8.75 ชั่วโมง เอาไปคูณค่าแรงต่อได้
ตัวอย่างที่ 2: กะดึกข้ามเที่ยงคืน
C2-B2 ตรงๆ จะได้ −16 ชั่วโมง (แสดงเป็น #####) การครอบด้วย MOD(…, 1) จะวนค่าลบให้กลับมาเป็นบวก ได้ 8:00 ชั่วโมงถูกต้อง
ตัวอย่างที่ 3: หักเวลาพักเที่ยง
ใส่เวลาพักในคอลัมน์ D เป็น 1:00 แล้วลบออก ได้ชั่วโมงทำงานจริง 8:00
ตัวอย่างที่ 4: รวมทั้งสัปดาห์
=SUM(E2:E6)| A | E | |
|---|---|---|
| 2 | จันทร์ | 8:45 |
| 6 | ศุกร์ | 9:15 |
| 8 | รวม | 45:30 |
เซลล์ผลรวมต้องจัดรูปแบบเป็น [h]:mm ไม่งั้นจะแสดงเป็น 21:30 (ตัดส่วนที่เกิน 24 ชม. ทิ้ง) วงเล็บเหลี่ยมสำคัญมากตรงนี้
ตัวอย่างที่ 5: แยกชั่วโมงปกติกับ OT
ชั่วโมงปกติ = =MIN(F2, 8/24) (ไม่เกิน 8 ชม.) ส่วน OT = =MAX(0, F2 - 8/24) เลข 8/24 คือ 8 ชั่วโมงในหน่วยเศษของวัน
ตัวอย่างที่ 6: คิดเป็นเงิน
คูณ 24 เพื่อแปลงเศษของวันเป็นชั่วโมง แล้วคูณค่าแรง (ล็อกด้วย $B$8) OT คิด 1.5 เท่า: 8×75 + 2×75×1.5 = 600 + 225 = 825 บาท ปรับตัวคูณ OT ตามระเบียบบริษัท
ข้อควรรู้
- ขึ้น
#####— ถ้าไม่ใช่คอลัมน์แคบ แปลว่าเวลาติดลบ ให้ใช้ MOD หรือเช็คว่าใส่เวลาเข้า-ออกสลับกัน - ปัดเวลาเข้าช่วง 15 นาที — ใช้ MROUND(B2,”0:15″) ก่อนคำนวณ
- เวลาที่เป็นข้อความ — ถ้า “8:30” ชิดซ้ายในเซลล์แปลว่าเป็นข้อความ ใช้
=TIMEVALUE(B2)แปลงก่อน ดู ฟังก์ชันเวลา - นับเฉพาะวันทำงาน — ถ้าต้องหักวันหยุด ใช้คู่กับ NETWORKDAYS
