สูตรคำนวณอายุงานใน Excel ด้วย DATEDIF และสูตรวันที่สำหรับงาน HR

งาน HR มีเรื่องที่ต้องคำนวณจากวันที่อยู่ตลอด ทั้งอายุงาน อายุพนักงาน วันครบทดลองงาน วันเกษียณ จำนวนวันทำงาน และสิทธิ์ต่าง ๆ ที่ขึ้นกับอายุงาน ถ้าคำนวณด้วยมือหรือใช้วันที่ลบกันหาร 365 ตัวเลขจะคลาดเคลื่อนได้ บทความนี้รวมสูตร Excel สำหรับงาน HR ที่ใช้บ่อย โดยมี DATEDIF เป็นพระเอก

เรื่องที่ต้องรู้ก่อน: วันที่ใน Excel และปี พ.ศ.

Excel เก็บวันที่เป็นตัวเลข (จำนวนวันนับจาก 1 ม.ค. 1900) จึงลบกันได้ แต่ปัญหาที่ HR ไทยเจอบ่อยคือพิมพ์ปีเป็น พ.ศ. เช่น 15/06/2560 ซึ่ง Excel จะเข้าใจว่าเป็นปี ค.ศ. 2560 ทำให้อายุงานผิดไป 543 ปี

• แนะนำ: กรอกวันที่เป็น ค.ศ. แล้วตั้งรูปแบบเซลล์ให้แสดงเป็น พ.ศ. ด้วย Format Cells > Custom ใส่ d/m/bbbb (bbbb คือปีพุทธศักราช)
• ถ้าข้อมูลเดิมถูกกรอกเป็น พ.ศ. ไปแล้วและ Excel รับเป็นวันที่ ใช้สูตรแปลงในคอลัมน์ช่วย =DATE(YEAR(A2)-543, MONTH(A2), DAY(A2))
• ตรวจว่าเซลล์เป็นวันที่จริงด้วย =ISNUMBER(A2) ถ้าได้ FALSE แสดงว่าเป็นข้อความ ต้องแปลงก่อน

DATEDIF คืออะไร

=DATEDIF(start_date, end_date, unit)

DATEDIF คำนวณระยะห่างระหว่าง 2 วันที่ในหน่วยที่ต้องการ เป็นสูตรที่ไม่ขึ้นในรายการแนะนำของ Excel (ไม่มีคำใบ้ขณะพิมพ์) แต่ใช้ได้ทุกเวอร์ชัน

unit	ผลลัพธ์
"y"	จำนวนปีเต็ม
"m"	จำนวนเดือนเต็ม
"d"	จำนวนวัน
"ym"	เดือนที่เหลือหลังหักปีเต็ม
"yd"	วันที่เหลือหลังหักปีเต็ม
"md"	วันที่เหลือหลังหักเดือนเต็ม (Microsoft แจ้งว่าอาจให้ผลผิดในบางกรณี)

ข้อควรระวัง: start_date ต้องน้อยกว่า end_date เสมอ ถ้ากลับกันจะได้ #NUM!

สูตรคำนวณอายุงาน

สมมติ B2 คือวันเริ่มงาน

อายุงานเป็นปีเต็ม

=DATEDIF(B2, TODAY(), "y")

อายุงานแบบ "ปี เดือน"

=DATEDIF(B2, TODAY(), "y") & " ปี " & DATEDIF(B2, TODAY(), "ym") & " เดือน"

อายุงานแบบ "ปี เดือน วัน"

=DATEDIF(B2, TODAY(), "y") & " ปี " & DATEDIF(B2, TODAY(), "ym") & " เดือน " & DATEDIF(B2, TODAY(), "md") & " วัน"

ถ้าต้องการคำนวณ ณ วันที่ตัดรอบ (เช่น สิ้นปี) แทนวันนี้ ให้ใส่วันตัดรอบไว้ในเซลล์ เช่น $H$1 แล้วใช้แทน TODAY() ทุกจุด ตัวเลขจะไม่เปลี่ยนทุกครั้งที่เปิดไฟล์

อายุงานเป็นปีทศนิยม

=ROUND(YEARFRAC(B2, TODAY(), 1), 2)

ได้ผลเช่น 5.27 ปี เหมาะกับการคำนวณที่ต้องการสัดส่วน เช่น โบนัสตามอายุงาน

สูตรคำนวณอายุพนักงาน

=DATEDIF(C2, TODAY(), "y")

C2 คือวันเกิด ใช้ทำรายงานกำลังคนแยกตามช่วงอายุ หรือนำไปจัดกลุ่มใน Pivot Table ทีละ 10 ปี

สูตรวันครบทดลองงาน วันเกษียณ และวันครบรอบ

ต้องการ	สูตร
ครบทดลองงาน 119 วัน (นับวันเริ่มงานเป็นวันที่ 1)	=B2+118
ครบทดลองงาน 3 เดือน	=EDATE(B2, 3)-1
วันเกษียณ อายุ 60 ปี	=EDATE(C2, 60*12)
เกษียณสิ้นเดือนที่ครบ 60 ปี	=EOMONTH(C2, 60*12)
เกษียณสิ้นปีงบประมาณ (30 ก.ย.) ที่ครบ 60 ปี	=DATE(YEAR(C2)+60+(MONTH(C2)>9), 9, 30)
ครบรอบวันเริ่มงานปีนี้	=DATE(YEAR(TODAY()), MONTH(B2), DAY(B2))

