บทช่วยสอน Excel: การคำนวณ DateTime (คำนวณส่วนต่าง อายุ บวก/ลบ)

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

การนำทางของบทช่วยสอนนี้

1. คำนวณความแตกต่างระหว่างสองวัน/เวลา 1.1 คำนวณผลต่างวัน/เดือน/ปีระหว่างวันที่สองวัน 1.11 คำนวณความแตกต่างระหว่างสองวันในวัน/เดือน/ปี/สัปดาห์ 1.12 คำนวณเดือนละเว้นปีและวันระหว่างวันที่สองวัน 1.13 คำนวณวันละเว้นปีและเดือนระหว่างวันที่สองวัน 1.14 คำนวณผลต่างระหว่างสองวันกับปีเดือนและวันคืน 1.15 คำนวณความแตกต่างระหว่างวันที่กับวันนี้ 1.16 คำนวณวันทำงานโดยมีหรือไม่มีวันหยุดระหว่างสองวัน 1.17 คำนวณวันหยุดสุดสัปดาห์ระหว่างสองวัน 1.18 คำนวณวันทำงานเฉพาะระหว่างวันที่สองวัน 1.19 คำนวณวันคงเหลือในเดือน/ปี

1.2 คำนวณผลต่างระหว่างสองครั้ง 1.21 คำนวณผลต่างระหว่างสองครั้ง 1.22 คำนวณความแตกต่างระหว่างสองครั้งในหน่วยชั่วโมง/นาที/วินาที 1.23 คำนวณผลต่างของชั่วโมงระหว่างสองครั้งเท่านั้น (ไม่เกิน 24 ชั่วโมง) 1.24 คำนวณความแตกต่างของนาทีระหว่างสองครั้งเท่านั้น (ไม่เกิน 60 นาที) 1.25 คำนวณความแตกต่างของวินาทีระหว่างสองครั้งเท่านั้น (ไม่เกิน 60 วินาที) 1.26 คำนวณผลต่างระหว่างสองครั้งกับชั่วโมง นาที วินาที 1.27 คำนวณความแตกต่างระหว่างสองวันเวลา 1.28 คำนวณความแตกต่างของเวลาด้วยมิลลิวินาที 1.29 คำนวณชั่วโมงทำงานระหว่างสองวันไม่รวมวันหยุดสุดสัปดาห์

1.3 คำนวณความแตกต่างระหว่างสองวันกับ Kutools for Excel 1.31 คำนวณความแตกต่างระหว่างสองวันเวลาโดย Data & Time Helper 1.32 คำนวณความแตกต่างของวันหยุดสุดสัปดาห์/วันทำงาน/เฉพาะระหว่างวันสองวันโดย Formula Helper

1.4 รวมสองคอลัมน์ถ้าว่าง 1.41 การใช้ฟังก์ชัน IF 1.42 การใช้VBA

2. เพิ่มหรือลบวันที่และเวลา 2.1 เพิ่มหรือลบวัน/เดือน/ปี/สัปดาห์/วันทำงานเป็นวันที่ 2.11 เพิ่มหรือลบวันในวันที่ 2.12 เพิ่มหรือลบเดือนเป็นวันที่ 2.13 บวกหรือลบปีถึงวันที่ 2.14 เพิ่มหรือลบสัปดาห์เป็นวันที่ 2.15 เพิ่มหรือลบวันทำงานรวมทั้งหรือไม่รวมวันหยุด 2.16 บวกหรือลบเฉพาะปี เดือน วัน จนถึงวันที่

2.2 เพิ่มหรือลบชั่วโมง/นาที/วินาทีเป็นครั้ง 2.21 เพิ่มหรือลบชั่วโมง/นาที/วินาทีเป็น datetime 2.22 ผลรวมครั้งใน 24 ชั่วโมง 2.23 เพิ่มชั่วโมงทำงานในวันที่ไม่รวมวันหยุดสุดสัปดาห์และวันหยุด

2.3 เพิ่มหรือลบวันที่ / เวลาโดย Kutools for Excel

2.4 ส่วนขยาย 2.41 ตรวจสอบหรือไฮไลท์ว่าวันหมดอายุหรือไม่ 2.42 คืนสิ้นเดือนปัจจุบัน/วันแรกของเดือนถัดไป

3. คำนวณอายุ 3.1 คำนวณอายุตามวันที่ 3.11 คำนวณอายุตามวันเกิดที่กำหนด 3.12 คำนวณอายุในรูปแบบปี เดือน และวันตามวันเกิดที่กำหนด 3.13 คำนวณอายุตามวันเกิดก่อน 1/1/1900

3.2 คำนวณอายุตามการเกิดโดยใช้ Kutools for Excel

3.3 คำนวณอายุหรือวันเกิดตามหมายเลขชุด 3.31 รับวันเกิดจากเลขบัตรประชาชน 3.32 คำนวณอายุจากเลขประจำตัวประชาชน

 

ในบทช่วยสอนนี้ ฉันสร้างตัวอย่างเพื่ออธิบายวิธีการต่างๆ คุณสามารถเปลี่ยนข้อมูลอ้างอิงที่คุณต้องการได้เมื่อคุณใช้โค้ดหรือสูตร VBA ด้านล่าง

---

1. คำนวณความแตกต่างระหว่างสองวัน/เวลา

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

1.11 คำนวณความแตกต่างระหว่างสองวันในวัน/เดือน/ปี/สัปดาห์

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

คลิกดูรายละเอียดเพิ่มเติมเกี่ยวกับ วันที่ ฟังก์ชัน

ความแตกต่างของวันระหว่างสองวันที่

หากต้องการทราบความแตกต่างของวันระหว่างวันที่สองวันในเซลล์ A2 และ B2 โปรดใช้สูตรดังนี้

=DATEDIF(A2,B2,"d")

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

ความแตกต่างของเดือนระหว่างวันที่สองวัน

หากต้องการทราบความแตกต่างของเดือนระหว่างวันที่สองวันในเซลล์ A5 และ B5 โปรดใช้สูตรดังนี้

=DATEDIF(A5,B5,"m")

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

ความแตกต่างระหว่างปีระหว่างสองวัน

หากต้องการทราบความแตกต่างของจำนวนปีระหว่างวันที่สองวันในเซลล์ A8 และ B8 โปรดใช้สูตรดังนี้

