วันอังคารที่ 16 ตุลาคม พ.ศ. 2555

excel คำนวณวันเกษียณ ด้วย Datedif()

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


ผมรับปากว่าจะช่วย พอทำเสร็จคิดว่า น่าจะเป็นประโยชน์สำหรับคนที่ทำหน้าที่บุคลากร หรือเป็นแนวทางในการคำนวณเรื่องของอายุ ก็เลยนำมาไว้ที่นี่ ให้ดาวน์โหลดกัน

การคำนวณอายุ เราใช้ฟังก์ชัน Datedif()

รูปแบบการใช้งาน มีดังนี้
=DATEDIF(Date1, Date2, Interval)
  • Date1 คือ วัน เดือน ปี เริ่มต้น ข้อมูลต้องเป็นวันที่นะครับ เช่น 15/6/2551 ถ้าคำนวณหาอายุ ตัวนี้จะใช้เป็น วัน เดือน ปี เกิด
  • Date2 คือ วัน เดือน ปี สิ้นสุด ถ้าคำนวณอายุ ตัวนี้ก็คือวันที่ในปัจจุบัน ส่วนมากจะใช้ฟังก์ชัน today() แต่ดูให้ดีนะครับ ถ้ากำหนดรูปแบบเป็นแบบตะวันตก ต้องบวกด้วย 543 แต่ถ้า Excel2010 กำหนดรูปแบบวันที่เป็นแบบไทย ปีพุทธศักราช ก็แล้วไป สำหรับในแบบฟอร์ม ใช้วันที่ที่กำหนดในเซลล E2 ก็เลยไม่ต้องวิตกเรื่องนี้ (แนะนำให้ ดาวน์โหลดไฟล์ มาศึกษาดูด้วยก็ดี)
  • Interval เป็นรูปแบบที่ต้องการ เช่น จะให้เป็นปี เป็นเดือน หรือ เป็นวัน ก็ กำหนดที่นี่ เช่น "y" หมายถึง ปี "ym" หมายถึงเศษของเดือนในปีที่เหลือ และ "md" คือ วันที่เหลือจากเศษของเดือน เป็นต้น 
ถ้า Date1 มากกว่า หรือเลย Date2 ฟังก์ชัน DATEDIF() จะแสดงค่าผิดพลาดเป็น #NUM! และถ้าเขียนไม่ถูกรูปแบบวันที่ ไม่ว่าจะเป็น Date1 หรือ Date2 ก็ตามจะเกิดข้อผิดพลาด  #VALUE
 
ค่า Interval ที่ใช้ได้ มีดังนี้
 
ค่าความหมายอธิบาย
mเดือนจำนวนเดือนทั้งหมด ที่มีในระหว่างวันที่ ที่กำหนด
dวันจำนวนวันทั้งหมด ที่มีในระหว่างวันที่ ที่กำหนด
yปีจำนวนปีทั้งหมด  ที่มีในระหว่างวันที่ ที่กำหนด
ymเศษเดือนที่เหลือเศษของเดือนที่เหลือไม่ถึงปี
ydเศษวันที่เหลือปีเศษของวันที่เหลือไม่ถึงปี (เอาเศษเดือนมาคิดเป็นวัน)
mdเศษวันที่เหลือเดือนเศษของวันที่เหลือไม่ถึงเดือน

ถ้าไม่ใช่รูปแบบที่กำหนดข้างต้น ฟังก์ชันจะแสดงค่าผิดพลาดเป็น  #NUM

ค่า Interval ถ้าใส่โดยตรง ต้องอยู่ในเครื่องหมายคำพูด เช่น

=DATEDIF(Date1,Date2,"m")

แต่ถ้าอ้างอิงมาจากเซลล์อื่น ก็ไม่ต้องมีเครื่องหมายคำพูด เช่น

=DATEDIF(Date1,Date2,A1)

ในแบบฟอร์ม ใน Sheet2 บอกว่า ขอให้เอาข้อมูลมารวมกันในเซลล์เดียว ดังนั้นจึงต้องมีการรวม วัน เดือน ปี ที่คำนวณได้มาไว้ในเซลล์เดียวกัน โดยใช้เครื่องหมาย & เชื่อม เช่น

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

ซึ่งจะแสดง ดังนี้ เช่น   12 ปี 8  เดือน 14 วัน

ลองดาวน์โหลด แล้วศึกษาดูนะครับ

อ้างอิง
http://www.cpearson.com/excel/datedif.aspx

 

