เพิ่มลดวันเดือนปี ใน Excel แบบง่าย ๆ โดยใช้ฟังก์ชัน DATE เผื่อบางทีจะช่วยแก้ปัญหาเรื่องวันที่โดยไม่ต้องแก้ยุ่งยาก
วันนี้มาเรื่องสั้น ๆ คือมีน้องมาปรึกษาว่าว่าเมื่อนำไฟล์ข้อมูลจาก Text File เข้า Excel แล้วมีปัญหา 2 เรื่อง
- ต้นทางใส่เดือนก่อนวัน แล้วจึงเป็นปี เช่น 12 พฤศจิกายน 2020 ใส่ข้อมูลมาเป็น 11-12-2020
- บางไฟล์ ข้อมูลวันที่ บันทึกเป็นปีพ.ศ. พอนำมารวมแล้วมีปัญหาเรื่อง format ที่ไม่เหมือนกัน และมีปัญหาเรื่องการคำนวณวันตามมา
เรื่องของวันที่ มันน่าปวดหัว
อันนี้ขอบ่นหน่อยเถอะ เรื่องวันที่ดูเหมือนเรื่องง่าย ๆ ไม่มีอะไร แต่ก็เป็นปัญหาให้ตามแก้กันได้เรื่อย ๆ คล้ายกับที่เคยได้เขียนถึงวิธีแก้ปัญหาไปแล้วใน Access พอย้ายมาทำใน Excel ก็ยิ่งมีปัญหาจุกจิกกวนใจ
ปัญหาข้อแรกของน้องเขา เกิดจากเครื่องคอมฯ ของต้นทาง ใช้ระบบวันที่แบบอเมริกา (ซึ่งก็ไม่เข้าใจเหมือนกันทำไมอเมริกามันไม่ใช้รูปแบบเหมือนส่วนอื่นทั่วโลกบ้าง)
ส่วนปัญหาข้อที่สอง เกิดจาก ไทยเราใช้พุทธศักราช แต่คนบันทึกไปตั้งค่าฟอร์แมตเป็นคริสศักราชเอาไว้ซะงั้น เป็นปัญหาของตัวบุคคล ซึ่ง บางทีก็เกิดจากความไม่รู้จริง ๆ บางทีก็เกิดจากความเลินเล่อ ไม่ใส่ใจ
เวลาพิมพ์วันที่ลงใน Excel มักจะแนะนำอย่างจริงใจเสมอว่า อย่าชาตินิยม อย่าพิมพ์ปีพ.ศ. อย่าพิมพ์เลขไทย ให้ใส่เป็น วัน เดือน ปี แบบนี้ 12/11/2020
ถ้าพิมพ์แบบอื่นจะเป็นอย่างไร?
- 12 พ.ย. 2020
- 12 พฤศจิกายน 2020
- 12 พ.ย. 2563
- 12 พฤศจิกายน พ.ศ. 2563
- ๑๒ พ.ย.๒๕๖๓
- 12/11/2563
ลองดูนะจ๊ะ
ตัวอย่างนี้ จัด format cell ให้เป็น Date แบบ คริสต์ศักราช
Excel ในปัจจุบัน “ฉลาด” พอจะรู้ว่าสิ่งที่เราพิมพ์เข้าไป เป็นอะไร และจัดการให้โดยอัตโนมัติ แม้แต่พิมพ์เป็นเลขไทยมันก็รับทราบไม่มีปัญหา (ตัวอย่างนี้ใช้ Microsoft 365 อัปเดตล่าสุด ณ วันที่พิมพ์ ฟอร์แมตหลักของเครื่องนี้ UK) แต่บางเวอร์ชัน หรือ การตั้งค่ามาตรฐานตั้งต้นของบางเครื่อง อาจจะออกมาเป็นแบบนี้
อย่างที่เห็น บางค่าจะโดนเก็บในรูปแบบ Text หรือ ข้อความ
จะมีบางอย่างเท่านั้นที่ยังคงมีปัญหาอยู่ คือ 12 พฤศจิกายน พ.ศ. 2563 พอมันมีคำว่า พ.ศ. เข้าไปแทน excel เลยคิดว่านี่คือ Text หรือข้อความ จะเห็นว่า เอาไปจัดชิดขวา นั่นคือ เก็บไว้ในรูปแบบ Text หรือ ข้อความ ซึ่งจะไม่สามารถนำไปคำนวณต่อไป
แต่ที่มีปัญหาคือ พอพิมพ์ ๑๒ พ.ย.๒๕๖๓ มันกลับไม่รับว่านี่คือ พ.ศ. 2563 ซึ่งควรจะแสดงผลเป็นปี 2020 มันกลายเป็นปี ค.ศ. 2563 แทนที่จะเป็น พ.ศ. 2563
และนี่คือปัญหาพื้นฐานที่เจอบ่อยมาก คือ ค่าของปี พ.ศ. สลับกับ ค.ศ.
ถ้าอยากพิมพ์ข้อมูลเป็นปีพุทธศักราช
ความจริง ถ้าใครชาตินิยม อยากใส่ข้อมูลเป็นปี พ.ศ. เพราะนับปีง่ายดีด้วยความคุ้นเคย รวมทั้งข้าพเจ้าด้วย สารภาพตามตรงว่าจะใส่เป็นปีพุทธศักราชเหมือนกัน
ถ้าคิดจะพิมพ์เข้าไปเป็น พ.ศ. ตั้งแต่แรก ให้คลิก Format Cells ไปที่แท็บ Number เลือก Category DATE แล้ว ติ๊กเลือก “input dates according to selected calendar”
เลือกแบบนี้ แบบนี้ถ้าพิมพ์ 12/11/2563 เข้าไป มันก็จะรู้ว่ากำลังรับข้อมูล “พ.ศ.” ไม่ใช่ “ค.ศ.”
อันนี้ช่วยได้ ถ้าอยากพิมพ์ปี พ.ศ. ลงไป โดยไม่กระทบกระเทือนต่ออะไรอื่น ยังคำนวณและแสดงผลได้เหมือนเดิม
แต่ถ้าพิมพ์เป็นอย่างอื่น ก็เหมือนเดิม คือกลายเป็นชิดขวา นั่นคือ ไม่สามารถนำมาคำนวณได้ และ โปรดสังเกต พอพิมพ์ 12/11/2020 Excel ก็จัดไปชิดขวา เพราะนี่ไม่เข้ารูปแบบวันที่ของ Excel นั่นคือ ถ้าเป็นปี พ.ศ. 2020 มันจะกลายเป็น ปี ค.ศ. 1477 ซึ่งเป็นปีก่อน 1900 ที่ Excel จะไม่รู้จักและไม่ยอมรับว่าเป็นวันที่
ดังนั้น วิธีการตั้งฟอร์แมตก็ไม่ช่วยอะไร ถ้ายังมีการพิมพ์ข้อมูลหลากหลาย ไม่เป็นไปในทิศทางเดียวกัน
ถ้าจะแนะนำอย่างจริงจังที่สุดคือ ต้องใส่วันที่ในรูปแบบเดียวกัน จะช่วยลดปัญหาที่มากวนใจได้สารพัน
โปรดอ่านเรื่อง วัน เดือน ปี ใน Excel ประกอบอีกครั้ง
แนวทางการแก้ปัญหา
ปัญหานี้คือตอนบันทึกหรือนำเข้าวันที่ เก็บข้อมูลในแบบพ.ศ. แต่ปลายทางที่รับดันตั้งค่า ค.ศ. เอาไว้ Excel จึงเก็บข้อมูลปีเป็น ค.ศ. 2560 แทนที่จะเป็น พ.ศ. 2560 เมื่อเราตั้งฟอร์แมตวันที่ในเซลนั้นให้แสดงปีพุทธศักราชและเดือนภาษาไทย มันก็เลยกลายเป็น 12 พฤศจิกายน 3103 แทนที่จะเป็น 12 พฤศจิกายน 2560
วันที่มันเพี้ยนเล็กน้อยตรงปี เช่น จากปี 2560 กลายเป็น 3103 นั่นคือบวกเพิ่มมา 543 อันเนื่องมาจากปัญหาเรื่องบันทึกปี พ.ศ. – ค.ศ. สลับกัน
อย่างที่บอกเสมอว่า วัน เดือน ปี ใน Excel มันเก็บไว้ในรูปแบบ ตัวเลข (Number) จะมีคุณสมบัติบวกลบได้ โดยจะมีค่าตั้งต้นเป็น “วัน” เรื่องนี้คงต้องท้าวความถึงเมื่อตอนเขียนเรื่องวันที่ไปครั้งที่แล้วว่า excel จัดเก็บวันที่เป็นวัน โดยเริ่มจากวันที่ 1 มกราคม ค.ศ. 1900 ดังนั้นการบวกหรือลบจึงไม่ใช่ปัญหา อย่างเช่น cell B2 จะเป็น A2+25 ผลลัพธ์ก็จะเท่ากับบวกวันเข้าไปจากที่กำหนดในช่อง A2 (ก็คือ 20/01/2560) ไปอีก 25 วัน จะได้ ผลลัพธ์จะได้ 14 กุมภาพันธ์
จากโจทย์ เราต้องการเพียงแค่เพิ่มปีเข้าไป 543 ปี เราจะคำนวณอย่างไร เอา 543×365 ไปอย่างนั้นหรืออย่างไร?
บ้าไปแล้ว แล้วบางปีมี 366 วันล่ะ จะทำอย่างไร?
Excel ก็มีฟังก์ชันพร้อมช่วยแก้ปัญหาอยู่แล้ว ชื่อว่า Date
Function DATE
ถ้าอ่านคำอธิบายขอไมโครซอฟต์ จะอธิบายว่า ใช้ฟังก์ชัน DATE ของ Excel เมื่อคุณต้องแยกค่าทั้งสาม แล้วรวมเข้าด้วยกันเป็นรูปแบบของวันที่ ไม่รู้ว่าจะทำให้เข้าใจง่ายขึ้นหรือว่างงมากขึ้น
รูปแบบสูตร: DATE(ปี,เดือน,วัน)
คืออันนี้ จะระบุค่า 3 ค่า แทน ปี เดือน วัน ซึ่งมีรายละเอียดภายใน ดังนี้
ปี
ค่าของ “ปี” ถ้าน้อยกว่า 1900 (อย่างที่เคยเล่าเรื่องการเก็บค่าวันที่ของ Excel ไปแล้วคงพอจำกันได้) Excel จะปรับค่าอัตโนมัติ
ถ้าเราใส่เลข ไม่เกิน 2 หลัก excel จะบวก 1900 ปี อัตโนมัติ หน้า เพื่อไม่ให้เป็นปีค.ศ. ที่น้อยกว่าปีค.ศ. 1900
เช่น ตรงตำแหน่งนี้ ถ้าเราใส่ 10 Excel จะคิดเป็น ปีค.ศ. 1910 หรือ พ.ศ. 2463 ไปแทน (1900+10) จะไม่ใช่ปี ค.ศ. 10 แต่ ถ้าค่า “ปี” ติดลบหรือเกิน 10,000 จะแสดงผลว่าค่าผิดพลาด #NUM!
เดือน
ส่วนค่าของ “เดือน” ก็คล้ายกันกับค่าของ “ปี” คือถ้าเกิน 12 จะเริ่มทบ 12 เดือนเป็น 1 ปีและเศษที่เกินจะเริ่มนับ 1 ใหม่โดยอัตโนมัติ เช่น ถ้าเป็น 25 จะกลายเป็นเดือน 1 หรือ มกราคม และบวกปีให้อีก 2 ปีโดยอัตโนมัติ (25/12 = 24 หรือ 2 ปี เศษ 1 คือเดือนมกราคม)
และในทางกลับกัน ถ้าค่าของ “เดือน” ติดลบ มันก็จะนับย้อนกลับไปแบบเดียวกัน เช่น -25 จะเท่ากับเวลาลบไป 2 ปี และส่งกลับค่าเดือน 11 หรือ พฤศจิกายน (-25/24 =2 ได้เศษ 1 เอา 12-1 ได้ 11 ก็คือเดือนพฤศจิกายน
วัน
“วัน” ก็เช่นกันกับเดือน คือรับค่า 1-31 แต่ถ้าใส่เดือนที่มี 30 วัน (เช่น มิถุนายน หรือ 6) มันจะทบไปอีกเดือนให้โดยอัตโนมัติ เช่น 31 มิถุนายน จะกลายเป็น 1 กรกฎาคม หรือถ้าใส่ตัวเลขไปอีก มันก็จะบวกค่าเพิ่มเข้าไปอีก เช่น 2018-06-40 จะกลายเป็น 19 สิงหาคม ไปแทน ในทางกลับกัน ถ้าค่าของ “วัน” ติดลบ ก็จะหักย้อนหลังกลับไปเหมือนกับ “เดือน”
ฟังก์ชันนี้มีประโยชน์มากเวลาที่จะรับข้อมูล วัน เดือน ปี แยกเป็นส่วน ๆ (หรือ column) นอกจากนี้ประโยชน์อีกอย่างของฟังก์ชัน DATE ก็คือแปลง TEXT เป็น DATE ได้ และ นำมาจัดการเรื่อง เพิ่มลดวันเดือนปี ได้ดีทีเดียว
แก้ปัญหา สลับตำแหน่ง เดือน – วัน – ปี
ดังนั้น ปัญหาข้อแรกที่ว่า ต้นทางใส่เดือนก่อนวัน ก็แก้ไขง่าย ๆ ตามนี้เอง
ขั้นแรก แยก ข้อมูลวันที่ ออกเป็น 3 คอลัมน์ โดยใช้ Text to Columns เป็นตัวช่วยแยก แบบ delimiter โดยใช้ / (ทับ หรือ สแลช – slash) เป็นตัวแยก
ขั้นต่อมา ใช้ ฟังก์ชัน DATE ซึ่งจะใช้ค่าสามค่า แทน ปี-เดือน-วัน ตามลำดับ เราก็กำหนด
DATE(เซลล์ที่เป็นปี, เซลล์ที่เป็นเดือน, เซลล์ที่เป็นวัน)
ตัวอย่าง คอลัมน์ B จะเป็น Reg_Date ซึ่งเป็นรูปแบบวันที่ต้นทาง ซึ่งมีปัญหาว่า ใส่เดือน นำหน้าวัน
เมื่อเราใช้ Text to Columns ก็จะได้ คอลัมน์ C (เดือน) คอลัมน์ D (วัน) คอลัมน์ E (ปี)
ในคอลัมน์ F จะเป็นการนำวัน เดือน ปี มารวมกันอีกครั้ง โดยเซลล์ F2 จะมีสูตรดังนี้
=DATE(E2,C2,D2)จากตัวอย่างจะเห็นปัญหาที่เขียนถึงในเรื่องของ ปี นั่นคือ ต้นทางบันทึกปีมาไม่ครบ ใส่แค่ 2 หลัก คือ 20 ทำให้ Excel ปรับเป็นปี 1920 โดยอัตโนมัติ ไม่ใช่ 2020 (ความจริง ตรงนี้ต้องเป็นปี 2018 ดังนั้น ความผิดพลาดนี้ ยังไงก็ผิดพลาด การเห็นอะไรแปลก ๆ ทำให้ กรอง ออกมาตรวจสอบภายหลังก็ดีอยู่
เพิ่มลดวันเดือนปี ใน Excel
ปัญหาต่อมา เพิ่มลดวันเดือนปี จะบวกหรือลบปีไปอีก 543 ปี เพื่อสลับค่า ค.ศ. กับ พ.ศ. อย่างไร
ใน cell ที่กำหนด (ในที่นี้คือ A2) ได้กำหนดฟอร์แมตเป็น Date อยู่แล้ว ไม่ได้แยกเป็นสาม cell ที่แยกวัน เดือน ปี ออกจากกัน เราจึงไม่สามรถบวกหรือลบวัน เดือน ปี เข้าไปตรง ๆ ได้ ต้องใช้ฟังก์ชันแยกออกมาอีกต่างหากคือฟังก์ชัน year month day เข้ามาช่วย ซึ่งฟังก์ชั่นพวกนี้จะช่วยแยก ปี เดือน และ วันออกจากค่าที่เก็บไว้ในรูป date ได้
ดังนั้นถ้าช่อง A2 ใส่ข้อมูลในฟอร์แมตวันที่อยู่แล้ว เพียงแต่ต้องลบปีไปอีก 543 ปีก็ใช้
=DATE(YEAR(A2)-543,MONTH(A2),DAY(A2))อธิบายได้ว่า เราลบ 543 จากปี ส่วน เดือนและวัน ให้ใช้ค่าเดิม
เพียงเท่านี้ก็เรียบร้อย ปัญหาเกี่ยวกับวันที่ คงมีโอกาสได้เขียนถึงอีกแน่นอน มีลางสังหรณ์ไว้แบบนั้น แต่ก็หวังว่าทริกที่นำเสนอครั้งนี้น่าจะช่วยท่านที่ประสบปัญหาแบบเดียวกันได้
Tip and Trick
จากรูปนี้ ขออธิบายเพิ่มเติม ถึงผลลัพธ์ที่ออกมาเสียหน่อย
- ปี ใน Row 3 เป็น 20 ทำให้ ผลลัพธ์ออกมาเป็นปี 1920 หรือ 2463
- เดือน ใน Row 4 เป็น 25 ซึ่งไม่มีเดือน 25 จึงทบ นับวนไปจนได้ 25 เดือน จึงเป็น มกราคม (นับ 1 เป็นมกราคม เมื่อค่าเป็น 25 จึงวนกลับมาที่ มกราคม
- เดือน ใน Row 5 เป็น -25 ซึ่งไม่มีเดือน – 25 จึงนับย้อนหลังกลับไป จึงเป็นเดือน พฤศจิกายน
- วันที่ ใน Row 6 เป็น 31 แต่เนื่องจากค่าเดือนเป็น 6 คือ มิถุนายน มีแค่ 30 วัน จึงกลายเป็น 1 กรกฎาคม แทน
- วันที่ใน Row 7 เป็น 80 ซึ่งเราไม่มีวันที่ 80 จึงบวกเพิ่มจาก (นับ 31 เป็น 1 กรกฎาคม) จึงได้ผลลัพธ์เป็น 19 สิงหาคม
ก็มีเพียงเท่านี้ ที่เป้นหลักเกี่ยวกับการเพิ่ม ลด วัน เดือน ปี ใน excel สำหรับ ฟังก์ชันอื่น เกี่ยวกับการนับวัน เช่น DateDif โปรดอ่านเรื่อง ฟังก์ชันเกี่ยวกับวันที่ นะจ๊ะ กิ๊ดกิ้ววววววววววว
Discover more from Data Revol
Subscribe to get the latest posts sent to your email.