=DATEDIF(A8,B8,"y")

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

สัปดาห์ที่ต่างกันระหว่างสองวัน

หากต้องการทราบความแตกต่างของสัปดาห์ระหว่างวันที่สองวันในเซลล์ A11 และ B11 โปรดใช้สูตรดังนี้

=DATEDIF(A11,B11,"d")/7

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

หมายเหตุ

1) เมื่อคุณใช้สูตรข้างต้นเพื่อให้ได้ผลต่างสัปดาห์ อาจส่งคืนผลลัพธ์ในรูปแบบวันที่ คุณต้องจัดรูปแบบผลลัพธ์เป็นแบบทั่วไปหรือตัวเลขตามที่คุณต้องการ

2) เมื่อคุณใช้สูตรข้างต้นเพื่อหาค่าส่วนต่างสัปดาห์ มันอาจกลับเป็นเลขฐานสิบ หากคุณต้องการได้เลขสัปดาห์จำนวนเต็ม คุณสามารถเพิ่มฟังก์ชัน ROUNDDOWN ก่อนดังที่แสดงด้านล่างเพื่อรับผลต่างสัปดาห์จำนวนเต็ม:

=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)

1.12 คำนวณเดือนละเว้นปีและวันระหว่างวันที่สองวัน

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

=DATEDIF(A2,B2,"ym")

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

A2 คือวันที่เริ่มต้น และ B2 คือวันที่สิ้นสุด

1.13 คำนวณวันละเว้นปีและเดือนระหว่างวันที่สองวัน

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

=DATEDIF(A5,B5,"md")

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

A5 คือวันที่เริ่มต้น และ B5 คือวันที่สิ้นสุด

1.14 คำนวณผลต่างระหว่างสองวันกับปีเดือนและวันคืน

หากคุณต้องการทราบความแตกต่างระหว่างสองวันที่และผลตอบแทน xx ปี xx เดือน และ xx วัน ตามที่แสดงด้านล่างนี้ นี่คือสูตรที่ให้ไว้ด้วย

=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

A8 คือวันที่เริ่มต้น และ B8 คือวันที่สิ้นสุด

1.15 คำนวณความแตกต่างระหว่างวันที่กับวันนี้

ในการคำนวณความแตกต่างระหว่างวันที่กับวันนี้โดยอัตโนมัติ เพียงเปลี่ยน end_date ในสูตรด้านบนเป็น TODAY() ต่อไปนี้ ให้ใช้การคำนวณความแตกต่างของวันระหว่างวันที่ในอดีตกับวันนี้เป็นตัวอย่าง

=DATEDIF(A11,TODAY(),"d")

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

หมายเหตุ: หากคุณต้องการคำนวณความแตกต่างระหว่างวันที่ในอนาคตกับวันนี้ ให้เปลี่ยน start_date เป็นวันนี้ และใช้วันที่ในอนาคตเป็น end_date ดังนี้:

=DATEDIF(TODAY(),A14,"d")

สังเกตว่า start_date ต้องน้อยกว่า end_date ในฟังก์ชัน DATEDIF ไม่เช่นนั้นจะกลับไปเป็น #NUM! ค่าความผิดพลาด

1.16 คำนวณวันทำงานโดยมีหรือไม่มีวันหยุดระหว่างสองวัน

บางครั้ง คุณอาจต้องนับจำนวนวันทำงานโดยมีหรือไม่มีวันหยุดระหว่างวันที่กำหนดสองวัน

ในส่วนนี้ คุณจะใช้ฟังก์ชัน NETWORKDAYS.INTL:

คลิก NETWORKDAYS.INTL เพื่อทราบข้อโต้แย้งและการใช้งาน

นับวันทำงานด้วยวันหยุด

หากต้องการนับวันทำงานที่มีวันหยุดระหว่างวันที่สองวันในเซลล์ A2 และ B2 โปรดใช้สูตรดังนี้:

=NETWORKDAYS.INTL(A2,B2)

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

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

หากต้องการนับวันทำงานที่มีวันหยุดระหว่างวันที่สองวันในเซลล์ A2 และ B2 และไม่รวมวันหยุดในช่วง D5:D9 โปรดใช้สูตรดังนี้:

=NETWORKDAYS.INTL(A5,B5,1,D5:D9)

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

หมายเหตุ

ในสูตรข้างต้น จะใช้วันเสาร์และวันอาทิตย์เป็นวันหยุดสุดสัปดาห์ หากคุณมีวันหยุดสุดสัปดาห์ต่างกัน โปรดเปลี่ยนอาร์กิวเมนต์ [สุดสัปดาห์] ตามที่คุณต้องการ

1.17 คำนวณวันหยุดสุดสัปดาห์ระหว่างสองวัน

หากคุณต้องการนับจำนวนวันหยุดสุดสัปดาห์ระหว่างวันที่สองวัน ฟังก์ชัน SUMPRODUCT หรือ SUM สามารถช่วยคุณได้

ในการนับวันหยุดสุดสัปดาห์ (วันเสาร์และวันอาทิตย์) ระหว่างวันที่สองวันในเซลล์ A12 และ B12:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))

Or

=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

1.18 คำนวณวันทำงานเฉพาะระหว่างวันที่สองวัน

หากต้องการนับจำนวนวันทำงานที่ระบุ เช่น วันจันทร์ระหว่างวันที่สองวัน ฟังก์ชัน INT และ WEEKDAY จะช่วยคุณได้

เซลล์ A15 และ B15 เป็นวันที่สองวันที่คุณต้องการนับระหว่างวันจันทร์ โปรดใช้สูตรดังนี้:

=INT((WEEKDAY(A15- 2)-A15 +B15)/7)

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

เปลี่ยนจำนวนวันทำงานในฟังก์ชัน WEEKDAY เพื่อนับวันทำงานอื่น:

1 คือวันอาทิตย์ 2 คือวันจันทร์ 3 คือวันอังคาร 4 คือวันพุธ 5 คือวันพฤหัสบดี 6 คือวันศุกร์และ 7 คือวันเสาร์)

1.19 คำนวณวันคงเหลือในเดือน/ปี

บางครั้ง คุณอาจต้องการทราบวันที่เหลือในเดือนหรือปีตามวันที่ที่ระบุดังภาพหน้าจอด้านล่าง:

