สูตรคํานวณวันหมดอายุ excel

ความรู้

สารบัญ Show

ไวยากรณ์
ข้อสังเกต
ตัวอย่าง
ปัญหาที่ทราบแล้ว
ต้องการความช่วยเหลือเพิ่มเติมไหม
ดูเพิ่มเติม
ต้องการความช่วยเหลือเพิ่มเติมหรือไม่

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

ไปลองดูตัวอย่างกันเลยจะได้เข้าใจง่ายและลองทำตามได้

ตัวอย่างที่ 1 คำนวณอายุโดยใช้สูตร (วันที่ปัจจุบัน - วันเกิด) / 365.25
ใน Excel เราสามารถคำนวณอายุของบุคคลได้โดยเรารู้วันเกิดและวันที่ปัจจุบัน ลองดูจากตัวอย่างด้านล่างนะ
ตอนนี้มีวันเกิดและวันที่ปัจจุบันแล้วต้องการหาอายุโดยใช้สูตร (วันที่ปัจจุบัน - วันเกิด) / 365.25

มีคำถามว่าทำไมถึงต้องหารด้วยตัวเลข 365.25 เพราะว่าทุกๆ 4 ปีเราจะพบกับปีอธิกสุรทินหรือปีที่เดือนกุมภาพันธ์มี 29 วันนั่นเองเลยต้องใช้การหารด้วย 365.25 ส่วนผลลัพธ์นั้นอาจจะไม่ใช่ตัวเลขเป็นจำนวนเต็มเรากก็จะใช้การปัดเศษโดยจะปัดลงหรือปัดขึ้นนั้นขึ้นอยู่กับความต้องการของเรานะครับ

ตัวอย่างที่ 2 คำนวณอายุโดยใช้คำสั่ง DATEDIF
คำสั่ง DATEDIF เป็นคำสั่งของ Excel ที่จะคืนค่าผลต่างระหว่างวันที่สองวันที่โดยจะคืนค่ากลับมาเป็นปี เดือนหรือวันแล้วแต่เรากำหนด
โดย DATEDIF จะเป็นคำสั่งที่ซ่อนไว้ของ Excel แต่เราก็สามารถเรียกใช้งานได้นะครับ เท่าที่ทดลองใช้ Excel จะไม่พบคำสั่งนี้ตั้งแต่ Excel รุ่น 2010 หรือ 2013 เป็นต้นมาแต่ก็ยังสามารถเรียกใช้งานได้นะ

คำสั่ง DATEDIF จะมีหน้าตาดังภาพ

โดยที่

