สูตร excel วันที่ ลบวันที่

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

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

เรื่องพื้นฐาน เกี่ยวกับวันที่

ก่อนจะเข้าเรื่อง ฟังก์ชันเกี่ยวกับวันที่ ที่อยากแนะนำ เรามาทบทวนเรื่องพื้นฐานเกี่ยวกับเรื่องวันที่ ใน Excel สักเล็กน้อย (ความจริงเคยเขียนไปแล้วเรื่อง วัน-เดือน-ปี ใน excel)

Excel เก็บวันที่แบบไหน

ถ้าใครช่างสังเกตนิดหนึ่ง จะเห็นว่า เวลาพิมพ์วันที่เข้าไป ทาง Excel จะจัดชิดซ้ายให้โดยอัตโนมัติ นั่นหมายถึงว่าอยู่ในกลุ่มเดียวกับ Numbers (ถ้าเป็น Text ที่เอาไปคำนวณไม่ได้จะชิดขวา) ซึ่งก็เคยเขียนไปครั้งหนึ่งแล้วว่า ความจริง Excel ไม่ได้เก็บวันที่ในรูป “วันที่” แต่เก็บในรูปแบบ ตัวเลขเรียงลำดับ – serial number แล้วประมวลผลให้เห็นเป็นวันที่ โดยจะเริ่มนับ 1 ที่วันที่ 1 มกราคม ค.ศ. 1900

• 2 ก็จะเป็น 2 มกราคม ค.ศ. 1900
• 3 ก็จะเป็น 3 มกราคม ค.ศ. 1900
• 4 ก็จะเป็น 4 มกราคม ค.ศ. 1900
• 5 ก็จะเป็น 5 มกราคม ค.ศ. 1900

และนับ 6 7 8… ไปเรื่อย ๆ อย่างเช่นวันที่เขียนอยู่นี้ 28 ธันวาคม ค.ศ. 2020 ก็จะเป็นเลข 44193

เข้าใจว่า (อันนี้มโนล้วน ๆ) การเก็บวันที่ในรูปแบบ ตัวเลข ก็เพื่อให้นำไปคำนวณ บวก ลบ ได้ ลองบวกหรือลบวันเล่น ๆ ดูก็ได้ เช่น บวก 20 วัน ย้อนหลัง 2 วัน มันก็ออกมาได้เหมือนกัน

และจากตัวอย่าง ท่านจะเห็นอะไรแปลก ๆ คือตรงวันที่ 1 มกราคม ค.ศ. 1900 พอ – 2 วันแล้วผลลัพธ์ออกมาเป็น ########## หรือไม่แสดงผล ก็เป็นเพราะ Excel เริ่มนับ 1 ในวันที่ 1 มกราคม ค.ศ. 1900 จะไม่แสดงผลก่อนหน้านั้น ลองดูวันที่ 2 มกราคม ค.ศ. 1900 ก็ได้ พอลบ 2 วัน จะกลายเป็นวันที่ 0 มกราคม ค.ศ. 1900 ทั้งที่ควรเป็น 31 ธันวาคม ค.ศ. 1899 อันนี้ก็ด้วยเหตุผลข้างต้นที่อธิบายไปแล้วว่ามันเริ่มที่เดือนมกราคม ค.ศ. 1900

