วันนี้ วันนั้น วันไหน? : เรื่องลับๆเกี่ยวกับวันที่ใน Excel

เรื่องวันที่ใน Excel นั้นมีความลึกซึ้งยิ่งนัก หากเราเข้าใจแบบผิวเผิน ก็อาจจะทำงานผิดพลาดโดยไม่รู้ตัวก็ได้ ลองมาดูกันครับ ว่าคุณรู้เรื่องพวกนี้แล้วหรือยัง?

ประเด็นที่ 1 : การกรอกวันที่ปัจจุบัน

วิธีกรอกวันที่ปัจจุบันลงไปใน Excel มีง่ายๆ 2 วิธีด้วยกัน

1. ใช้ฟังก์ชัน =TODAY()
2. กดคีย์ลัด Ctrl+; (ปุ่ม ว)

ซึ่งมันออกมาเป็นวันที่ปัจจุบันทั้งคู่… แล้วทั้ง 2 วิธีต่างกันตรงไหน? หากใช้ผิด ผลที่ได้อาจไม่ใช่อย่างที่คิดนะครับ…

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

แต่ถ้าหากใช้คีย์ลัด Ctrl+; (ปุ่ม ว) มันจะเป็นการ Stamp ค่าวันที่ลงไปใน Cell เลย เหมือนกับการที่เราพิมพ์ลงไปเอง แค่สะดวกขึ้น ดังนั้นวิธีนี้ถึงจะเอาไฟล์ไปเปิดในวันถัดไป ค่าก็จะไม่เปลี่ยนครับ

ประเด็นที่ 2 : วิธีกรอกวันที่ใน Excel

สมมติว่าวันที่เราต้องการกรอกลงไปใน Excel คือวันอังคาร ที่ 25 มิถุนายน พ.ศ. 2562 นะ

คุณจะกรอกวันที่ดังกล่าวลงไปใน Excel ยังไงครับ? (เราใส่วันที่เป็น / หรือ – ได้ทั้งคู่นะ)

ก) 25/6/2562 หรือ 25-6-2562

ข) 25/6/2019 หรือ 25-6-2019

