สูตร excel นับวัน เดือน ปี

วิธีคำนวณหาจำนวนวันที่ห่างกัน ด้วยฟังก์ชัน DATEDIF ใน Google Sheet

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

บทความเกี่ยวกับ Google อื่นๆ

ในบทความนี้เราเลยจะมาแนะนำวิธีการคำนวณหาจำนวนวันที่ห่างกันอย่างง่าย ๆ ด้วยการใช้ฟังก์ชัน DATEIF ที่มีให้ใช้งานทั้งใน แอปพลิเคชันสเปรดชีตชื่อดังอย่าง Excel และ Google Sheet เลย จะมีวิธีการใช้ฟังก์ชันนี้อย่างไร ไปอ่านต่อกัน

ฟังก์ชัน DATEIF คืออะไร ?

ฟังก์ชัน DATEDIF เป็นฟังก์ชันหนึ่งของแอป Excel และ Google Sheet ที่ใช้คำนวณความแตกต่างของข้อมูลวันที่ ในตารางคำนวณเสปรดชีต โดยสามารถใช้หาผลลัพธ์ออกมาเป็นความแตกต่างของจำนวนปี, เดือน หรือวัน ได้อย่างง่ายดาย โดยฟังก์ชัน DATEDIF สามารถใช้งานได้โดยใส่สูตรดังนี้

=DATEDIF(ข้อมูล 1, ข้อมูล 2, "หน่วย")

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

วิธีใช้ฟังก์ชัน DATEIF ใน Excel และ Sheet

แม้จะเป็นแอปพลิเคชันคนละตัวจากผู้พัฒนาคนละค่าย Excel จาก Microsoft และ Sheet จาก Google แต่ทั้งคู่มีวิธีการใช้งานฟังก์ชันนี้เหมือนกันเป๊ะ สูตรก็หน้าตาเหมือนกันทุกประการ ดังนั้น ไม่ว่าจะใช้แอปพลิเคชันตัวไหน ก็สามารถนำไปปรับใช้งานได้เลย

อย่างไรก็ตาม ในบทความนี้จะสาธิตการใช้งานบน Sheet นะ โดยจะยกตัวอย่างเป็นการคำนวณจากวันแรกที่เปิดตัวเว็บไซต์ไทยแวร์

ก่อนอื่นเราต้องมีข้อมูลวันที่ค่าแรกเป็นตัวตั้งก่อน โดยใน Sheet ตามปกติแล้ว จะเรียงข้อมูลวันที่ในรูปแบบ เดือน/วัน/ปี ค.ศ. เว็บไทยแวร์เปิดตัวอย่างเป็นทางการเมื่อวันที่ 1 มกราคม พ.ศ. 2542 ข้อมูลก็จะอยู่ในรูปแบบ "1/1/1999"

เราก็นำข้อมูลไปใส่ไว้ใน Cell "A2" ส่วนวันที่ปัจจุบัน เพื่อให้ข้อมูลอัปเดตอัตโนมัติ โดยที่เราไม่จำเป็นต้องมาแก้ไขเอง ก็จะใช้ฟังก์ชันเข้ามาช่วย โดยให้ใส่สูตรว่า "=TODAY()"

ต่อมาเราก็จะเริ่มใส่สูตร DATEDIF เข้าไป อย่างที่เรากล่าวไว้ข้างต้นว่า สูตรจะอยู่ในรูปแบบดังนี้ "=DATEDIF(ข้อมูล 1, ข้อมูล 2, "หน่วย")" ลองแทนค่าก็จะได้ "ข้อมูล 1" = "A2" และ  "ข้อมูล 2" = "B2" ดังนั้นก็จะออกมาเป็น "=DATEDIF(A2, B2, "หน่วย")" ซึ่งค่าหน่วยก็พิจารณาจากตารางด้านบนว่าเราต้องการหาค่าแบบไหน แล้วก็นำ Y, M, D, MD, YM หรือ YD ไปแทนค่าในสูตรได้เลย เช่น หาจำนวนวัน ก็จะได้เป็นสูต

ผลลัพธ์ที่ได้ ก็จะออกมาดังภาพด้านล่างนี้ ใครที่ต้องคำนวณหาข้อมูลในลักษณะนี้บ่อย ๆ ก็ลองนำฟังก์ชัน DATEDIF ไปประยุกต์ใช้งานกันดูนะ

ที่มา : www.makeuseof.com , support.microsoft.com

เขียนโดย     Thaiware

แอดมินสายเปื่อย ชอบลองอะไรใหม่ไปเรื่อยๆ รักแมว และเสียงเพลงเป็นพิเศษ

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

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