รับจำนวนวันที่เหลือในเดือนปัจจุบัน

คลิก อีเดือน เพื่อทราบข้อโต้แย้งและการใช้งาน

เพื่อให้ได้วันที่เหลือของเดือนปัจจุบันในเซลล์ A2 โปรดใช้สูตรดังนี้:

=EOMONTH(A2,0)-A2

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

เคล็ดลับ: ผลลัพธ์อาจแสดงเป็นรูปแบบวันที่ เพียงแค่เปลี่ยนเป็นรูปแบบทั่วไปหรือรูปแบบตัวเลข

รับจำนวนวันที่เหลือในปีปัจจุบัน

เพื่อให้ได้วันที่เหลือของปีปัจจุบันในเซลล์ A2 โปรดใช้สูตรดังนี้:

=DATE(YEAR(A2),12,31)-A2

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

---

1.21 คำนวณผลต่างระหว่างสองครั้ง

เพื่อให้ได้ความแตกต่างระหว่างสองครั้ง ต่อไปนี้เป็นสูตรง่ายๆ สองสูตรที่สามารถช่วยคุณได้

สมมติว่าในเซลล์ A2 และ B2 มี start_time และ end_time แยกกัน โดยใช้สูตรดังนี้:

=B2-A2

=TEXT(B2-A2,"hh:mm:ss")

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

หมายเหตุ

• ถ้าคุณใช้ end_time-start_time คุณสามารถจัดรูปแบบผลลัพธ์เป็นรูปแบบเวลาอื่นตามที่คุณต้องการในกล่องโต้ตอบจัดรูปแบบเซลล์
• หากคุณใช้ TEXT(end_time-first_time,"time_format") ให้ป้อนรูปแบบเวลาที่คุณต้องการให้ผลลัพธ์แสดงในสูตร เช่น TEXT(end_time-first_time,"h") จะส่งกลับ 16
• ถ้า end_time น้อยกว่า start_time ทั้งสองสูตรจะคืนค่าความผิดพลาด สำหรับการแก้ปัญหานี้ คุณสามารถเพิ่ม ABS ที่ด้านหน้าของสูตรเหล่านี้ เช่น ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")) แล้วจัดรูปแบบผลลัพธ์เป็นเวลา

1.22 คำนวณความแตกต่างระหว่างสองครั้งในหน่วยชั่วโมง/นาที/วินาที

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

รับความแตกต่างของชั่วโมงระหว่างสองครั้ง

หากต้องการทราบความแตกต่างของชั่วโมงระหว่างสองครั้งใน A5 และ B5 โปรดใช้สูตรดังนี้:

=INT((B5-A5)*24)

ข่าวประชา เข้าสู่ คีย์ จากนั้นจัดรูปแบบผลลัพธ์ของรูปแบบเวลาเป็นแบบทั่วไปหรือตัวเลข

หากคุณต้องการได้ส่วนต่างชั่วโมงทศนิยม ให้ใช้ (end_time-start_time)*24

รับความแตกต่างเป็นนาทีระหว่างสองครั้ง

หากต้องการทราบความแตกต่างของนาทีระหว่างสองครั้งใน A8 และ B8 โปรดใช้สูตรดังนี้:

=INT((B8-A8)*1440)

ข่าวประชา เข้าสู่ คีย์ จากนั้นจัดรูปแบบผลลัพธ์ของรูปแบบเวลาเป็นแบบทั่วไปหรือตัวเลข

หากคุณต้องการได้ส่วนต่างนาทีทศนิยม ให้ใช้ (end_time-start_time)*1440

รับความแตกต่างวินาทีระหว่างสองครั้ง

หากต้องการทราบความแตกต่างของวินาทีระหว่างสองครั้งใน A5 และ B5 โปรดใช้สูตรดังนี้:

=(B11-A11)*86400)

ข่าวประชา เข้าสู่ คีย์ จากนั้นจัดรูปแบบผลลัพธ์ของรูปแบบเวลาเป็นแบบทั่วไปหรือตัวเลข

1.23 คำนวณผลต่างของชั่วโมงระหว่างสองครั้งเท่านั้น (ไม่เกิน 24 ชั่วโมง)

หากความแตกต่างระหว่างสองครั้งไม่เกิน 24 ชั่วโมง ฟังก์ชัน HOUR สามารถรับส่วนต่างของชั่วโมงระหว่างสองครั้งนี้ได้อย่างรวดเร็ว

คลิก ชั่วโมง สำหรับรายละเอียดเพิ่มเติมเกี่ยวกับฟังก์ชันนี้

หากต้องการทราบความแตกต่างของชั่วโมงระหว่างเวลาในเซลล์ A14 และ B14 โปรดใช้ฟังก์ชัน HOUR ดังนี้:

=HOUR(B14-A14)

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

start_time ต้องน้อยกว่า end_time มิฉะนั้น สูตรจะส่งกลับ #NUM! ค่าความผิดพลาด

1.24 คำนวณความแตกต่างของนาทีระหว่างสองครั้งเท่านั้น (ไม่เกิน 60 นาที)

ฟังก์ชัน MINUTE สามารถรับความแตกต่างของนาทีระหว่างสองครั้งนี้ได้อย่างรวดเร็ว และละเว้นชั่วโมงและวินาที

คลิก นาที สำหรับรายละเอียดเพิ่มเติมเกี่ยวกับฟังก์ชันนี้

หากต้องการทราบความแตกต่างของนาทีระหว่างเวลาในเซลล์ A17 และ B17 โปรดใช้ฟังก์ชัน MINUTE ดังนี้

=MINUTE(B17-A17)

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

start_time ต้องน้อยกว่า end_time มิฉะนั้น สูตรจะส่งกลับ #NUM! ค่าความผิดพลาด

1.25 คำนวณความแตกต่างของวินาทีระหว่างสองครั้งเท่านั้น (ไม่เกิน 60 วินาที)

ฟังก์ชัน SECOND สามารถรับส่วนต่างของวินาทีระหว่างสองครั้งนี้ได้อย่างรวดเร็ว และละเว้นชั่วโมงและนาที

คลิก นาปรัง สำหรับรายละเอียดเพิ่มเติมเกี่ยวกับฟังก์ชันนี้