เรื่องนี้เคยเป็นปัญหา เวลาบันทึกข้อมูลวันที่ผิดรูปแบบ (เครื่องเก็บเป็น พ.ศ. แต่บันทึกเป็นค.ศ. ทำให้เกิดปัญหาเมื่อส่งจาก Access มา Excel แล้ววันที่หายไป หรือ กลายเป็น ##########

การใส่ข้อมูลวันที่

เรื่องพื้นฐานที่บอกทุกครั้ง นั่นคือการกรอกวันที่ให้ถูกรูปแบบ อย่าเพิ่งชาตินิยมใช้ พุทธศักราช เวลาทำงานพวกนี้ใช้ คริสต์ศักราช ไปเลย

หลายต่อหลายครั้ง พบว่า เครื่องคอมพิวเตอร์นั้นตั้งค่า Region (อยู่ใน Setting หรือ Control Panel) เป็น English (United States) ซึ่งจะมีปัญหาแรกคือ US จะตั้งค่าให้เป็นเดือนก่อนวัน คือ 08/02/2020 จะไม่ใช่วันที่ 8 กุมภาพันธ์ 2563 แต่จะกลายเป็นวันที่ 2 สิงหาคม 2563 แทน

เรื่องนี้สำคัญนะจ๊ะ กำหนด Region ผิด เวลากรอกข้อมูลลงไปมันอาจผิดความจริงไปหลายเดือน และบางทีกว่าจะรู้ตัวก็แก้ไขลำบากแล้ว โดยส่วนตัว เวลาตั้ง Region จะตั้งเป็น English (United Kingdom) ซึ่งมี format เป็น วัน/เดือน/ปี แบบที่คนทั่วโลก (ยกเว้นสหรัฐอเมริกา) คุ้นเคย

ถ้าอยากให้แสดงผลเป็น พุทธศักราช ให้ใช้ Format Cells เป็นตัวช่วย (คลิกขวา เลือก Numbers เลือกเป็น Date และเลือกปฏิทินเป็น พุทธศักราช)

หรือ ถ้าจะบังคับการแสดงผล เราสามารถใช้ Custom Format Numbers โดยแทนที่จะเป็น Date ให้เปลี่ยนเป็น Custom สำหรับภาษาอังกฤษแบบสหราชอาณาจักร ให้ใช้  [$-en-GB]  และ สำหรับบังคับให้ออกมาทางไทย ให้ใช้ [$-th-TH,107]

เช่นตัวอย่างนี้ใช้ [$-th-th,107] dd mmmm yyyy จะเป็นการบังคับให้ออกมาเป็นเดือนภาษาไทยและเป็นปีพุทธศักราชในแบบ วัน – เดือน – ปี

เวลาพิมพ์เข้าไปตรง ๆ เช่น 28/12/2020 หรือ 28-12-2020 ตัว Excel ก็จะรับรู้ว่านี่คือวันที่ และพร้อมให้เอาไปคำนวณต่อได้

แสดงวันที่ปัจจุบันด้วย TODAY

ฟังก์ชันเกี่ยวกับวันที่ใน Excel มีหลายแบบ เริ่มต้นด้วยแบบง่าย ๆ ก็คือ ให้แสดงค่าวันที่ “วันนี้” เพียงพิมพ์สูตร

=TODAY ()

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

เราสามารถแยก ให้แสดงเฉพาะ วัน หรือ เดือน หรือ ปี ได้ เพียงแค่ใส่กำกับ

ถ้าจะให้แสดงแค่วันที่ ใช้สูตร

=DAY(TODAY())

ถ้าจะให้แสดงแค่เดือน ใช้สูตร

=MONTH(TODAY())

ถ้าจะให้แสดงแค่เดือน ใช้สูตร

=YEAR(TODAY())

อย่างที่เขียนไปข้างต้น เราสามารถใช้ฟังก์ชัน TODAY เพื่อแสดงวันที่ปัจจุบัน และเราสามารถ เอามันมา บวก ลบ กันได้ปกติ

เช่น

=TODAY()-A2

โดยใน A2 จะเป็นวันที่กำหนด

จะนำไปบวกหรือลบวันก็ได้ เช่น

=TODAY()-7

ก็หมายถึง 7 วันก่อนหน้านี้

หรือ ถ้าจะนับจำนวนปี เช่น เราอยากรู้ว่า ถ้า คนเกิดปี ค.ศ. 1986 ปัจจุบันอายุกี่ปี เราใช้สูตร

=YEAR(TODAY())-1986

แบบนี้เป็นต้น

หรือจะนับเป็นจำนวนวันก็ได้ โดยเอาไปลบจากวันที่เลยโดยตรง

=TODAY()-A2 โ

ดยใน A2 จะเป็นวันที่กำหนด

 TIPS – ถ้าต้องการให้แสดงทั้งวันและเวลา ให้ใช้ NOW()

TIPS ครั้งที่ 2 – ถ้าต้องการใส่ค่าวันที่ปัจจุบัน แต่ให้เก็บถาวร ไม่เปลี่ยน อย่าใช้ฟังก์ชัน TODAY ให้ใช้ keyboard shortcut คือ Ctrl + ;

นับวันด้วย DATEDIF

สิ่งที่โดนถามบ่อยที่สุด คือเรื่องการนับจำนวนวัน อย่างเช่น เริ่มโครงการวันที่ 1 มกราคม 2563 สิ้นสุดโครงการวันที่ 31 ธันวาคม 2563 จะใช้ ฟังก์ชันเกี่ยวกับวันที่ อันไหน

ใช้ฟังก์ชันนี้จ้ะ Datedif ซึ่งน่าจะเป็นฟังก์ชันเกี่ยวกับวันที่ ซึ่งใช้กันบ่อย

ความจริง เอาง่าย ๆ ก่อน ใส่ 1 มกราคม 2563 ที่ช่อง A2 ใส่ 31 ธันวาคม 2563 ที่ช่อง B2 แล้วที่ C1 ให้ใส่สูตร B2 – A2

ง่ายแบบนี้เลยจ้ะ

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

มันง่ายไปใช่มั้ย? เดี๋ยวดูไม่โปรฯ  งั้นเรามาใช้สูตรกันเถอะ เราใช้ฟังก์ชัน DateDif ก็ได้

เราลองมาเขียนสูตรตามนี้

=DATEDIF(A2, B2,"D")

ผลลัพธ์ก็จะได้ 365 เท่ากับเอา B2 – A2 นั่นแหละ

สูตรนับวัน DATEDIF มีไวยากรณ์ในการเขียนดังนี้

DATEDIF (วันเริ่มต้น, วันสิ้นสุด, รูปแบบเวลา)

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

D – นับวัน

M – นับเต็มเดือน (ถ้ามีวันตัดทิ้ง)

Y – นับเต็มปี ตัดเศษทิ้ง

MD – นับวัน ไม่สนใจเดือนหรือปี (คือหักเดือนปีออก เหลือแต่วันที่เป็นเศษ) แต่ แต่ช้าแต่ ทางไมโครซอฟต์เคยออกคำเตือนว่า การคำนวณหาค่า MD อาจจะให้ผลลัพธ์ที่ผิดพลาดเป็น 0 หรือ ติดลบ ไม่ควรใช้ ไม่รู้ว่าตอนนี้มีการปรับปรุงแก้ไขหรือยัง อันนี้ก็ต้องแจ้งให้ทราบก่อน แต่เท่าที่ลองใช้งานดูก็ไม่ผิดพลาด แต่สามารถใช้สูตรหักลบออกจากของเดิมได้โดยใช้ฟังก์ชัน DATE โดยถ้าวันสิ้นสุดอยู่ที่ B2 เอาลองใช้สูตรหักลบ Year และ Month ออก และ 1 คือวันตั้งต้นทีจะวันหลังลบปีและเดือนออกไป ก็จะได้ B2-DATE(YEAR(B2), MONTH(B2), 1)

YM – นับเดือนไม่สนใจปีหรือวัน (คือนับเดือนจำนวนเต็มหลังจากหากปีเต็มไปแล้ว)

YD– นับวัน ไม่สนใจปี นับวัน ที่หักปีไปแล้ว

เช่นในตัวอย่าง เราจะนับวัน ก็ใส่สูตรดังนี้

=(DATEDIF(A2,B2, “D”)

ระวัง DateDif จะไม่เหมือนกับการเอามาลบกันตรง ๆ แบบตัวอย่างก่อนหน้า ตรงที่ ถ้า วันเริ่มต้น เป็นวันที่ใหม่กว่า วันสิ้นสุด  ผลลัพธ์จะได้เป็น #NUM ในขณะที่การเอามาลบกันธรรมดาอย่างตัวอย่างก่อนหน้านั้น จะได้ผลลัพธ์เป็นลบเหมือนลบตัวเลขเฉย ๆ

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

ทีนี้ถ้าอยากได้ผลลัพธ์มาว่า ระหว่างวันที่เรากำหนด มันกี่ปี กี่เดือน กี่วัน ก็ต้องเอาสูตรมารวมกัน

=DATEDIF(A2,B2,"Y")&" ปี "&DATEDIF(A2,B2,"YM")&" เดือน "&DATEDIF(A2,B2,"md")&" วัน"

มันก็จะดูยุ่งยากหน่อย

นับวันด้วยฟังก์ชัน DAYS

ฟังก์ชัน Days นี้ ใช้งานง่าย ๆ ไม่ยุ่งยาก ให้ผลลัพธ์แบบเดียวกับ DateDif เลยจ้ะ ฟังก์ชันเกี่ยวกับวันที่นี้ มีวิธีการเขียนสูตรดังนี้

DAYS (วันสิ้นสุด, วันเริ่มต้น)

โปรดสังเกต Days จะใช้ วันสิ้นสุด ขึ้นก่อนอันนี้จะต่างจาก DateDif และ ถ้าเผลอใส่ วันเริ่มต้น เป็นวันที่ใหม่กว่า วันสิ้นสุด ผลลัพธ์ก็ไม่ error แต่จะขึ้นติดลบ เหมือนกับ การเอาตัวเลขมาลบกันธรรมดา ๆ นี่เอง

นับเฉพาะวันทำงาน ด้วย NETWORKDAYS

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

สูตรการเขียนฟังก์ชันคือ

=NETWORKDAYS(วันเริ่ม,วันสิ้นสุด,[วันหยุดพิเศษ])

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

=NETWORKDAYS(A2,B2)

ก็จะได้ผลลัพธ์เป็น 382 วัน แต่ ถ้ามีวันหยุดพิเศษเพิ่มเติมนอกเหนือจากวันเสาร์อาทิตย์ ก็ใส่เพิ่มเข้าไปได้ เช่น ระบุวันหยุดพิเศษลงไปในช่อง E2 ถึง E7 จะได้สูตรดังนี้

=NETWORKDAYS(A2,B2,E2:E7)

ผลลัพธ์ออกมาเป็น 376 วัน

หรือในกรณีที่ วันหยุดของเราไม่ใช่ เสาร์ อาทิตย์ จะต้องใช้ฟังก์ชัน NETWORKDAYS.INTL เพื่อกำหนดวันหยุดที่เฉพาะเจาะจง

วิธีการเขียนสูตรก็จะคล้าย ๆ กัน คือ

= NETWORKDAYS.INTL (วันเริ่มต้น, วันสิ้นสุด, วันหยุด)

แต่วันหยุดนี้จะแตกต่างไป คือ จะเป็น code ดังนี้

จำไม่ได้ไม่เป็นไร เวลาเขียนสูตรในฟังก์ชันมันจะโผล่มาบอกเอง

อย่างเช่น ถ้าวันหยุดเป็นวัน อาทิตย์ กับวันจันทร์ ก็จะเป็น

=NETWORKDAYS.INTL (A2,B2,2)

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

=NETWORKDAYS.INTL (A2,B2, “0101001”) ก็คือ อังคาร พฤหัส และ อาทิตย์ เป็นวันหยุด หรือ เรารู้ว่าจำนวนวันทำงานว่ากี่วัน (หรือกี่เดือน กี่ปี) เราก็คำนวณหาวันเริ่มต้น หรือ วันสิ้นสุดได้ เหมือนกัน โดยใช้ฟังก์ชัน WORKDAY

ซึ่ง Today, DateIf, Days และ NetWorkDays เป็น ฟังก์ชันเกี่ยวกับวันที่ ที่มักจะใช้งานกันบ่อย

สำหรับการแสดงวันที่ อย่างที่ต้องการพิเศษ โปรดอ่าน Function Text