สร้างวันที่ด้วย DATE และ EDATE

ในบทเรียนที่แล้ว คุณแยกวันที่ออกเป็นส่วน ๆ ตอนนี้คุณจะประกอบวันที่ขึ้นมาใหม่ บางครั้งคุณมีปี เดือน และวันเป็นตัวเลขแยกกัน และต้องรวมตัวเลขเหล่านั้นให้เป็นวันที่จริงหนึ่งค่า

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

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

ฟังก์ชัน DATE รับอาร์กิวเมนต์สามตัวตามลำดับที่ตายตัว ได้แก่ ปี เดือน วัน

ดังนั้น =DATE(2026, 6, 18) จะสร้างค่าของวันที่จริงเป็น 18 มิถุนายน 2026 จากนั้นสเปรดชีตจะแสดงค่านี้ตามรูปแบบวันที่ของคุณ

เรื่องนี้สำคัญเมื่อข้อมูลมาเป็นส่วน ๆ หากปีอยู่ใน A2 เดือนอยู่ใน B2 และวันอยู่ใน C2 คุณก็สามารถประกอบข้อมูลเหล่านี้ให้เป็นวันที่หนึ่งค่าได้

คุณค่าที่แท้จริงของ DATE จะเห็นได้ชัดเมื่อส่วนประกอบต่าง ๆ อยู่ในเซลล์คนละเซลล์ สมมติว่า A2 มีปี B2 มีเดือน และ C2 มีวัน

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

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

ตัวอย่างเช่น =DATE(2026, 1, 0) จะส่งคืนค่า 31 ธันวาคม 2025 และ =DATE(2026, 13, 1) จะส่งคืนค่า 1 มกราคม 2027

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

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

หาก A2 มีวันที่ใด ๆ สูตร =DATE(YEAR(A2), MONTH(A2)+1, 0) จะส่งคืนวันสุดท้ายของเดือนใน A2 โดยจัดการเดือนได้อย่างถูกต้องไม่ว่าจะมี 28, 29, 30 หรือ 31 วัน

นอกจากนี้ยังมีฟังก์ชันเฉพาะชื่อ EOMONTH ที่ทำสิ่งเดียวกันได้โดยตรงกว่า ซึ่งเราจะได้เรียนรู้ต่อไป

ฟังก์ชัน EDATE จะเลื่อนวันที่ตามจำนวนเดือนเต็ม ๆ โดยรับอาร์กิวเมนต์สองตัว ได้แก่ วันที่เริ่มต้นและจำนวนเดือนที่จะเลื่อน

จำนวนบวกจะเลื่อนไปข้างหน้า ส่วนจำนวนลบจะเลื่อนย้อนกลับ ดังนั้น =EDATE(A2, 3) จะส่งคืนวันที่หลังจาก A2 สามเดือน และ =EDATE(A2, -1) จะส่งคืนวันที่ก่อนหน้าไปหนึ่งเดือน

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

คุณอาจคิดว่าการบวก 30 จะเลื่อนวันที่ไปหนึ่งเดือน แต่แต่ละเดือนมีจำนวนวันไม่เท่ากัน การบวก 30 วันให้กับวันที่ 31 มกราคมจะไปตกในช่วงต้นเดือนมีนาคม ไม่ใช่เดือนกุมภาพันธ์

EDATE เข้าใจเดือนตามปฏิทินจริง จาก31 มกราคม สูตร =EDATE(A2, 1) จะส่งคืนค่า28 กุมภาพันธ์ หรือวันที่ 29 ในปีอธิกสุรทิน เพราะเดือนกุมภาพันธ์ไม่มีวันที่ 31

นี่คือเหตุผลที่ EDATE เป็นเครื่องมือที่ถูกต้องสำหรับกำหนดการรายเดือน

ฟังก์ชันคู่กับ EDATE คือ EOMONTH ซึ่งจะส่งคืนวันสิ้นเดือนที่อยู่ห่างออกไปตามจำนวนเดือนที่กำหนด

=EOMONTH(A2, 0) จะให้วันสุดท้ายของเดือนเดียวกับ A2 ส่วน =EOMONTH(A2, 1) จะให้วันสุดท้ายของเดือนถัดไป

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

ลูกค้าสมัครสมาชิกในวันที่ระบุใน A2 โดยเลือกแผน 12 เดือน คุณต้องการทราบวันที่ต่ออายุ

ใช้ EDATE เพื่อเลื่อนไปข้างหน้าหนึ่งปีพอดี โดยคำนึงถึงเดือนตามปฏิทิน หากลูกค้าสมัครในวันที่ 29 กุมภาพันธ์ของปีอธิกสุรทิน EDATE จะเลื่อนไปลงที่วันที่ 28 กุมภาพันธ์ของปีถัดไปได้อย่างเหมาะสม

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

ผสาน EOMONTH เข้ากับการบวกแบบง่าย ๆ เพื่อจัดการความต้องการทั่วไปอีกอย่าง นั่นคือการหาวันแรกของเดือนถัดไป

EOMONTH ให้วันสุดท้ายของเดือนปัจจุบัน ดังนั้นเมื่อบวก 1 วัน ก็จะได้วันแรกของเดือนถัดไป

ดังนั้น =EOMONTH(A2, 0)+1 จะส่งคืนวันแรกของเดือนถัดไปเสมอ ไม่ว่าเดือนปัจจุบันจะมีกี่วันก็ตาม เครื่องมือเช่นนี้ช่วยให้สูตรสำหรับการจัดตารางเวลามีความแม่นยำและเชื่อถือได้

ผสาน EDATE เข้ากับวันที่เริ่มต้นเพื่อสร้างกำหนดการทั้งชุด ใส่วันที่เริ่มต้นใน A2 จากนั้นในเซลล์ถัดไปให้เพิ่มหนึ่งเดือน แล้วเพิ่มอีกหนึ่งเดือนในเซลล์ถัดไป และทำต่อไปเรื่อย ๆ

ตัวอย่างเช่น =EDATE($A$2, 1), =EDATE($A$2, 2), =EDATE($A$2, 3) จะแสดงวันเดียวกันตามปฏิทินในเดือนที่ต่อเนื่องกัน เครื่องหมาย $ จะล็อกเซลล์เริ่มต้นไว้ เพื่อให้คุณเติมสูตรลงด้านล่างได้

วิธีนี้ช่วยสร้างวันที่เรียกเก็บเงินหรือวันที่ชำระเงินรายเดือนที่เป็นระเบียบ โดยใช้จุดอ้างอิงเพียงจุดเดียว

ทดสอบทักษะการสร้างวันที่ของคุณ

คุณได้เรียนรู้วิธีประกอบและเลื่อนวันที่แล้ว:

• DATE(year, month, day) สร้างวันที่จริงจากตัวเลขสามตัว และยังปรับค่าที่ล้นช่วงให้ถูกต้องได้
• DATE(YEAR(d), MONTH(d)+1, 0) ค้นหาวันสุดท้ายของเดือนโดยใช้เทคนิคกำหนดวันเป็นศูนย์
• EDATE(date, months) เลื่อนวันที่ตามเดือนเต็ม ๆ ในปฏิทิน และจัดการจำนวนวันที่แตกต่างกันของแต่ละเดือนได้อย่างถูกต้อง
• EOMONTH(date, months) ส่งคืนวันที่สิ้นเดือน และเมื่อบวก 1 จะได้วันแรกของเดือนถัดไป

ถัดไป คุณจะได้วัดช่วงห่างระหว่างวันที่สองค่า