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

วันนี้ วันนั้น วันไหน? : เรื่องลับๆเกี่ยวกับวันที่ใน Excel 1
รวมคีย์ลัดใน 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%)

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

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

วิธีกรอกวันที่แบบถูกต้องให้ง่ายที่สุด คือ กรอกตามตัวอย่างรูปแบบวันที่ปัจจุบันที่แสดงขึ้นมาจาก =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) ซึ่งจะทำให้วันที่ออกมาเป็น วัน/เดือน/ปี ซึ่งเป็นแบบที่คนไทยถนัดครับผม

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

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

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

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

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

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

ประเด็นที่ 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 หมื่นกว่าๆ แสดงว่าผิดชัวร์

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

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

การจะได้ข้อมูลวันเดือนปีอย่างถูกต้องจากข้อมูลที่เป็นวันที่ สามารถใช้ฟังก์ชัน DATE, MONTH, YEAR มาช่วยได้ง่ายๆ เลยครับ

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

  • วันที่ คือ จำนวนเต็ม
  • เวลา คือ ทศนิยม

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

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

ถ้าเรามีข้อมูลรวมวันที่และเวลาอยู่ในช่องเดียวกัน เราก็แยกวันที่ออกมาด้วยวิธีการหาจำนวนเต็ม เช่น ใช้ INT มาช่วย

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

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

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

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

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

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

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

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

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

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

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

  • โหมด 1 : ให้วันอาทิตย์เป็น 1 และ เสาร์เป็น 7
  • โหมด 2 : ให้วันจันทร์เป็น 1 และ อาทิตย์เป็น 7
วันนี้ วันนั้น วันไหน? : เรื่องลับๆเกี่ยวกับวันที่ใน Excel 13

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

........

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

อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ


รวมคีย์ลัดใน Excel ที่ใช้บ่อย พร้อมเทคนิคการจำ
  • 419
  •  
  •  
  •  
  •  

Posted on: June 27, 2019
Tags: , ,

Leave a Reply

Your email address will not be published. Required fields are marked *