นับจำนวนวันระหว่างวันที่

งานเกี่ยวกับวันที่ที่พบบ่อยที่สุดอย่างหนึ่งคือการหาว่ามีระยะเวลาห่างระหว่างวันที่สองวันเท่าใด เช่น จำนวนวันก่อนถึงกำหนดส่ง อายุเป็นปี หรือระยะเวลาของโครงการ

เนื่องจากสเปรดชีตจัดเก็บวันที่เป็นตัวเลข คำตอบที่ง่ายที่สุดคือการลบโดยตรง สำหรับความต้องการที่ซับซ้อนขึ้น เช่น การนับเดือนเต็มหรือปีเต็ม ฟังก์ชัน DATEDIF จะเข้ามาช่วย

ในบทเรียนนี้ คุณจะได้เรียนรู้ทั้งวิธีลบแบบรวดเร็วและวิธีใช้ DATEDIF เพื่อคำนวณอย่างแม่นยำ

หากต้องการนับจำนวนวันระหว่างวันที่ก่อนหน้าใน A2 กับวันที่ภายหลังใน B2 ให้ลบกันโดยตรง

ผลลัพธ์คือจำนวนวันที่อยู่ระหว่างวันที่เหล่านั้น หาก A2 เป็นวันที่ 1 มิถุนายน และ B2 เป็นวันที่ 18 มิถุนายน =B2-A2 จะให้ผลลัพธ์เป็น 17

ใส่วันที่ภายหลังไว้ข้างหน้าเพื่อให้ผลลัพธ์เป็นค่าบวก หากลบสลับกันจะได้ค่าลบ ซึ่งหมายความเพียงว่าลำดับวันที่กลับกัน

สิ่งที่มักทำให้ประหลาดใจคือ หลังจากลบวันที่แล้ว บางครั้งเซลล์ผลลัพธ์แสดงเป็น วันที่ แทนที่จะเป็นตัวเลข เช่น 1/17/1900

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

เมื่อจัดรูปแบบเป็นตัวเลขแล้ว คุณจะเห็นจำนวนวันที่ชัดเจนตามที่คาดไว้

การลบโดยตรงจะนับ ช่วงห่าง ระหว่างวัน ไม่ใช่จำนวนวันจริง จากวันที่ 1 มิถุนายนถึงวันที่ 18 มิถุนายน การลบจะได้ 17 แต่ถ้านับรวมทั้งสองวัน จะมีทั้งหมด 18 วันตามปฏิทิน

หากสถานการณ์ของคุณต้องนับรวมทั้งวันเริ่มต้นและวันสิ้นสุด ให้บวก 1 กับผลลัพธ์

ดังนั้น =B2-A2+1 จะให้จำนวนวันที่นับรวมปลายทาง ให้พิจารณาว่ารายงานของคุณต้องใช้แบบใด

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

รหัสหน่วยจะกำหนดว่าจะนับสิ่งใด:

• "D" นับวัน
• "M" นับเดือนที่ครบถ้วน
• "Y" นับปีที่ครบถ้วน

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

ใช้หน่วย "M" เพื่อนับ เดือนที่ครบถ้วน ระหว่างวันที่สองวัน โดยจะไม่นับเดือนที่ยังไม่ครบ

หาก A2 เป็นวันที่ 15 มกราคม และ B2 เป็นวันที่ 10 เมษายน =DATEDIF(A2, B2, "M") จะให้ผลลัพธ์เป็น 2 เพราะมีเพียงสองเดือนเต็มที่ผ่านไปแล้ว (เดือนที่สามยังไม่ครบเมื่อถึงวันที่ 10)

วิธีนี้ตรงกับการวัดระยะเวลาการสมัครสมาชิกหรือระยะเวลาสัญญาที่ใช้กันโดยทั่วไป

หน่วย "Y" จะให้ผลเป็น ปีที่ครบถ้วน ซึ่งเป็นวิธีที่ถูกต้องในการคำนวณอายุ

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

วิธีนี้ช่วยหลีกเลี่ยงข้อผิดพลาดจากการปัดเศษที่เกิดขึ้นเมื่อหารจำนวนวันที่ต่างกันด้วย 365