start_date คือวันที่เริ่มต้นที่จะใช้ในการเปรียบเทียบ (ทั้งนี้ต้องใช้วันที่น้อยกว่าหรือเท่ากับ end_date เท่านั้นนะครับ ไม่เช่นนั้นจะส่งค่าเป็นข้อความผิดพลาด #NUM กลับมาให้เรา)
end_date คือวันที่ต้องการนำมาเปรียบเทียบ
unit คือหน่วยที่ต้องการคืนค่ากลับมาโดยมีตัวเลือกทางด้านล่าง
Y คืนค่ากลับมาเป็นปี
M คืนค่ากลับมาเป็นเดือน
D คืนค่ากลับมาเป็นจำนวนวัน
MD (Month Day Unit) จะหาค่าความแตกต่างในจำนวนวัน โดยจะไม่สนใจเดือนและปี (ง่ายๆ ก็คือถ้านับเดือนและปีเป็นจำนวนเต็มแล้วเหลือเศษของวันอยู่เท่าใด)
YM (Year Month Unit) จะหาค่าความแตกต่างในจำนวนเดือน โดยไม่สนใจวันและปี (คือนับปีเป็นจำนวนเต็มแล้วหาว่าเหลือเศษของเดือนอยู่เท่าใด)
YD (Year Date Unit) จะหาค่าความแตกต่างในจำนวนวัน โดยไม่สนใจปี (คือนับปีเป็นจำนวนเต็มเรียบร้อยแล้วดูว่าเหลือเศษของปีอยู่เท่าใด)

จากคำอธิบายแล้วอาจจะงง ไปลองดูตัวอย่างกันนะ

สมมติมีข้อมูลลักษณะแบบนี้แล้วต้องการหาอายุของพนักงานเป็นปีเดือนและวัน

มาลองคำนวณปี (Year) ที่คอลัมน์ E โดยใช้สูตร =DATEDIF(C2, D2, “Y”), โดยที่ C2 คือวันที่เกิดของพนักงานและ D2 คือวันที่ปัจจุบันโดยเขียนเป็นคำสั่ง =TODAY() ไว้ และเราจะใส่ unit ในคำสั่ง DATEDIF เป็น "Y" เพื่อให้คำสั่งส่งค่าคืนมาเป็นปี

ลองหาจำนวนเดือนด้วยสูตร =DATEDIF(C2, D2, "YM") โดย YM คือให้ DATEDIF ส่งค่ากลับมาเป็นผลต่างของจำนวนเดือนกัน
แล้วทำไมถึงไม่ใช้ M ก็เห็นว่า M ก็คืนค่ามาเป็นเดือน ที่เราไม่ใส่ M นั้นก็เพราะว่า M จะคำนวณเดือนทั้งหมด ไม่ได้คำนวณเศษเดือนที่เหลือเหมือน YM (ยังไงเอาไปลองกันดูนะครับ อธิบายอาจจะเข้าใจยาก)

ลองหาจำนวนเศษของวันโดยการใช้สูตร =DATEDIF(C2, D2, "MD") โดยที่ MD คือการบอกคำสั่ง DATEDIF ว่าให้หาเศษของวันที่เหลือหลังจากการเปรียบเทียบปีกับเดือนเรียบร้อยแล้ว

ความคิดเห็น

กรุณาเข้าสู่ระบบเพื่อทำการคอมเม้นต์

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel สำหรับเว็บ Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 SharePoint Foundation 2010 Excel 2010 SharePoint Server 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 Windows SharePoint Services 3.0 เพิ่มเติม...น้อยลง

คํานวณจํานวนวัน เดือน หรือปีระหว่างวันที่สองวัน

คำเตือน: Excel มีฟังก์ชัน DATEDIF เพื่อสนับสนุนเวิร์กบุ๊กที่เก่ากว่าจาก Lotus 1-2-3 ฟังก์ชัน DATEDIF อาจคํานวณผลลัพธ์ที่ไม่ถูกต้องภายใต้สถานการณ์บางอย่าง โปรดดูส่วนปัญหาที่ทราบแล้วของบทความนี้เพื่อดูรายละเอียดเพิ่มเติม

ไวยากรณ์

DATEDIF(start_date,end_date,unit)

อาร์กิวเมนต์

คำอธิบาย

start_date

จำเป็น

วันที่ซึ่งแสดงถึงวันที่เริ่มต้นหรือวันที่เริ่มต้นของช่วงเวลาที่ระบุ ใส่วันที่เป็นสตริงข้อความภายในเครื่องหมายอัญประกาศ (ตัวอย่างเช่น "2001/1/30") เป็นเลขลของคอมพิวเตอร์ (ตัวอย่างเช่น 36921 ซึ่งแทนวันที่ 30 มกราคม 2001 ถ้าคุณใช้ระบบวันที่ 1900) หรือเป็นผลลัพธ์ของสูตรหรือฟังก์ชันอื่นๆ (ตัวอย่างเช่น DATEVALUY("2001/1/30"))

end_date

จำเป็น

วันที่ซึ่งแสดงถึงวันที่สุดท้าย หรือวันที่สิ้นสุดของช่วงเวลา

Unit

ชนิดของข้อมูลที่คุณต้องการส่งกลับ โดย:

Unit	ส่งกลับ
"Y"	จำนวนปีแบบเต็มในช่วงเวลา
"M"	จำนวนเดือนแบบเต็มในช่วงเวลา
"D"	จำนวนวันในช่วงเวลา
"MD"	ผลต่างระหว่างจำนวนวันใน start_date และ end_date โดยไม่สนใจเดือนและปีของวันที่ทั้งสองวัน สิ่งสำคัญ: เราไม่แนะนำให้ใช้อาร์กิวเมนต์ “MD” เนื่องจากที่ข้อจำกัดที่ทราบแล้ว ดูส่วนปัญหาที่ทราบแล้วทางด้านล่าง
"YM"	ความแตกต่างระหว่างเดือนใน start_date end_date วันและปีของวันที่จะถูกละเว้น
"YD"	ผลต่างระหว่างจำนวนวันของ start_date และ end_date โดยไม่สนใจปีของวันที่ทั้งสองวัน

ข้อสังเกต

วันที่จะถูกจัดเก็บเป็นเลขลดับต่อเนื่องเพื่อให้สามารถใช้ในการคํานวณได้ ตามค่าเริ่มต้น วันที่ 1 มกราคม 1900 มีเลขลของคอมพิวเตอร์เป็น 1 และวันที่ 1 มกราคม พ.ศ. 2551 มีเลขล 39448 เนื่องจากอยู่หลังจากวันที่ 1 มกราคม 1900 ไป 39,447 วัน
ฟังก์ชัน DATEDIF มีประโยชน์ในสูตรที่คุณต้องการคำนวณหาอายุ
ถ้าค่าtart_dateมากกว่าค่า end_dateจะส่งผลให้เกิด#NUM!.

ตัวอย่าง

Start_date	End_date	สูตร	คำอธิบาย (ผลลัพธ์)
1/1/2001	1/1/2003	=DATEDIF(start_date,end_date,"Y")	สองปีเต็มในช่วงเวลา (2)
6/1/2001	8/15/2002	=DATEDIF(start_date,end_date,"D")	440 วันระหว่าง June 1, 2001 และ August 15, 2002 (440)
6/1/2001	8/15/2002	=DATEDIF(start_date,end_date,"YD")	75 วันระหว่าง June 1 และ August 15 ไม่สนใจส่วนที่เป็นปีของวันที่ (75)

ปัญหาที่ทราบแล้ว

อาร์กิวเมนต์ "MD" อาจทำให้ผลลัพธ์เป็นจำนวนติดลบ ค่าศูนย์ หรือผลลัพธ์ที่ไม่แม่นยำ ถ้าคุณพยายามคำนวณวันที่เหลือหลังจากเดือนที่เสร็จสมบูรณ์ล่าสุด นี่คือวิธีการแก้ไขปัญหาชั่วคราว

สูตรนี้จะลบวันแรกของเดือนสิ้นสุด (5/1/2016) จากวันที่สิ้นสุดดั้งเดิมในเซลล์ E17 (5/6/2016) นี่คือวิธีดำเนินการ: ก่อนอื่น ฟังก์ชัน DATE จะสร้างวันที่ 5/1/2016 ซึ่งจะสร้างโดยใช้ปีในเซลล์ E17 และเดือนในเซลล์ E17 จากนั้น 1 จะแสดงวันแรกของเดือน ผลลัพธ์สำหรับฟังก์ชัน DATE คือ 5/1/2016 จากนั้น เราจะลบจากวันที่สิ้นสุดดั้งเดิมในเซลล์ E17 ซึ่งก็คือ 5/6/2016 โดย 5/6/2016 ลบ 5/1/2016 ก็คือ 5 วัน