กฎการนับวันทดลองงานและอายุเกษียณขึ้นกับนโยบายของแต่ละบริษัท ปรับตัวเลขในสูตรให้ตรงกับระเบียบของคุณ

นับจำนวนวันทำงาน ไม่รวมวันหยุด

=NETWORKDAYS(B2, C2, วันหยุด!$A$2:$A$20)

นับวันทำงานจันทร์–ศุกร์ระหว่าง B2 ถึง C2 และหักวันหยุดนักขัตฤกษ์ที่ใส่ไว้ในชีต "วันหยุด" ใช้คำนวณวันทำงานของพนักงานเข้าใหม่ระหว่างเดือน หรือจำนวนวันลา

ถ้าบริษัททำงานวันเสาร์ด้วย (หยุดเฉพาะวันอาทิตย์) ใช้ NETWORKDAYS.INTL แล้วกำหนดวันหยุดเป็นรหัส 11

=NETWORKDAYS.INTL(B2, C2, 11, วันหยุด!$A$2:$A$20)

หรือใช้ข้อความ 7 หลักแทนวันจันทร์–อาทิตย์ (1 = หยุด, 0 = ทำงาน) เช่น "0000011" คือหยุดเสาร์–อาทิตย์

ใช้อายุงานคำนวณสิทธิ์ต่าง ๆ

วันลาพักร้อนตามอายุงาน

ทำตารางสิทธิ์แยกไว้ในชีต "สิทธิ์ลา" แล้วดึงด้วย XLOOKUP แบบหาค่าที่น้อยกว่าถัดไป

A: อายุงานขั้นต่ำ (ปี)	B: วันลาพักร้อน
1	6
3	8
5	10
10	12

=XLOOKUP(D2, สิทธิ์ลา!$A$2:$A$5, สิทธิ์ลา!$B$2:$B$5, 0, -1)

D2 คืออายุงานเป็นปีเต็ม คนที่ทำงานไม่ถึง 1 ปีจะได้ 0 จากช่อง if_not_found ตัวเลขในตารางเป็นตัวอย่าง ให้ใส่ตามระเบียบบริษัท

ตัวอย่าง: อัตราค่าชดเชยตามอายุงาน

ใช้หลักเดียวกันกับตารางค่าชดเชยตามกฎหมายคุ้มครองแรงงาน โดยคำนวณอายุงานเป็นจำนวนวัน (=DATEDIF(B2, วันสิ้นสุด, "d")) แล้วเทียบกับตารางขั้นอายุงาน เช่น ครบ 120 วัน = 30 วัน, ครบ 1 ปี = 90 วัน, ครบ 3 ปี = 180 วัน, ครบ 6 ปี = 240 วัน, ครบ 10 ปี = 300 วัน และครบ 20 ปี = 400 วัน ควรตรวจสอบอัตราล่าสุดกับกฎหมายหรือฝ่ายกฎหมายก่อนใช้จริงทุกครั้ง

ปัญหาที่พบบ่อย

• #NUM! วันเริ่มมากกว่าวันสิ้นสุด มักเกิดกับพนักงานที่ยังไม่เริ่มงาน ครอบด้วย =IF(B2>TODAY(), "ยังไม่เริ่มงาน", DATEDIF(B2, TODAY(), "y"))
• #VALUE! วันที่เป็นข้อความ เช่น นำเข้าจากระบบมาเป็น "15.06.2017" ใช้ Data > Text to Columns เลือกรูปแบบวันที่ DMY เพื่อแปลง
• อายุงานเกิน 500 ปี วันที่ถูกกรอกเป็นปี พ.ศ. ใช้สูตรแปลงด้านบน
• ผลลัพธ์แสดงเป็นวันที่แปลก ๆ เซลล์ผลลัพธ์ถูกจัดรูปแบบเป็น Date ให้เปลี่ยนเป็น General

สรุป

DATEDIF ร่วมกับ TODAY, EDATE, EOMONTH, NETWORKDAYS และ XLOOKUP ครอบคลุมงานคำนวณวันที่ของ HR เกือบทั้งหมด สิ่งสำคัญที่สุดคือกรอกวันที่ให้ Excel รู้จักเป็นวันที่จริง และแยกตารางสิทธิ์ออกจากสูตร เพื่อให้แก้นโยบายได้ในที่เดียว

ถ้าทีม HR ต้องการเรียนสูตรเหล่านี้กับข้อมูลพนักงาน วันลา และรายงานกำลังคนแบบที่ใช้จริง ดูหลักสูตรอบรม Excel HR หรืออ่าน20 สูตร Excel พื้นฐานเพื่อทบทวนก่อน สำหรับองค์กรที่ต้องการให้วิทยากรสอน Excel ให้ฝ่ายบุคคลทั้งทีม ดูรายละเอียดอบรม In-house