หากต้องการทราบความแตกต่างระหว่างเวลาในเซลล์ A20 และ B20 เพียงวินาที โปรดใช้ฟังก์ชัน SECOND ดังนี้:

=SECOND(B20-A20)

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

start_time ต้องน้อยกว่า end_time มิฉะนั้น สูตรจะส่งกลับ #NUM! ค่าความผิดพลาด

1.26 คำนวณผลต่างระหว่างสองครั้งกับชั่วโมง นาที วินาที

หากคุณต้องการแสดงความแตกต่างระหว่างสองครั้งเป็น xx ชั่วโมง xx นาที xx วินาที โปรดใช้ฟังก์ชัน TEXT ดังที่แสดงด้านล่าง:

คลิก TEXT เพื่อทำความเข้าใจข้อโต้แย้งและการใช้ฟังก์ชันนี้

ในการคำนวณความแตกต่างระหว่างเวลาในเซลล์ A23 และ B23 ให้ใช้สูตรดังนี้:

=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

หมายเหตุ

สูตรนี้ยังคำนวณเฉพาะส่วนต่างของชั่วโมงที่ไม่เกิน 24 ชั่วโมง และ end_time ต้องมากกว่า start_time ไม่เช่นนั้น จะส่งกลับ #VALUE! ค่าความผิดพลาด

1.27 คำนวณความแตกต่างระหว่างสองวันเวลา

หากมีรูปแบบ mm/dd/yyyy hh:mm:ss สองครั้ง หากต้องการคำนวณความแตกต่างระหว่างรูปแบบดังกล่าว คุณสามารถใช้สูตรใดสูตรหนึ่งด้านล่างได้ตามต้องการ

รับความแตกต่างของเวลาระหว่างสองวันที่และเวลาส่งคืนผลลัพธ์ในรูปแบบ hh:mm:ss

ใช้วันที่สองครั้งในเซลล์ A2 และ B2 เป็นตัวอย่าง โปรดใช้สูตรดังนี้:

=B2-A2

ข่าวประชา เข้าสู่ คีย์ ส่งคืนผลลัพธ์ในรูปแบบ datetime จากนั้นจัดรูปแบบผลลัพธ์นี้เป็น [h]: mm: ss ในหมวดหมู่ที่กำหนดเองภายใต้ จำนวน แท็บใน จัดรูปแบบเซลล์ โต้ตอบ
 

รับความแตกต่างระหว่างสองวันและเวลาส่งคืน ชั่วโมง นาที วินาที

ใช้วันที่สองครั้งในเซลล์ A5 และ B5 เป็นตัวอย่าง โปรดใช้สูตรดังนี้:

=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

หมายเหตุ: ในทั้งสองสูตร end_datetime ต้องมากกว่า start_datetime ไม่เช่นนั้น สูตรจะคืนค่าความผิดพลาด

1.28 คำนวณความแตกต่างของเวลาด้วยมิลลิวินาที

ประการแรก คุณจำเป็นต้องรู้วิธีจัดรูปแบบเซลล์เพื่อแสดงมิลลิวินาที:

เลือกเซลล์ที่คุณต้องการแสดงเป็นมิลลิวินาทีและด้านขวาเพื่อเลือก จัดรูปแบบเซลล์ เพื่อเปิดใช้งาน จัดรูปแบบเซลล์ ใหเลือก ประเพณี ใน หมวดหมู่ รายการภายใต้แท็บ Number และพิมพ์ this hh: mm: ss.000 ลงในกล่องข้อความ

ใช้สูตร:

ที่นี่เพื่อคำนวณความแตกต่างระหว่างสองครั้งในเซลล์ A8 และ B8 ให้ใช้สูตรดังนี้:

=ABS(B8-A8)

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

1.29 คำนวณชั่วโมงทำงานระหว่างสองวันไม่รวมวันหยุดสุดสัปดาห์

บางครั้ง คุณอาจต้องนับชั่วโมงทำงานระหว่างวันที่สองวัน ไม่รวมวันหยุดสุดสัปดาห์ (วันเสาร์และวันอาทิตย์)

เวลาทำงานจะกำหนดไว้ที่ 8 ชั่วโมงทุกวัน และหากต้องการคำนวณชั่วโมงทำงานระหว่างวันที่สองวันที่ระบุไว้ในเซลล์ A16 และ B16 โปรดใช้สูตรดังนี้:

=NETWORKDAYS(A16,B16) * 8

ข่าวประชา เข้าสู่ คีย์แล้วจัดรูปแบบผลลัพธ์เป็นแบบทั่วไปหรือตัวเลข

สำหรับตัวอย่างเพิ่มเติมเกี่ยวกับการคำนวณชั่วโมงทำงานระหว่างสองวัน โปรดไปที่ รับชั่วโมงการทำงานระหว่างสองวันใน Excel

---

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

1.31 คำนวณความแตกต่างระหว่างสองวันเวลาโดย Data & Time Helper

ในการคำนวณความแตกต่างระหว่างเวลาสองวันใน Excel เพียงแค่ ตัวช่วยวันที่และเวลา ก็เพียงพอแล้ว

1. เลือกเซลล์ที่คุณวางผลการคำนวณ แล้วคลิก Kutools > ตัวช่วยสูตร > ตัวช่วยวันที่และเวลา.

2. ใน popping ตัวช่วยวันที่และเวลา โต้ตอบตามการตั้งค่าด้านล่าง:

1. ตรวจสอบ ความแตกต่าง ตัวเลือก;
2. เลือกวันที่เวลาเริ่มต้นและวันที่สิ้นสุดใน การป้อนอาร์กิวเมนต์ คุณยังสามารถป้อนวันที่และเวลาลงในช่องป้อนข้อมูลด้วยตนเองได้โดยตรง หรือคลิกไอคอนปฏิทินเพื่อเลือกวันที่
3. เลือกประเภทผลลัพธ์จากรายการดรอปดาวน์
4. ดูตัวอย่างผลลัพธ์ใน ผล มาตรา.

3 คลิก Ok. ผลลัพธ์จากการคำนวณจะถูกส่งออก และลากจุดจับป้อนอัตโนมัติเหนือเซลล์ที่คุณต้องการคำนวณด้วย

เคล็ดลับ:

หากคุณต้องการทราบความแตกต่างระหว่างสองวันที่และเวลาแสดงผลเป็นวัน ชั่วโมง และนาทีด้วย Kutools for Excel โปรดทำดังนี้:

เลือกเซลล์ที่คุณต้องการวางผลลัพธ์ แล้วคลิก Kutools > ตัวช่วยสูตร > วันเวลา > นับวัน ชั่วโมง และนาทีระหว่างสองวัน.

จากนั้นใน ตัวช่วยสูตร กล่องโต้ตอบ ระบุวันที่เริ่มต้นและวันที่สิ้นสุด จากนั้นคลิก Ok.

และผลต่างจะแสดงเป็นวัน ชั่วโมง และนาที

คลิก ตัวช่วยวันที่และเวลา เพื่อทราบการใช้งานคุณลักษณะนี้เพิ่มเติม

คลิก Kutools สำหรับ Excel เพื่อทราบคุณลักษณะทั้งหมดของ Add-in นี้

คลิก ดาวน์โหลดฟรี เพื่อรับ Kutools for Excel ทดลองใช้ฟรี 30 วัน

1.32 คำนวณความแตกต่างของวันหยุดสุดสัปดาห์/วันทำงาน/เฉพาะระหว่างวันสองวันโดย Formula Helper

ถ้าคุณต้องการนับวันหยุดสุดสัปดาห์วันทำงานหรือวันทำงานเฉพาะระหว่างสองวันอย่างรวดเร็ว Kutools for Excel's ตัวช่วยสูตร กลุ่มสามารถช่วยคุณได้

1. เลือกเซลล์ที่จะวางผลการคำนวณ คลิก Kutools > สถิติ > จำนวนวันที่ไม่ทำงานระหว่างวันที่สองวัน/จำนวนวันทำการระหว่างวันที่สองวัน/นับจำนวนวันที่ระบุในสัปดาห์.

2. ในการโผล่ออกมา ตัวช่วยสูตร กล่องโต้ตอบ ให้ระบุวันที่เริ่มต้นและวันที่สิ้นสุด หากคุณสมัคร นับจำนวนวันที่ระบุในสัปดาห์คุณต้องระบุวันทำงานด้วย

ในการนับวันทำงานที่ระบุ คุณสามารถอ้างอิงบันทึกเพื่อใช้ 1-7 เพื่อระบุวันอาทิตย์-วันเสาร์

3 คลิก Okแล้วลากจุดจับป้อนอัตโนมัติไปบนเซลล์ที่ต้องการนับจำนวนวันหยุดสุดสัปดาห์/วันทำงาน/วันทำงานที่ระบุ หากต้องการ

คลิก Kutools สำหรับ Excel เพื่อทราบคุณลักษณะทั้งหมดของ Add-in นี้

คลิก ดาวน์โหลดฟรี เพื่อรับ Kutools for Excel ทดลองใช้ฟรี 30 วัน

---

2. เพิ่มหรือลบวันที่และเวลา

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

2.11 เพิ่มหรือลบวันในวันที่

หากต้องการเพิ่มหรือลบจำนวนวันที่เจาะจงในวันที่หนึ่งๆ ต่อไปนี้เป็นวิธีที่แตกต่างกันสองวิธี

สมมติว่าจะเพิ่มวันที่ 21 ในเซลล์ A2 โปรดเลือกวิธีใดวิธีหนึ่งด้านล่างเพื่อแก้ปัญหา

วิธีที่ 1 วัน+วัน

เลือกเซลล์และพิมพ์สูตร:

=A+21

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

หากคุณต้องการลบ 21 วัน ให้เปลี่ยนเครื่องหมายบวก (+) เป็นเครื่องหมายลบ (-)

วิธีที่ 2 วางแบบพิเศษ

1. พิมพ์จำนวนวันที่คุณต้องการเพิ่มในเซลล์โดยสมมติในเซลล์ C2 แล้วกด Ctrl + C เพื่อคัดลอก

2. จากนั้นเลือกวันที่ที่คุณต้องการเพิ่ม 21 วัน คลิกขวาเพื่อแสดงเมนูบริบท แล้วเลือก แปะพิเศษ....

3 ใน วางแบบพิเศษ โต้ตอบตรวจสอบ เพิ่ม ตัวเลือก (หากต้องการลบวัน ให้เลือก ลบออก ตัวเลือก). คลิก OK.

4. ตอนนี้วันที่เดิมเปลี่ยนเป็นตัวเลข 5 หลัก ให้จัดรูปแบบเป็นวันที่

2.12 เพิ่มหรือลบเดือนเป็นวันที่

ในการเพิ่มหรือลบเดือนเป็นวันที่ คุณสามารถใช้ฟังก์ชัน EDATE ได้

คลิก แก้ไข เพื่อศึกษาข้อโต้แย้งและการใช้งาน

สมมติว่าจะเพิ่มวันที่ในเซลล์ A6 เป็นเวลา 2 เดือน ให้ใช้สูตรดังนี้:

=EDATE(A2,6)

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

หากคุณต้องการลบ 6 เดือนถึงวันที่ ให้เปลี่ยน 6 เป็น -6

2.13 บวกหรือลบปีถึงวันที่

ในการเพิ่มหรือลบ n ปีเป็นวันที่ คุณสามารถใช้สูตรที่รวมฟังก์ชัน DATE, YEAR, MONTH และ DAY ได้

สมมติว่าจะเพิ่ม 3 ปีถึงวันที่ในเซลล์ A2 ให้ใช้สูตรดังนี้:

=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

หากคุณต้องการลบ 3 ปีถึงวันที่ ให้เปลี่ยน 3 เป็น -3

2.14 เพิ่มหรือลบสัปดาห์เป็นวันที่

ในการบวกหรือลบสัปดาห์ถึงวันที่ สูตรทั่วไปคือ

สมมติว่าจะเพิ่มวันที่ในเซลล์ A4 2 สัปดาห์ ให้ใช้สูตรดังนี้:

=A2+4*7

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

หากคุณต้องการลบ 4 สัปดาห์นับจากวันที่ ให้เปลี่ยนเครื่องหมายบวก (+) เป็นเครื่องหมายลบ (-)

2.15 เพิ่มหรือลบวันทำงานรวมทั้งหรือไม่รวมวันหยุด