• ฟังก์ชันลึกลับ?
• วิธีการใช้งานของ DATEDIF
  • โหมดการทำงานของ DATEDIF
• อาการแปลกๆ ของ DATEDIF
  • อาการนับจำนวนติดลบหากวันเริ่มอยู่ในช่วงปลายเดือน
  • อาการแปลกๆ อันอื่น
• แนวทางการปรับสูตร DATEDIF ให้ทำงานดั่งใจ
  • การ adjust เรื่อง md ที่มีติดลบ
• สรุปสูตรทั้งหมด
  • คำนวณปี
  • คำนวณเดือน
  • คำนวณวัน
  • ตัวอย่างการคำนวณ

ฟังก์ชันลึกลับ?

ที่ผมบอกว่ามันลึกลับเพราะว่ามันเป็นฟังก์ชันที่ไม่ขึ้นใน List ของฟังก์ชันใน Excel ด้วยซ้ำ

เวลาพิมพ์ =DATE เพื่อดูว่ามีฟังก์ชันอะไรบ้าง? ก็หาเจ้าตัวนี้ไม่เจอ!

แต่ถ้าพิมพ์จนครบแล้วเปิดวงเล็บ จะพบว่ามันมีตัวตนอยู่จริงๆ (แต่ก็ไม่ได้แสดง Tool Tips อะไรออกมาเลย 555)

วิธีการใช้งานของ DATEDIF

DATEDIF( start_date, end_date, interval )

โดยที่ interval เป็นการเลือก Mode ของการแสดงผลลัพธ์ ซึ่งมีหลายแบบ ดังนี้

โหมดการทำงานของ DATEDIF

• d : ห่างกันกี่วัน
• m : ห่างกันกี่เดือน (เต็มเดือน)
• y : ห่างกันกี่ปี (เต็มปี)
• md : ห่างกันกี่วัน (ไม่สนใจเดือน เสมือนอยู่เดือนเดียวกัน)
• yd : ห่างกันกี่วัน (ไม่สนใจปี เสมือนอยู่ปีเดียวกัน)
• ym : ห่างกันกี่เดือน (ไม่สนใจปี เสมือนอยู่ปีเดียวกัน)

ตัวอย่างการใช้งานดังรูป

พอเห็นแบบนี้แล้วหลายคนคงสงสัยว่า ทำไมถึงต้องเอาฟังก์ชันที่เจ๋งขนาดนี้ไปซ่อนให้ลึกลับด้วยล่ะ?

สาเหตุเพราะฟังก์ชันนี้มันมีพฤติกรรมแปลกๆ หลายอย่างเลย ทาง Microsoft ถึงกับเขียนไว้ใน support document เลยนะ ว่ามันน่ากลัวมาก 555 ลองไปอ่านดูได้

อาการแปลกๆ ของ DATEDIF

อาการนับจำนวนติดลบหากวันเริ่มอยู่ในช่วงปลายเดือน

ถ้าเริ่มวันที่ 31 แล้วจบแถวๆ สิ้นเดือนที่มี 31 วัน ก็จะดูไม่มีปัญหาอะไร ทุกอย่างดูดี โปรแกรมมองว่าครบวันพอดีที่วันที่ 31

ถ้าเริ่มวันที่ 31 แล้วจบแถวๆ สิ้นเดือนที่มี 30 วัน สังเกตว่ามันจะมองว่าครบเดือนเต็มๆ ในวันที่ 1 ของเดือนถัดไป (จินตนาการว่าถ้าเดือนเมษามี 31 วันจะจบวันนั้นแหละ)

สาเหตุเพราะว่ามันพยายามมองว่าการจบเดือน เปรียบเสมือนต้องจบเดือนในเลขวันเดียวกับวันเริ่มต้นนั่นเอง

ถ้าเริ่มวันที่ 31 แล้วจบแถวๆ สิ้นเดือน กพ. มันจะมองว่าครบเดือนเต็มๆ เลยไปอีก (จินตนาการว่าถ้าเดือน กพ. มี 31 วันจะจบวันนั้นแหละ) ส่งผลให้การนับจำนวนวันติดลบได้

และถ้ายิ่งปีที่กพ. มี 28 วัน (ก็ปีปกติแหละ) ยิ่งแปลกหนัก คือติดลบ 2 เลยทีเดียว เพราะมันจะมองว่าครบเดือนเต็มๆ ในวันที่ 3 (จินตนาการว่าถ้าเดือนกพ. มี 31 วันจะจบวันนั้นแหละ)

อาการแปลกๆ อันอื่น

