การคำนวณชั่วโมงทำงานจากเวลาเข้า-ออกใน Excel มีจุดที่พลาดง่ายอยู่ 2 เรื่อง คือ Excel เก็บเวลาเป็น “เศษของวัน” และกะดึกที่ข้ามเที่ยงคืนจะได้ผลติดลบ บทความนี้รวมสูตรครบ ตั้งแต่คิดชั่วโมงต่อวัน หักพักเที่ยง รวมทั้งสัปดาห์ แยก OT ไปจนถึงคิดเป็นเงิน
พื้นฐาน: เวลาใน Excel คือเศษของวัน
08:00 = 8/24 = 0.333… เวลา 1 วันเต็ม = 1 การเอาเวลาออกลบเวลาเข้าจะได้ “เศษของวัน” ต้องจัดรูปแบบเซลล์ผลลัพธ์เป็น [h]:mm (วงเล็บเหลี่ยมทำให้แสดงเกิน 24 ชม. ได้) หรือคูณ 24 เพื่อให้เป็นชั่วโมงแบบทศนิยม
ตัวอย่างที่ 1: ชั่วโมงทำงานต่อวัน
=C2-B2| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | วัน | เข้า | ออก | ชั่วโมง (h:mm) | ชั่วโมง (ทศนิยม) |
| 2 | จันทร์ | 08:30 | 17:15 | 8:45 | 8.75 |
คอลัมน์ D จัดรูปแบบเป็น [h]:mm ส่วนคอลัมน์ E ใช้ =(C2-B2)*24 ได้ 8.75 ชั่วโมง เอาไปคูณค่าแรงต่อได้
ตัวอย่างที่ 2: กะดึกข้ามเที่ยงคืน
=MOD(C2-B2, 1)| A | B | C | D | |
|---|---|---|---|---|
| 1 | กะ | เข้า | ออก | ชั่วโมง |
| 2 | ดึก | 22:00 | 06:00 | 8:00 |
C2-B2 ตรงๆ จะได้ −16 ชั่วโมง (แสดงเป็น #####) การครอบด้วย MOD(…, 1) จะวนค่าลบให้กลับมาเป็นบวก ได้ 8:00 ชั่วโมงถูกต้อง
ตัวอย่างที่ 3: หักเวลาพักเที่ยง
=MOD(C2-B2, 1) - D2| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | วัน | เข้า | ออก | พัก | ชั่วโมงสุทธิ |
| 2 | อังคาร | 08:00 | 17:00 | 1:00 | 8:00 |
ใส่เวลาพักในคอลัมน์ 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)| A | F | E | G | |
|---|---|---|---|---|
| 1 | วัน | ชั่วโมงรวม | ปกติ (≤8) | OT (ส่วนเกิน) |
| 2 | พุธ | 10:00 | 8:00 | 2:00 |
ชั่วโมงปกติ = =MIN(F2, 8/24) (ไม่เกิน 8 ชม.) ส่วน OT = =MAX(0, F2 - 8/24) เลข 8/24 คือ 8 ชั่วโมงในหน่วยเศษของวัน
ตัวอย่างที่ 6: คิดเป็นเงิน
=E2*24*$B$8 + G2*24*$B$8*1.5| A | E | G | D | |
|---|---|---|---|---|
| 2 | พุธ | 8:00 | 2:00 | 825 |
| 8 | ค่าแรง/ชม. | 75 | ||
คูณ 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