ในส่วนนี้ จะแนะนำวิธีการใช้ฟังก์ชัน WORKDAY เพื่อเพิ่มหรือลบวันทำการในวันที่กำหนด ยกเว้นวันหยุดหรือรวมวันหยุด

เยี่ยมชมร้านค้า วันทำงาน เพื่อทราบรายละเอียดเพิ่มเติมเกี่ยวกับข้อโต้แย้งและการใช้งาน

เพิ่มวันทำงานรวมทั้งวันหยุด

ในเซลล์ A2 คือวันที่ที่คุณใช้ ในเซลล์ B2 มีจำนวนวันที่คุณต้องการเพิ่ม โปรดใช้สูตรดังนี้:

=WORKDAY(A2,B2)

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

เพิ่มวันทำงานไม่รวมวันหยุด

ในเซลล์ A5 คือวันที่ที่คุณใช้ ในเซลล์ B5 มีจำนวนวันที่คุณต้องการเพิ่ม ในช่วง D5:D8 จะแสดงรายการวันหยุด โปรดใช้สูตรดังนี้:

=WORKDAY(A5,B5,D5:D8)

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

หมายเหตุ

ฟังก์ชัน WORKDAY จะใช้วันเสาร์และวันอาทิตย์เป็นวันหยุดสุดสัปดาห์ หากวันหยุดสุดสัปดาห์ของคุณเป็นวันเสาร์และวันอาทิตย์ คุณสามารถใช้ฟังก์ชัน WOKRDAY.INTL ซึ่งรองรับการระบุวันหยุดสุดสัปดาห์ได้

เยี่ยมชมร้านค้า workday.intl .

ถ้าคุณต้องการลบวันทำงานเป็นวันที่ ให้เปลี่ยนจำนวนวันเป็นค่าลบในสูตร

2.16 บวกหรือลบเฉพาะปี เดือน วัน จนถึงวันที่

ถ้าคุณต้องการเพิ่มปี วันเดือนเป็นวันที่ สูตรที่รวมฟังก์ชัน DATE, YEAR, MONTH และ DAYS ไว้ด้วยกัน

หากต้องการเพิ่ม 1 ปี 2 เดือน 30 วันเป็นวันที่ใน A11 โปรดใช้สูตรดังนี้:

=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

หากคุณต้องการลบ ให้เปลี่ยนเครื่องหมายบวกทั้งหมด (+) เป็นเครื่องหมายลบ (-)

---

2.21 เพิ่มหรือลบชั่วโมง/นาที/วินาทีเป็น datetime

มีสูตรบางอย่างสำหรับการเพิ่มหรือลบชั่วโมง นาที หรือวินาทีเป็นวันที่และเวลา

เพิ่มหรือลบชั่วโมงเป็น datetime

สมมติว่าจะเพิ่ม 3 ชั่วโมงเป็นวันที่เวลา (อาจเป็นเวลาก็ได้) ในเซลล์ A2 โปรดใช้สูตรดังนี้:

=A2+3/24

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

เพิ่มหรือลบชั่วโมงเป็น datetime

สมมติว่าจะเพิ่ม 15 นาทีเป็นวันที่เวลา (อาจเป็นเวลาก็ได้) ในเซลล์ A5 โปรดใช้สูตรดังนี้:

=A2+15/1440

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

เพิ่มหรือลบชั่วโมงเป็น datetime

สมมติว่าจะเพิ่ม 20 วินาทีใน datetime (อาจเป็นเวลา) ในเซลล์ A8 โปรดใช้สูตรดังนี้:

=A2+20/86400

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

2.22 ผลรวมครั้งใน 24 ชั่วโมง

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

ที่จริงแล้ว คุณเพียงแค่ต้องจัดรูปแบบผลลัพธ์เป็น [hh]:นาที:ss.

คลิกขวาที่เซลล์ผลลัพธ์ เลือก จัดรูปแบบเซลล์ ในเมนูบริบทและในการป๊อปอัป จัดรูปแบบเซลล์ โต้ตอบเลือก ประเพณี จาก blist และพิมพ์ [hh]:นาที:ss ในกล่องข้อความในส่วนขวา ให้คลิก OK.
  

ผลรวมจะแสดงอย่างถูกต้อง

2.23 เพิ่มชั่วโมงทำงานในวันที่ไม่รวมวันหยุดสุดสัปดาห์และวันหยุด

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

ในตาราง Excel A11 ประกอบด้วยวันที่เริ่มต้น และ B11 ประกอบด้วยชั่วโมงทำงาน ในเซลล์ E11 และ E13 คือเวลาเริ่มต้นและสิ้นสุดการทำงาน และเซลล์ E15 มีวันหยุดที่จะถูกยกเว้น

โปรดใช้สูตรดังนี้:

=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

---

หากคุณมี Kutools for Excel ติดตั้งเครื่องมือเดียวเท่านั้น - วันที่ & เวลา Helper สามารถแก้ปัญหาการคำนวณส่วนใหญ่เกี่ยวกับการเพิ่มและการลบวันที่และเวลาได้

1. คลิกเซลล์ที่คุณต้องการแสดงผลลัพธ์ และใช้เครื่องมือนี้โดยคลิก Kutools > ตัวช่วยสูตร > ตัวช่วยวันที่ & เวลา

2 ใน ตัวช่วยวันที่และเวลา โต้ตอบตรวจสอบ เพิ่ม option หรือ ลบออก ตามที่คุณต้องการ จากนั้นเลือกเซลล์หรือพิมพ์วันที่และเวลาที่คุณต้องการใช้โดยตรง การป้อนอาร์กิวเมนต์ จากนั้นระบุปี เดือน สัปดาห์ วัน ชั่วโมง นาที และวินาทีที่คุณต้องการเพิ่มหรือลบ จากนั้นคลิก Ok. ดูภาพหน้าจอ:

คุณสามารถดูตัวอย่างผลการคำนวณใน ผล มาตรา.

ตอนนี้ผลลัพธ์ออกมาแล้ว ให้ลากตัวจัดการอัตโนมัติไปไว้เหนือเซลล์อื่นๆ เพื่อให้ได้ผลลัพธ์