DATEDIF มีรหัสหน่วยเพิ่มเติมสำหรับแยกช่วงเวลาออกเป็นส่วนย่อย:

• "YM" = จำนวนเดือนที่เหลือหลังจากนับปีเต็มแล้ว
• "MD" = จำนวนวันที่เหลือหลังจากนับเดือนเต็มแล้ว
• "YD" = จำนวนวันที่เหลือหลังจากนับปีเต็มแล้ว

รหัสเหล่านี้ช่วยให้คุณระบุระยะเวลาเป็น 2 ปี 3 เดือน 5 วัน ได้ด้วยการรวมการเรียกใช้ DATEDIF สามครั้ง

การลบโดยตรงจะนับทุกวันตามปฏิทิน รวมถึงวันหยุดสุดสัปดาห์ด้วย หากต้องการนับเฉพาะ วันทำการ ให้ใช้ NETWORKDAYS

ฟังก์ชันนี้นับวันธรรมดาระหว่างวันที่สองวัน และให้คุณระบุรายการวันหยุดที่ต้องข้ามได้ ดังนั้น =NETWORKDAYS(A2, B2) จะคืนค่าจำนวนวันตั้งแต่วันจันทร์ถึงวันศุกร์ และอาร์กิวเมนต์ที่สามซึ่งเป็นตัวเลือกจะชี้ไปยังช่วงวันหยุด

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

สมมติว่าวันครบกำหนดชำระใบแจ้งหนี้อยู่ใน A2 และคุณต้องการระบุว่าล่าช้ากว่าวันนี้กี่วัน แต่ให้แสดง 0 หากยังไม่เกินกำหนด

นำวันครบกำหนดไปลบออกจากวันนี้ แล้วครอบผลลัพธ์ด้วย MAX และ 0 เพื่อไม่ให้ใบแจ้งหนี้ที่ยังไม่ถึงกำหนดแสดงค่าติดลบ

สูตรเดียวนี้ให้จำนวนวันที่เกินกำหนดที่ชัดเจนและเหมาะสมสำหรับรายงานติดตามหนี้

เมื่อคุณมีจำนวนวันแล้ว คุณสามารถแปลงเป็นหน่วยอื่นได้ หากต้องการแสดงช่วงเวลาเป็น สัปดาห์เต็ม ให้หารจำนวนวันที่ต่างกันด้วย 7 แล้วปัดเศษลงด้วย INT

ดังนั้น =INT((B2-A2)/7) จะบอกว่าระหว่างวันที่สองวันนั้นมีสัปดาห์เต็มกี่สัปดาห์ ช่วงห่าง 17 วันจะกลายเป็น 2 สัปดาห์

แนวคิดเดียวกันนี้ใช้ได้กับทุกหน่วย เพียงหารจำนวนวันแล้วปัดเศษตามที่รายงานของคุณต้องการ

ทดสอบความรู้เกี่ยวกับการหาความแตกต่างระหว่างวันที่ของคุณ

คุณได้เรียนรู้วิธีวัดช่วงเวลาหลายวิธี:

• =B2-A2 ให้ช่วงห่างเป็นจำนวนวัน (ใส่วันที่ภายหลังไว้ข้างหน้า) และให้บวก 1 หากต้องการนับรวมปลายทาง
• เปลี่ยนรูปแบบของเซลล์ผลลัพธ์เป็น ตัวเลข หากเซลล์แสดงเป็นวันที่
• DATEDIF(start, end, "D"/"M"/"Y") นับจำนวนวัน เดือน หรือปีเต็ม เหมาะอย่างยิ่งสำหรับการคำนวณอายุและระยะเวลา
• NETWORKDAYS นับวันทำการและสามารถข้ามวันหยุดได้
• ครอบสูตรด้วย MAX(0, ...) เพื่อหลีกเลี่ยงจำนวนวันที่เกินกำหนดซึ่งเป็นค่าลบ

คุณเรียนจบหลักสูตรฟังก์ชันวันที่และเวลาแล้ว