เดินทางข้ามเวลาด้วยฟังก์ชั่นกลุ่ม Date Time เพื่อจัดการข้อมูลวันที่และเวลา

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

ฟังก์ชั่นเกี่ยวกับการจัดการข้อมูลวันที่

DATE ใช้ประกอบร่าง ตัวเลขปี เลขเดือน เลขวัน ให้กลายเป็นรูปแบบของวันที่

DATE(year, month, day) 

เช่น หากใส่สูตรใน A1 ว่า

=DATE(2018,11,24) => 24/11/2018 (24 พฤศจิกายน ค.ศ. 2018)

Tips1 : สิ่งที่ต้องระวังก็คือ เราจะต้องแทนปีด้วยเลข คศ. นะครับ

Tips2 : เราสามารถใส่ day เป็นเลขติดลบ หรือ เลขที่มากกว่า 31 วัน ได้ด้วยนะครับ วันที่มันจะเลื่อนกลายเป็นของเดือนอื่นไป และที่เจ๋งคือ ถ้าเราใส่เป็นเลข 0 มันจะกลายเป็นวันสุดท้ายของเดือนก่อนครับ !!
=> ตัวอย่าง http://www.thepexcel.com/last-day-of-month/

ต่อไปเป็นการหา ปี เดือน วัน จากข้อมูลที่เป็นวันที่

YEAR ใช่หาว่าเลขวันที่ที่กำหนด อยู่ในปีที่เท่าไหร่

YEAR(serial_number)

เช่น = YEAR(A1) จะได้เลข 2018 

MONTH ใช่หาว่าเลขวันที่ที่กำหนด อยู่ในเดือนที่เท่าไหร่

MONTH(serial_number)

เช่น = MONTH(A1) จะได้เลข 11

DAY ใช่หาว่าเลขวันที่ที่กำหนด อยู่ในวันที่เท่าไหร่

DAY(serial_number)

เช่น = DAY(A1) จะได้เลข 24

EDATE ใช้เลื่อนวัน โดยบวกจำนวนเดือนเพิ่มเข้าไปในวันที่ที่กำหนด เช่น

EDATE(start_date,months)

ให้ C1 = 31/01/2018

EDATE(C1,1) => 28/2/2018 (ไม่มีวันที่ 31/2/2018 จึงปรับเป็นวันสิ้นเดือนให้)

EDATE(C1,2) => 31/3/2018

EDATE(C1,3) => 30/4/2018 (ไม่มีวันที่ 31/4/2018 จึงปรับเป็นวันสิ้นเดือนให้)

EDATE(C1,4) => 31/5/2018

EDATE(C1,-1) => 31/12/2017 (ติดลบแล้วจะถือว่าย้อนเวลากลับไปในอดีต)

การแยกข้อมูลวันที่และเวลาออกจากกัน

เราสามารถแบ่งข้อมูล วันที่/เวลา ออกเป็นสองส่วนได้ นั่นคือ ส่วนของวันที่ และ ส่วนของเวลา

ส่วนของวันที่

วันที่คือ จำนวนเต็ม ดังนั้น เราจะใช้ฟังก์ชั่น =INT(number) มาช่วย (ให้ค่ากลับมาเป็นจำนวนเต็ม)

ส่วนของเวลา

เวลาคือ ทศนิยม ดังนั้นเราจะแยกส่วนของทศนิยมออกมาได้ เราต้องใช้ความรู้เรื่องของการหารเศษส่วน นั่นคือ

อะไรก็ตาม หารด้วย 1 จำนวนเต็มจะถูกหารลงตัวหมด ดังนั้น เศษ มันคือ ทศนิยมนั่นเอง ดังนั้นเราจะหาเศษเหลือของการหารได้ด้วยฟังก์ชั่น =MOD(number,divisor) มาช่วยครับ โดยใช้ =MOD(ข้อมูลวันที่และเวลา,1)

ซึ่ง พอเราเปลี่ยน Format ให้เหลือแต่เวลาแล้ว ก็จะมองเห็นเป็นเวลาตามที่เราต้องการครับ

การคำนวณค่าหาวันสุดท้ายของเดือน

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

วิธีที่1 ใช้ฟังก์ชั่น =EOMONTH(start_date,[months]) โดยใส่วันที่ที่ต้องการลงไปในช่อง start_date แต่ถ้าขี้เกียจจำฟังก์ชั่นเยอะแยะ จริงๆ เราใช้อีกวิธีก็ได้ครับ นั่นคือ

วิธีที่ 2 ใส่ day ในฟังก์ชั่น =DATE(year,month,day) เป็นเลข 0 โดยใส่ month เป็นเดือนถัดไป เช่น ผมใส่เป็น =DATE(2014,3,0) มันจะได้ว่า 28/2/2014 ครับ ซึ่งจะเป็นวันสุดท้ายของเดือนก่อนหน้านั่นเอง เราสามารถเอาตรงนี้มาประยุกต์ใช้หาวันสุดท้ายของแต่ละเดือนได้ครับ

การหาวันที่ใน xx เดือน ก่อนหน้า/ถัดไป

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

1. ใช้ฟังก์ชั่น DATEเหมือนเดิม

ถ้าเราใช้ DATE เราสามารถหาวันที่ ใน 6 เดือนข้างหน้าได้โดยการบวกเลข 6 ลงไปในช่อง month ได้เลยครับ เช่น ผมปรับจากตัวอย่างที่แล้ว เป็น =DATE(2014,12+6,13) = 13/6/2015

2. ใช้ EDATE มาช่วย