5 ความคิดเห็น:

  1. ดิฉันเกิด 19 มกราคม 2523
    รับราชการวันที่ 17 ธันวาคม 2558
    ต้องเกษียณวันที่ 30 กันยายน 2583
    ซึ่งคำนวณอายุราชการแล้วจะไม่ถึง 25 ปีเต็ม จะได้เพียง 24 ปี กับอีกประมาณ 9 เดือน กับ 13 วัน ถูกต้องไหมคะ..
    ถ้าเป็นเช่นนี้ แสดงว่าดิฉันจะไม่มีสิทธิ์รับบำนาญใช่หรือไม่คะ

    ตอบลบ
    คำตอบ
    1. ไม่เกี่ยวกันครับ..บำนาญเกียณอายุ 60 ปีก็ได้อยู่แล้ว เพียงแต่ท่านต้องอยู่ถึง 60 ปีลาออกก่อนไม่ได้เท่านั้นคือไม่ครบ 25 ปี

      ลบ
  2. ให้บันทึกเก็บไว้นะครับ
    มีประโยชน์มาก
    เกษียณแล้วได้อะไรบ้าง


    1)- ไม่เป็นสมาชิก กบข.
    ก): กรณีรับบำเหน็จ = เงินเดือนเดือนสุดท้าย x อายุราชการ(รวมอายุราชการทวีคูณ)
    กรณีนี้จะได้รับเงินก้อนเดียว
    สิทธิต่าง ๆ ระงับไป ยกเว้นการขอพระราชทานเพลิงศพ
    ข): กรณีรับบำนาญ = เงินเดือนเดือนสุดท้าย x อายุราชการ (รวมอายุราชการทวีคูณ เกิน 6 เดือนนับเป็น 1 ปี) หาร 50
    กรณีนี้จะได้รับเงินทุกเดือนจนกว่าจะเสียชีวิต และยังมีสิทธิได้รับ
    1. ค่ารักษาพยาบาลของตนเอง คู่สมรสและบิดามารดา บุตรที่ไร้ความสามารถ ยกเว้นบุตรปกติที่อายุครบ 20 ปีบริบูรณ์
    2. ค่าเล่าเรียนบุตร เบิกได้ถึงอายุ 25 ปีบริบูรณ์
    3. บำเหน็จดำรงชีพ = เงินบำนาญ x 15 เท่า
    เมื่อเกษียณได้รับเลย 200,000.-฿
    เมื่ออายุครบ 65 ปีขอรับได้อีก 400,000.-฿ รวม 2 ครั้งไม่เกิน 600,000.-฿ แต่ทั้งหมดนี้ไม่เกิน 15 เท่าของเงินบำนาญ
    4. เงินช่วยพิเศษ (ถึงแก่กรรม) = เงินบำนาญ x 3 เท่า
    มอบให้กับผู้ที่ผู้รับบำนาญแสดงเจตนาหรือทายาทตามกฎหมาย
    5. เงินบำเหน็จตกทอด (ถึงแก่กรรม) = เงินบำนาญ x 30 เท่า – เงินบำเหน็จดำรงชีพที่เบิกไปแล้ว
    มอบให้กับทายาทตามกฎหมายหรือผู้ที่ผู้รับบำนาญแสดงเจตนา (กรณีที่ไม่มีทายาท)
    และถ้าไม่มีผู้รับให้สิทธิบำเหน็จตกทอดเป็นอันยุติลง

    2)- เป็นสมาชิก กบข.
    ก): กรณีรับบำเหน็จ = เงินเดือนเดือนสุดท้าย (เศษเดือนเศษวันเป็นจุดทศนิยม x อายุราชการ (รวมอายุราชการทวีคูณ)
    กรณีนี้จะได้รับเงินก้อนเดียว
    สิทธิต่าง ๆ ระงับไป ยกเว้นการขอพระราชทานเพลิงศพ
    ข): กรณีรับบำนาญ = เงินเดือนเฉลี่ย 60 เดือนสุดท้าย x อายุราชการ (รวมอายุราชการทวีคูณ เป็นจุดทศนิยม) หารด้วย 50
    แต่ทั้งนี้ต้องไม่เกิน 70% ของเงินเดือนเฉลี่ย 60 เดือน
    กรณีนี้จะได้รับเงินทุกเดือนจนกว่าจะเสียชีวิต และยังมีสิทธิได้รับสิทธิต่าง ๆ เหมือนกับบำนาญปกติ

    สมาชิก กบข.แบบ ก.,ข. ยังได้รับเงิน
    1. เงินสะสม + ผลประโยชน์
    2. เงินประเดิม + ผลประโยชน์
    3. เงินชดเชย + ผลประโยชน์
    4. เงินสมทบ + ผลประโยชน์
    ส่วนผู้ที่ลาออกจาก กบข.จะได้เงินสะสมของตนเองคืน

    3)- เงินต่าง ๆ ที่สมัครเป็นสมาชิก
    ก): เงินฌาปนกิจสงเคราะห์
    ข): เงินผลประโยชน์จากหุ้นสหกรณ์
    ค): เงินประกันชีวิตและเงินช่วยเหลือจากสหกรณ์
    กรณีผู้รับบำนาญตายผู้ที่ได้รับคือผู้ที่ผู้รับบำนาญแสดงเจตนาหรือทายาทตามกฎหมาย

    หมายเหตุ
    ทายาทตามกฎหมายได้แก่
    1. บุตรที่ชอบด้วยกฎหมาย ได้รับคนละ 1 ส่วน
    2. สามีภรรยาที่ชอบด้วยกฎหมาย ได้รับ 1 ส่วน
    3. บิดามารดาหรือบิดาหรือมารดาที่มีชีวิตอยู่ ได้รับ 1 ส่วน

    ): ประกาศราชการทวีคูณ
    – ช่วงที่ 1 วันที่ 19 ก.ย.49 – 26 ม.ค.50 = 4 เดือน 11 วัน
    – ช่วงที่ 2 วันที่ 20 พ.ค.57 – 1 เม.ย.58 = 11 เดือน 7 วัน
    รวม 2 ช่วง = 15 เดือน 18 วัน

    ตอบลบ
  3. ขอถามหน่อยครับ ผมทำงานเอกชน เกิด 30 พย. 04 เท่ากับสิ้นปี 2559 ครบเกษียณไหมครับ

    ตอบลบ