ผมมองว่าอาการแปลกอื่นๆ เป็นเรื่องของความต้องการที่เจาะจงของแต่ละคน ซึ่งอาจจะต้องการไม่เหมือนกัน

เช่น บางครั้งเวลาผมไปสอนลูกค้า เค้าก็บอกว่าอยากให้เมื่อเริ่มสิ้นเดือน จบสิ้นเดือน (แม้เลขจะน้อยกว่า) เช่น เริ่ม 31 มค. จบ 30 เมษา แบบนี้ให้ถือว่าครบเต็มเดือนเดือน ณ วัน สิ้นเดือนนั้นๆ ไปเลย ซึ่งจะเห็นว่า DATEDIF จะยังไม่ได้มองว่าครบเดือน (แต่จะมาครบในวันที่ 1)

ถ้าคุณมีความต้องการแบบเดียวกับลูกค้าของผม มันก็ต้องมีการ Adjust สูตรให้มันทำงานให้ได้ดั่งใจ ซึ่งมีแนวทางดังนี้

แนวทางการปรับสูตร DATEDIF ให้ทำงานดั่งใจ

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

จะเห็นว่าเราสามารถทำให้ถือว่าครบเดือนในวันสิ้นเดือนได้ ซึ่งเอาไว้ใช้ adj แบบ m และ ym ได้ทั้งคู่เลย

เริ่มวันที่ 30 ก็ใช้ได้

ถ้าวันเริ่ม <=28 มันก็จะไม่มีการ adjust เพราะเลขวันเริ่มไม่ได้มากกว่าวันจบ (มันถูกต้องอยู่แล้ว)

การ adjust เรื่อง md ที่มีติดลบ

เราต้องถามตัวเองแหละว่าต้องการผลลัพธ์แบบไหนถึงเรียกว่าถูกใจเรา?

ผมคิดว่าถ้าเลขวันเริ่ม <=28 เราทุกคนไม่น่ามีปัญหากับผลลัพธ์ของ DATEDIF ดังนั้นมันจะเริ่มมีปัญหากับการเริ่มด้วยวันที่ 29,30,31 เท่านั้น

แต่เราลองมาดูก่อนว่า ถ้าเริ่มวันที่ 28 มันให้ผลลัพธ์แบบไหน ซึ่งจะพบว่าถ้าจบวันที่ 28 จะถือว่า md เป็น 0 แล้ววันถัดไปเป็น 1,2,3… ไปเรื่อยๆ

ดังนั้นถ้าเริ่มวันที่ 29 เราก็น่าจะอยากให้ถ้ามันจบวันที่ 29 ก็เป็น 0 แล้วไล่ไปเรื่อยๆ แต่ปรากฏว่ามันไม่ได้ทำงานแบบนั้น โดยเฉพาะหากเดือนก่อนหน้ามีไม่ถึงวันที่ 29

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

ซึ่งจะเห็นว่าผลลัพธ์ได้ดั่งใจแล้ว

ลองเปลี่ยนเลขไปเริ่ม 31 ว่า work มั้ย ก็พบว่าน่าจะได้ดั่งใจแล้วนะ

ลองเลขน้อยๆ ก็ดู ok ไม่ได้มีการ adjust อะไร

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

สรุปสูตรทั้งหมด

คำนวณปี

=DATEDIF(วันเริ่ม,วันจบ,"y")

คำนวณเดือน

=IF(AND(DAY(วันเริ่ม)>DAY(วันจบ),วันจบ=EOMONTH(วันจบ,0)),
DATEDIF(วันเริ่ม,วันจบ,"ym")+1,DATEDIF(วันเริ่ม,วันจบ,"ym"))

คำนวณวัน

=IF(DAY(วันเริ่ม)>DAY(วันจบ),IF(วันจบ=EOMONTH(วันจบ,0),0,
IF(DAY(EOMONTH(วันจบ,-1))<DAY(วันเริ่ม),DAY(วันจบ),DATEDIF(วันเริ่ม,วันจบ,"md"))))

ตัวอย่างการคำนวณ

ลองคำนวณ อายุงาน เป็นจำนวน ปี เดือนวัน ด้วยสูตรใหม่ดูได้ดังนี้

ผมสามารถแก้สูตร DATEDIF ให้ได้ผลลัพธ์ดั่งใจ(ผม)แล้ว แต่จะให้ได้ดั่งใจคุณรึเปล่าไม่รู้นะครับ เพราะแต่ละคนชอบไม่เหมือนกัน อาจต้องหา Logic มาปรับให้ตรงใจคุณอีกทีนะ ^^

แชร์ความรู้ให้เพื่อนๆ ของคุณ