ถ้าเราใช้ EDATE จะมีวิธีกรอกดังนี้ครับ =EDATE(start_date,months) คือให้เราใส่วันที่ลงไปใน start_date และใส่จำนวนเดือนที่ต้องการให้เลื่อนเวลาไปในช่อง months เช่น ถ้าผมเลื่อนจาก 13 ธันวาแบบข้างบน สมมติผมใส่วันที่ 13/12/2014 ไว้ในช่อง A1 ผมจะต้องเขียนเพื่อคำนวณหาวันในอีก 6 เดือนถัดไปว่า =EDATE(A1,6) = 13/6/2015 เช่นกัน

ที่นี้มาลองดูกันครับว่าถ้าเราเริ่มที่ 31 ธันวาคม 2557 บวกไปอีก 2เดือนเพื่อให้เป็นกุมภาพันธ์ที่มีแค่ 28 วันมันจะเป็นยังไง?

ถ้าเราใช้ DATE ใส่ว่า =DATE(2014,12+2,31) จะได้ออกมาเป็น 3/3/2015 นั่นคือ 3 มีนาคม 2558 เพราะว่าวันที่ 31 มันเลยวันที่ 28 ไปอีก 3 วัน นั่นเอง

แต่ถ้าเราเปลี่ยนมาใช้ EDATE แทน (ผมใส่ 31/12/2014 ไว้ในช่อง A1) จะได้ว่า =EDATE(A1,2)= 28/2/2015 ซึ่งได้ 28 ธันวาคม โดยไม่เลื่อนวันให้เหมือนการใช้ DATE

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

การคำนวณเกี่ยวกับวันทำงาน (Working Day)

อันนี้เป็นการทำงานยอดฮิต ที่หลายคนต้องใช้บ่อยๆ ซึ่งปัญหาหลักๆ ที่พบเจอมี 2 กรณีดังนี้

กรณี 1 : รู้วันเริ่มและวันสิ้นสุด อยากรู้จำนวนวันทำงานระหว่างสองวันนั้น –> ใช้ NETWORKDAYS

กรณี 2 : รู้วันเริ่ม (หรือวันสิ้นสุด) แล้วรู้จำนวนวันที่ต้องใช้ แต่อยากรู้วันที่ปลายทาง –> WORKDAY

ซึ่งจะตอบโจทย์ทั้งสองกรณีข้างล่างนี้ได้ เราจะต้องเตรียมข้อมูลวันหยุดพิเศษเสียก่อน Excel จึงจะรู้ว่าวันไหนเป็นวันทำงานบ้าง โดยมันจะดูว่า ถ้าเป็นวันเสาร์, อาทิตย์, หรือวันหยุดพิเศษ จะถือว่าไม่ใช่วันทำงานนั่นเอง และเพื่อเป็นการอำนวยความสะดวกให้ทุกท่าน ผมได้เตรียมตารางข้อมูลวันหยุดพิเศษในแต่ละปีให้แล้ว ไปดาวน์โหลดได้ที่ https://www.thepexcel.com/holiday-excel-10-years/ ได้เลย ฟรีๆ ครับ

วิธีทำ กรณีที่ 1

ใช้สูตร =NETWORKDAYS(start_date,end_date,holidays) มาช่วยครับ โดยที่สูตร NETWORKDAYS จะนับทั้งวันเริ่มและวันจบด้วย แต่จะไม่นับวันเสาร์-อาทิตย์ และวันที่ระบุใน Holidays  ซึ่งเราต้องใส่ข้อมูลวันหยุดพิเศษลงไปใน argument ที่ชื่อว่า holidays นี่แหละครับ

ตัวอย่าง

• วันเริ่ม = 2 มีนาคม พ.ศ. 2558 ซึ่งผมพิมพ์ไว้ในช่อง A1 ว่า 2/3/2015
• วันสิ้นสุด = 9 มีนาคม พ.ศ. 2558 ซึ่งผมพิมพ์ไว้ในช่อง A2 ว่า 9/3/2015
• สมมติว่าผมเตรียมข้อมูลวันหยุดพิเศษไว้แล้ว ในช่อง H1:H100 ซึ่งในระหว่าง 2 วันข้างต้น มีวันที่ 4/3/2015 เป็นวันหยุดพิเศษเนื่องจากเป็นวันมาฆบูชาครับ

หาจำนวนวันทำงานในช่อง A4 =NETWORKDAYS(A1,A2, H1:H100) จะได้ว่า = 5 วัน เพราะว่า

ปฏิทิน	วันทำงาน (ที่นับ)	วันที่ไม่นับ
	2/3/2015 (นับวันเริ่ม) 3/3/2015 5/3/2015 6/3/2015 9/3/2015 (นับวันจบ)	4/3/2015 เพราะอยู่ใน Holidays 7/3/2015 เพราะเป็นวันเสาร์ 8/3/2015 เพราะเป็นวันอาทิตย์

วิธีทำ กรณีที่ 2

ในทางกลับกัน หากผมเริ่มที่ 2 มีนาคม พ.ศ. 2558  ซึ่งผมพิมพ์ไว้ในช่อง A1 ว่า 2/3/2015

แล้วผมมีวันทำงานเป็น 5 วัน ผมก็จะหาวันสิ้นสุดได้จาก =WORKDAY(start_date,days,holidays)
=WORKDAY(A1,A4,H1:H100) = 10/3/2015 ซึ่งไม่ตรงกับตัวอย่างข้างบนซะงั้น!! สาเหตุเป็นเพราะว่าสูตร WORKDAY มันไม่นับจุดเริ่มต้นนั่นเอง (ต่างจาก NETWORKDAYS) ตรงนี้เป็นจุดที่ต้องสังเกตดีๆ ครับ