คลิก ตัวช่วยวันที่และเวลา เพื่อทราบการใช้งานคุณลักษณะนี้เพิ่มเติม

คลิก Kutools สำหรับ Excel เพื่อทราบคุณลักษณะทั้งหมดของ Add-in นี้

คลิก ดาวน์โหลดค่าธรรมเนียม เพื่อรับ Kutools for Excel ทดลองใช้ฟรี 30 วัน

---

2.41 ตรวจสอบหรือไฮไลท์ว่าวันหมดอายุหรือไม่

หากมีรายการวันที่หมดอายุของผลิตภัณฑ์ คุณอาจต้องการตรวจสอบและเน้นวันที่ที่หมดอายุตามวันนี้ตามที่แสดงภาพหน้าจอด้านล่าง

ที่จริงแล้ว การจัดรูปแบบตามเงื่อนไข สามารถจัดการงานนี้ได้อย่างรวดเร็ว

1. เลือกวันที่ที่คุณต้องการตรวจสอบ จากนั้นคลิก หน้าแรก > การจัดรูปแบบตามเงื่อนไข > กฎใหม่.

2 ใน กฎการจัดรูปแบบใหม่ ใหเลือก ใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ ใน เลือกประเภทกฎ และประเภท =B2 ลงในช่องป้อนข้อมูล (B2 คือวันแรกที่คุณต้องการตรวจสอบ) แล้วคลิก รูปแบบ ที่จะปรากฏขึ้น จัดรูปแบบเซลล์ กล่องโต้ตอบ จากนั้นเลือกการจัดรูปแบบอื่นเพื่อให้โดดเด่นกว่าวันที่หมดอายุตามที่คุณต้องการ คลิก OK > OK.
  

2.42 คืนสิ้นเดือนปัจจุบัน/วันแรกของเดือนถัดไป/a>

วันหมดอายุของผลิตภัณฑ์บางรายการอยู่ ณ สิ้นเดือนที่ผลิตหรือวันแรกของเดือนถัดไปของการผลิต หากต้องการระบุวันที่หมดอายุตามวันที่ผลิตอย่างรวดเร็ว โปรดปฏิบัติตามส่วนนี้

รับสิ้นเดือนปัจจุบัน

นี่คือวันที่ผลิตในเซลล์ B13 โปรดใช้สูตรดังนี้:

=EOMONTH(B13,0)

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

รับวันที่ 1 ของเดือนหน้า

นี่คือวันที่ผลิตในเซลล์ B18 โปรดใช้สูตรดังนี้:

=EOMONTH(B18,0)+1

ข่าวประชา เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

---

3. คำนวณอายุ

ในส่วนนี้จะแสดงรายการวิธีการแก้ปัญหาการคำนวณอายุตามวันที่ที่กำหนดหรือหมายเลขชุดข้อมูล

---

3.11 คำนวณอายุตามวันเกิดที่กำหนด

รับอายุเป็นเลขทศนิยมตามวันเกิด

คลิก ปีฟรัค สำหรับรายละเอียดเกี่ยวกับข้อโต้แย้งและการใช้งาน

ตัวอย่างเช่น หากต้องการทราบอายุตามรายการวันเกิดในคอลัมน์ B2:B9 โปรดใช้สูตรดังนี้

=YEARFRAC(B2,TODAY())

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

เคล็ดลับ:

1) คุณสามารถระบุตำแหน่งทศนิยมตามที่คุณต้องการใน จัดรูปแบบเซลล์ โต้ตอบ

2) หากคุณต้องการคำนวณอายุในวันที่ระบุโดยอ้างอิงจากวันเกิดที่กำหนด ให้เปลี่ยน TODAY() เป็นวันที่ที่ระบุซึ่งอยู่ในเครื่องหมายอัญประกาศคู่ เช่น =YEARFRAC(B2,"1/1/2021")

3) ถ้าคุณต้องการหาอายุของปีหน้าตามวันเกิด ให้เพิ่ม 1 ในสูตร เช่น =YEARFRAC(B2,TODAY())+1

รับอายุเป็นจำนวนเต็มตามวันเกิด

คลิก วันที่ สำหรับรายละเอียดเกี่ยวกับข้อโต้แย้งและการใช้งาน

จากตัวอย่างข้างต้น เพื่อให้ได้อายุตามวันเกิดในรายการใน B2:B9 โปรดใช้สูตรดังนี้:

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

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

เคล็ดลับ:

1) หากคุณต้องการคำนวณอายุในวันที่ระบุโดยอ้างอิงจากวันเกิดที่กำหนด ให้เปลี่ยน TODAY() เป็นวันที่ที่ระบุซึ่งอยู่ในเครื่องหมายอัญประกาศคู่ เช่น =DATEDIF(B2,"1/1/2021","y") .

2) หากคุณต้องการรับอายุของปีหน้าตามวันเกิด เพียงเพิ่ม 1 ในสูตร เช่น =DATEDIF(B2,TODAY(),"y")+1

3.12 คำนวณอายุในรูปแบบปี เดือน และวันตามวันเกิดที่กำหนด

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

เพื่อให้ได้อายุเป็นปี เดือน และวันตามวันเกิดในเซลล์ B12 โปรดใช้สูตรดังนี้:

=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"

ข่าวประชา เข้าสู่ เพื่อรับอายุ จากนั้นลากขอบจับป้อนอัตโนมัติลงไปที่เซลล์อื่น

เคล็ดลับ:

หากคุณต้องการคำนวณอายุในวันที่ระบุโดยอ้างอิงจากวันเกิดที่กำหนด ให้เปลี่ยน TODAY() เป็นวันที่ที่ระบุซึ่งล้อมรอบด้วยอัญประกาศคู่ เช่น = =DATEDIF(B12,"1/1/2021","Y")& " ปี, "&DATEDIF(B12,"1/1/2021","YM")&" เดือน, "&DATEDIF(B12,"1/1/2021","MD")&" วัน"

3.13 คำนวณอายุตามวันเกิดก่อน 1/1/1900

ใน Excel วันที่ก่อน 1/1/1900 ไม่สามารถป้อนเป็น datetime หรือคำนวณได้อย่างถูกต้อง แต่ถ้าคุณต้องการคำนวณอายุของบุคคลที่มีชื่อเสียงตามวันเกิดที่ระบุ (ก่อน 1/11900) และวันที่เสียชีวิต มีเพียงรหัส VBA เท่านั้นที่สามารถช่วยคุณได้