หากใครตอบ ก. ก็ผิดทันทีครับ เพราะวิธีกรอกที่ถูกต้องต้องกรอกเป็น ค.ศ. เสมอ (ถ้าเราไปกรอกเป็น 25/6/2562 Excel จะเข้าใจว่าเป็น ค.ศ. 2562 ซึ่งเป็นวันในอนาคตอีก 543 ปีข้างหน้า ซึ่งวันจันทร์-อาทิตย์ก็ไม่ตรงกับปัจจุบัน แถมบางปีอาจมี 29 กพ. ไม่ตรงกันด้วย (ปี ค.ศ. ที่หาร 4 ลงตัวจะมี 29 กพ. เกือบ 100%)

ส่วนใครตอบว่า ข ก็ยังต้องลุ้นต่อว่าจะถูกหรือผิดอยู่ดี…

วิธีกรอกวันที่แบบถูกต้องให้ง่ายที่สุด คือ ให้กรอกเป็น ปี/เดือน/วัน (ไม่ว่าคอมพ์จะตั้งค่าแบบไหนก็จะรู้จักวันที่รูปแบบนี้)
หรือไม่ก็กรอกตามตัวอย่างรูปแบบวันที่ปัจจุบันที่แสดงขึ้นมาจาก =TODAY() หรือ Ctrl+; นั่นแหละครับ ว่าเป็น วัน/เดือน/ปี หรือ เดือน/วัน/ปี

สิ่งที่ผมจะบอกก็คือ การกรอก 25/6/2019 เครื่องคอมพ์ของคนไทยส่วนใหญ่จะวันที่ออกมาชิดขวาของ Cell … แต่บางเครื่องกลับชิดซ้าย…

ถ้ากรอกแล้ววันที่ออกมาชิดซ้าย Cell แสดงว่าผิดชัวร์ เพราะแสดงว่า Excel ไม่ได้มองว่าข้อมูลนั้นเป็นวันที่แต่มองเป็นข้อความ (หากข้อมูลเป็นวันที่จริง Excel จะตีความเป็นตัวเลข ซึ่งจะชิดขวา)

แล้วทำไมพิมพ์แบบเดียวกันแท้ๆ บางเครื่องได้ บางเครื่องไม่ได้??

ก็เพราะเรื่องวันที่ มันขึ้นอยู่กับการตั้งค่าใน Control Panel ในส่วนของ Regional Setting (รูปลูกโลก) ว่าตั้งค่าเป็น Format ของประเทศอะไร ?

ถ้าตั้งค่าผิดจากที่คิด เช่น ตั้งเป็น English (United States) มันจะเป็น เดือน/วัน/ปี
ซึ่งถ้าเราใส่ 25/6/2019 มันจะนึกว่าเป็นเดือนที่ 25 วันที่ 6 ซึ่งไม่มี… (มันเลยไม่รู้จักไง)

ซึ่งผมขอแนะนำให้ตั้งเป็น Thai (Thailand) ไม่ก็ English (United Kingdom) ซึ่งจะทำให้วันที่ออกมาเป็น วัน/เดือน/ปี ซึ่งเป็นแบบที่คนไทยถนัดครับผม

ประเด็นที่ 3 : กรอกวันที่เป็น ค.ศ. แต่อยากแสดงปีเป็น พ.ศ. ทำไง?

ถ้ากรอกวันที่เป็น ค.ศ. ไปแล้ว เช่น 25/06/2019 แต่อยากให้แสดงออกมาเป็น พ.ศ. มันก็ง่ายมากๆ เลยครับ นั่นคือ ให้ไปปรับที่ Number Format นั่นเอง

พอปรับ Number Format แล้ว รูปแบบที่เรามองเห็นจะเปลี่ยนไป แต่ค่าจริงๆ ยังเหมือนเดิมครับ

ประเด็นที่ 4 : ค่าที่แท้จริงของวันที่ใน Excel

สมมติเรากรอกวันที่ 25/6/2019 ลงไปใน Cell ถูกต้องแล้ว…

สมมติว่าผมอยากได้เลขวัน ว่าเป็นวันที่เท่าไหร่ (ในที่นี้ต้องการเลข 25) หรือ อยากได้ปีว่าเป็นปีอะไร (ต้องการเลข 2019)

คนที่ไม่เข้าใจเรื่องวันที่อาจลองใช้ฟังก์ชัน LEFT(วันที่,2) เพื่อเอาข้อมูล 2 ตัวแรก หรืออาจจะใช้ RIGHT(วันที่,4) เพื่อเอาข้อมูล 4 ตัวท้าย ซึ่งมันผิดนะครับ

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

• ซึ่งจะเริ่มตั้งแต่วันแรกที่ Excel รู้จัก คือ วันที่ 1/1/1900 แทนด้วยเลข 1
• วันถัดมา คือ คือ วันที่ 2/1/1900 แทนด้วยเลข 2
• โดยเลขจะเพิ่มขึ้นเรื่อยๆ วันที่ในยุคปัจจุบัน จะถึงเลข 40000 กว่าๆ แล้ว

ถ้าไม่เชื่อลองปรับ Format ของข้อมูลวันที่เป็น General ดู จะเห็นว่าวันที่ 25/06/2019 เป็นเลข 43641 ซึ่งก็คือ ค่าที่แท้จริงครับ

แต่ถ้าคุณกรอกวันที่เป็น ปี พ.ศ. เช่น 25/06/2562 แล้วปรับเป็น General จะได้เลขออกมา 2 แสนกว่าๆ ซึ่งไม่ใช่ 4 หมื่นกว่าๆ แสดงว่าผิดชัวร์ (2 แสนกว่าคือวันที่ในอนาคต ในอีก 543 ปีข้างหน้า)

และนี่ก็คือสาเหตุว่าทำไม LEFT(วันที่,2) กลับได้ 43 จาก 43641 แทนจะได้ 25 และ RIGHT(วันที่,4) กลับได้ 3641 จาก 43641 แทน 2019

และการที่ Excel เก็บค่าที่แท้จริงของวันที่เป็นตัวเลขธรรมดา มันทำให้เราสามารถส่งไฟล์ Excel ของเราไปให้เพื่อนต่างประเทศ (ที่ตั้งค่า Region ไม่เหมือนกับเรา) แล้วข้อมูลไม่เพี้ยนนั่นเอง (แค่อาจแสดงค่าออกมาคนละ Format แต่มีค่าที่แท้จริงตรงกัน)

ประเด็นที่ 5 : การคำนวณเกี่ยวกับวันที่และเวลา

• วันที่ คือ จำนวนเต็ม
• เวลา คือ ทศนิยม (ที่เป็นสัดส่วนของวัน เช่น 12:00 คือ 0.5 วัน เนื่องจาก 24:00 คือ 1 วัน)

หากต้องการเลื่อนวันที่ไป 7 วัน ก็สามารถเอาค่าวันที่ไป +7 ได้เลย

และหากมีข้อมูลแยกกันคนละช่อง เราสามารถเอามารวมกันง่ายๆ ได้ด้วยการบวก (ไม่ใช่เอามา & เชื่อมกันนะ)

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

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

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

เช่นในรูปข้างบนนี้ เราเห็นผลหน้าตาออกมาเป็น 3 ชั่วโมง แต่หากลองปรับ Format เป็น general จะเห็นว่าค่าที่แท้จริงคือ 0.125 ซึ่งหน่วยเป็นวันต่างหาก

ดังนั้นจะเอาไปคำนวณอะไรต่อก็ระวังเรื่องหน่วยด้วยนะครับ เช่น จะเอาไปคูณค่าใช้จ่ายต่อชั่วโมง ก็ต้องแปลงหน่วยให้ตรงกันซะก่อน

นอกจากนี้แล้ว ก็มีฟังก์ชันเกี่ยวกับวันที่ที่ควรรู้อีกมากมาย ลองดูฟังก์ชันพื้นฐานได้ที่นี่

ประเด็นที่ 6 : วิธีการคำนวณวันของสัปดาห์(วันจันทร์-อาทิตย์)

ปกติแล้วถ้าเรามีข้อมูลวันที่อยู่ แล้วอยากรู้ว่าเป็นวันอะไร (จ-อา) ใน Excel ก็จะมีฟังก์ชันที่ทำหนี้ที่นี้อยู่ นั่นก็คือ WEEKDAY

แต่เจ้า WEEKDAY ออกแบบมาเอาใจคนทั่วโลกที่มีความหลากหลายมากๆ ก็เลยดันมี Option ใน Input ที่ชื่อว่า return_type โดยให้เลือกว่าจะให้วันจันทร์-อาทิตย์ออกมาเป็นตัวเลขรหัสแบบไหน เช่น

• โหมด 1 : ให้วันอาทิตย์เป็น 1 และ เสาร์เป็น 7
• โหมด 2 : ให้วันจันทร์เป็น 1 และ อาทิตย์เป็น 7

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

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

แต่ผมจะขอแนะนำอีกวิธีหนึ่งง่ายๆ นั่นก็คือการเปลี่ยน Custom Number Format เป็น dddd ครับ

พอเราปรับ Number Format เป็น dddd ก็จะแสดงข้อความออกมาเป็นชื่อวันเลย แต่ว่ามันก็เป็นแค่ Format หรือการแสดงผลนะครับ ค่าที่แท้จริงยังคงเป็นตัวเลขธรรมดาๆ อยู่ (สังเกตว่าคำว่าอังคารดันชิดขวา บ่งบอกว่าจริงๆ คือ ตัวเลขนะ)

Tips: เราสามารถเอาค่าดังกล่าวไปบวก 1 แล้วจะได้เป็นคำว่า พุธ ด้วยซ้ำ

ถ้าเราลองใช้ LEFT ดูจะเห็นว่าได้ออกมาเป็นเลข 4 เพราะค่าที่แท้จริง คือ 4 หมื่นกว่าๆ ซึ่งทำให้เกิดปัญหาว่าเราไม่สามารถเช็คข้อมูลได้สะดวกนักว่า Cell นั้นมีค่าตามที่เราต้องการรึเปล่า?

วิธีแก้ไข ถ้าอยากให้ชื่อวันเป็นข้อความจริงๆ ให้ใช้ฟังก์ชัน TEXT แล้วใส่ format เป็น dddd มาช่วยแทนครับ เช่น

แบบนี้จะเห็นว่าคำว่าอังคารจะกลายเป็น Text จริงๆ ไปแล้ว และอยู่ชิดซ้ายด้วย (ถ้า LEFT 1 ตัวอักษร ก็จะได้ อ แล้วครับ)

นอกจาก dddd แล้ว ก็ยังสามารถใส่ได้อีกหลายแบบเช่น

• ddd = ชื่อย่อของวัน
• dddd = ชื่อเต็มของวัน
• mmm = ชื่อย่อขอเดือน
• mmmm = ชื่อเต็มของเดือน

ประเด็นที่ 7 : การกำหนดภาษาของวันที่

ทีนี้ยังมีประเด็นจุกจิกอีกเล็กน้อย นั่นคือ บางเครื่องใส่ dddd แล้วออกมาเป็น “อังคาร” แต่บางเครื่องก็ออกมาเป็น “Tuesday”

ที่เป็นแบบนี้เพราะมันจะยึดตามภาษาที่ตั้งไว้ใน Control Panel ที่ผมบอกตอนแรกนั่นแหละครับ เครื่องผมตั้งค่าเป็น Thai (Thailand) ก็เลยออกมาเป็น “อังคาร” ซึ่งถ้าเอาไฟล์ไปเปิดในเครื่องคนอื่นก็อาจขึ้นเป็นภาษาอื่น ทำให้ไม่มีมาตรฐาน ซึ่งอาจยากต่อการเขียนสูตรอีกแล้ว

ทางแก้ไขคือ จะต้องมีการระบุภาษาให้เจาะจงลงไปใน code ของ Custom Number Format เลย เช่น จะเอาภาษาอังกฤษนะ ไม่ว่าจะเปิดในเครื่องไหน ก็จะเป็นภาษาอังกฤษเหมือนกันหมดครับ

วิธีในการดู Code คือ ให้ไปเลือก Format วันที่มาซักอันนึงทีมีข้อความ เช่นอันนี้ผมเลือกจาก Locale ภาษาอังกฤษ (English UK)

จากนั้นค่อยไปกดที่ Custom คราวนี้เราจะเห็น Code รหัสภาษาออกมาด้วย

คราวนี้ถ้าผมแก้เป็น [$-en-GB] นำหน้า ก็จะได้เป็น Tuesday แล้วครับ

ลองไปทำภาษาไทยดูก็จะเป็นแบบนี้ [$-th-TH,107] นำหน้า

ประเด็นที่ 8 : ถ้าข้อมูลไม่ใช่วันที่แบบถูกต้อง จะแก้ยังไง?

แทนที่จะได้ข้อมูลวันที่มาเป็นวันที่จริงๆ (ที่มีค่าที่แท้จริงเป็นเลข 4 หมื่นกว่าๆ) กลับได้ข้อมูลมาผิดๆ เลย เช่น มาเป็นตัวเลข หรือ ข้อความ เราจะทำไงดี?

ผมแนะนำว่าโดยทั่วไปคือ ให้แยกข้อมูลออกมาทีละส่วนเป็น ปี เดือน วัน ด้วย LEFT MID RIGHT ตามความเหมาะสม (คราวนี้ทำได้เพราะข้อมูลเป็นเลขหรือข้อความธรรรมดา) จากนั้นค่อยเอามารวมร่างกันด้วยฟังก์ชัน DATE ครับ

นอกจากนี้ อาจใช้เทคนิคเปลี่ยน Custom Number Format ให้ใส่ขีดคั่น แล้วบังคับแปลงเป็นตัวเลขด้วยการคูณ 1 แล้วค่อยเปลี่ยน Format เป็นวันที่ แบบนี้ก็ได้ครับ

สรุป

เรื่องวันที่เป้นเรื่องที่ควรทำให้ถูกแต่แรก ไม่งั้นจะปวดหัวตอนแก้ไข โดยเฉพาะถ้าต้องรวบรวมไฟล์จากคนหลายคน แล้วแต่ละคนดันกรอกวันที่ถูกบ้างผิดบ้าง…

ถ้าเจอปัญหาที่วันที่ปนกันหลายรูปแบบลองทำแบบนี้ดูครับ

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