1 กด อื่น ๆ + F11 คีย์เพื่อเปิดใช้งาน Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่างแล้วคลิก สิ่งที่ใส่เข้าไป และเลือก โมดูล เพื่อสร้างโมดูลใหม่

2. จากนั้นคัดลอกและวางโค้ดด้านล่างลงในโมดูลใหม่

VBA: คำนวณอายุก่อน 1/1/1900

Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
    Dim xSMonth As Integer
    Dim xSDay As Integer
    Dim xSYear As Integer
    Dim xEMonth As Integer
    Dim xEDay As Integer
    Dim xEYear As Integer
    Dim xAge As Integer
    If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
        AgeFunc = "Invalid Date"
        Exit Function
    End If
    If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
        AgeFunc = "Invalid Date"
        Exit Function
    End If
    xAge = xEYear - xSYear
    If xSMonth > xEMonth Then
        xAge = xAge - 1
    ElseIf xSMonth = xEMonth Then
        If xSDay > xEDay Then xAge = xAge - 1
    End If
    If xAge < 0 Then
        AgeFunc = "Invalid Date"
    Else
        AgeFunc = xAge
    End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
    Dim I As Long
    Dim K As Long
    Y = 0
    M = 0
    D = 0
    GetDate = True
    On Error Resume Next
    I = InStr(1, DateStr, "/")
    M = CLng(Left(DateStr, I - 1))
    D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
    Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
    If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
        GetDate = False
    End If
End Function

3. บันทึกรหัสและกลับไปที่แผ่นงานและเลือกเซลล์เพื่อวางอายุที่คำนวณได้ พิมพ์ =AgeFunc(วันเกิด, วันตาย) ในกรณีนี้ =อายุฟัน(B22,C22)ให้กดปุ่ม Enter เพื่อรับอายุ และใช้ที่จับเติมอัตโนมัติเพื่อใช้สูตรนี้กับเซลล์อื่นๆ หากจำเป็น

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

1. เลือกเซลล์ที่คุณต้องการวางอายุที่คำนวณแล้วคลิก Kutools > ตัวช่วยสูตร > ตัวช่วยวันที่และเวลา.

2 ใน ตัวช่วยวันที่และเวลา โต้ตอบ

• 1) ตรวจสอบ อายุ ตัวเลือก;
• 2) เลือกเซลล์วันเกิดหรือป้อนวันเกิดโดยตรงหรือคลิกไอคอนปฏิทินเพื่อเลือกวันเกิด
• 3) เลือก ในวันนี้ ตัวเลือกถ้าคุณต้องการคำนวณอายุปัจจุบันเลือก วันที่ระบุ ตัวเลือกและป้อนวันที่หากคุณต้องการคำนวณอายุในอดีตหรืออนาคต
• 4) ระบุประเภทผลลัพธ์จากรายการแบบหล่นลง
• 5) ดูตัวอย่างผลลัพธ์ผลลัพธ์ คลิก Ok.

คลิก ตัวช่วยวันที่และเวลา เพื่อทราบการใช้งานคุณลักษณะนี้เพิ่มเติม

คลิก Kutools สำหรับ Excel เพื่อทราบคุณลักษณะทั้งหมดของ Add-in นี้

คลิก ดาวน์โหลดฟรี เพื่อรับ Kutools for Excel ทดลองใช้ฟรี 30 วัน

---

3.31 รับวันเกิดจากเลขบัตรประชาชน

หากมีรายการหมายเลขประจำตัวประชาชนที่ใช้ตัวเลข 6 หลักแรกในการบันทึกวันเกิด เช่น 920315330 แสดงว่าวันเกิดคือ 03/15/1992 คุณจะเอาวันเกิดไปไว้อีกคอลัมน์หนึ่งอย่างรวดเร็วได้อย่างไร

ตอนนี้ มาดูรายการหมายเลข ID ที่เริ่มต้นในเซลล์ C2 เป็นตัวอย่าง และใช้สูตรดังนี้:

=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)

ข่าวประชา เข้าสู่ กุญแจ. จากนั้นลากที่จับสำหรับป้อนอัตโนมัติลงเพื่อรับผลลัพธ์อื่นๆ

หมายเหตุ

ในสูตร คุณสามารถเปลี่ยนการอ้างอิงตามความต้องการของคุณได้ ตัวอย่างเช่น หากหมายเลขประจำตัวแสดงเป็น 13219920420392 วันเกิดคือ 04/20/1992 คุณสามารถเปลี่ยนสูตรเป็น =MID(C2,8,2)&"/"&MID(C2,10,2)&"/ "&MID(C2,4,4) เพื่อให้ได้ผลลัพธ์ที่ถูกต้อง

3.32 คำนวณอายุจากเลขประจำตัวประชาชน

หากมีรายการหมายเลขประจำตัวประชาชนที่ใช้ตัวเลข 6 หลักแรกในการบันทึกวันเกิด เช่น 920315330 แสดงว่าวันเกิดคือ 03/15/1992 คุณจะคำนวณอายุตามหมายเลขประจำตัวแต่ละหมายเลขใน Excel ได้อย่างรวดเร็วได้อย่างไร

ตอนนี้ มาดูรายการหมายเลข ID ที่เริ่มต้นในเซลล์ C2 เป็นตัวอย่าง และใช้สูตรดังนี้:

=DATEDIF(DATE(IF(LEFT(C2,2)>TEXT(TODAY(),"YY"),"19"&LEFT(C2,2),"20"&LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),"y")

ข่าวประชา เข้าสู่ กุญแจ. จากนั้นลากที่จับสำหรับป้อนอัตโนมัติลงเพื่อรับผลลัพธ์อื่นๆ

หมายเหตุ

ในสูตรนี้ ถ้าปีน้อยกว่าปีปัจจุบัน ให้ถือว่าปีเริ่มด้วย 20 เช่น 200203943 ให้ถือเป็นปี 2020 หากปีนั้นมากกว่าปีปัจจุบัน ให้ถือว่าปีเริ่มต้นด้วย 19 เช่น 920420392 จะถือเป็นปี 1992

---

บทแนะนำ Excel เพิ่มเติม: