วิธีใช้วันที่และเวลา

June 23, 2021
Category: เรื่องทั่วไป

เชิญชมวิดีโอ 10 ตอนในหลักสูตร “เตรียมพร้อมเครื่อง เตรียมพร้อม Excel”

สมัครเรียนหลักสูตรนี้ ฟรี ได้ที่ https://www.excelexperttraining.com/online/courses/01-excel-ready/

  1. ในการบันทึก ให้พิมพ์ให้ครบทั้งวันเดือนปี อย่าบันทึกเฉพาะวันที่หรือเฉพาะเดือนหรือเฉพาะปีเท่านั้น โดยจะพิมพ์ส่วนของเวลาต่อท้ายด้วยหรือไม่ก็ได้ เช่น 14/2/2021 12:00
  2. ให้บันทึกโครงสร้างของวันที่ลงไปในเซลล์ตามแบบที่กำหนดไว้ใน Regional Setting ของ Windows เช่น ถ้ากำหนดไว้ใน Regional Setting เป็น Thai ก็ต้องบันทึกวันที่ตามแบบของประเทศไทยซึ่งใช้ลำดับตามวันก่อนเดือนก่อนปี แต่ถ้ากำหนดใน Regional Setting เป็น USA ก็ต้องบันทึกตามลำดับเดือนก่อนวันก่อนปี
  3. แม้จะใช้ Regional Setting เป็น Thai แต่ในการบันทึกในส่วนของเลขปีต้องใช้ปีค.ศ.ในการบันทึกเท่านั้น จากนั้นให้ใช้ Format ปรับการแสดงปีค.ศ.ให้เป็นปีพ.ศ.ในภายหลัง เช่น ให้บันทึก 14/2/2021 (ห้ามใช้ปีพ.ศ.แทนอย่างเด็ดขาด) จากนั้นเมื่อต้องการแสดงเป็น 14/2/2564 ให้ใช้ Format [$-1070000]d/m/yyyy หรือถ้าใช้ Excel 365 ใช้ Format [$-,107]d/m/yyyy
  4. ในโครงสร้างของวันที่ให้พิมพ์เครื่องหมาย / ในการแบ่งส่วนของ วัน/เดือน/ปีค.ศ. และในโครงสร้างของเวลาให้พิมพ์เครื่องหมาย : ในการแบ่งส่วนของ ชั่วโมง:นาที หรือ ชั่วโมง:นาที:วินาที
  5. ควรพิมพ์เลขปีค.ศ.ให้ครบทั้ง 4 หลักเสมอ แต่ถ้าจำเป็นต้องพิมพ์แค่สองหลักท้าย พอกดปุ่ม Enter เพื่อบันทึกค่าลงไป Excel จะเปลี่ยนตัวเลขปี 2 หลักท้าย ดังนี้
    • ตั้งแต่เลข 00-29 ให้เป็นช่วงปีค.ศ. 2000-2029
    • ตั้งแต่เลข 30-99 ให้ย้อนกลับเป็นช่วงปีค.ศ. 1930-1999
  6. เมื่อบันทึกวันที่แล้วต้องชิดขวาของเซลล์เสมอ (สาเหตุที่ชิดขวาเพราะมีค่าเป็นตัวเลข) และขอให้หลีกเลี่ยงการใช้วันที่แบบ Text ซึ่งชิดซ้ายของเซลล์เนื่องจาก Excel จะไม่นำ Regional Setting มาช่วยควบคุมในการแบ่งส่วนของวันเดือนปีที่ใช้แบบ Text
  7. ค่าของวันที่และเวลามีชื่อเรียกว่า Date Serial Number หรือเรียกว่า Serial Number (SN) โดย Excel ถือว่า 1/1/1900 0:00:00 มีค่า SN=1 ซึ่งเราสามารถแกะดูค่า SN ได้โดยเปลี่ยน Format เป็น General (โดยการกดปุ่ม Ctrl+Shift+ ~) เช่น 14/2/2021 12:00:00 มีค่า SN เท่ากับ 44241.5
    • ในส่วนของตัวเลข SN ส่วนที่เป็นจำนวนเต็ม คือ ค่าของวันที่
    • ในส่วนของตัวเลข SN ส่วนที่เป็นเศษทศนิยม คือ ค่าของเวลา
  8. เวลาของ Excel เริ่มจาก 0:00:00-23:59:59 และใช้ Format h:mm:ss
  9. ระยะเวลา เริ่มจาก 0:00:00 ขึ้นไป โดยไม่สิ้นสุดที่ 23:59:59
    • ใช้ Format [h]:mm:ss เพื่อแสดงเลขตั้งแต่ 24 ชั่วโมงขึ้นไปได้
    • ใช้ Format [mm]:ss เพื่อแสดงเลขตั้งแต่ 60 นาทีขึ้นไปได้
    • ใช้ Format [ss] เพื่อแสดงเลขตั้งแต่ 60 วินาทีขึ้นไปได้
  10. ในการแสดงเวลาหรือระยะเวลา ถ้าค่า SN < 1 จะใช้ Format ของเวลาหรือระยะเวลาก็ได้ แต่ถ้า SN >=1 ต้องเลือกใช้ Format ของระยะเวลาที่ใช้เครื่องหมาย [ ] เท่านั้น
  11. ถ้าต้องการบันทึกการทำงานข้ามคืน ไม่ควรบันทึกเฉพาะเวลาเข้าออกงาน แต่ให้บันทึกวันเดือนปีกำกับเวลาไว้ด้วย เช่น 14/2/2021 20:00 เพื่อทำให้ Excel รับรู้ค่าเป็น SN ที่มีครบทั้งส่วนของวันที่และเวลา
  12. Format ในการแสดงวันและเวลา
    • แสดงเลขวันที่ ใช้ d หรือ dd
    • แสดงเป็นชื่อวัน แบบย่อใช้ ddd หรือแบบเต็ม dddd
    • แสดงเป็นเลขเดือน ใช้ m หรือ mm
    • แสดงเป็นชื่อเดือน แบบย่อใช้ mmm หรือแบบเต็ม mmmm
    • แสดงเป็นเลขปี ใช้ yy หรือ yyyy
    • แสดงชั่วโมง นาที วินาที ใช้ h:mm:ss หรือ hh:mm:ss
    • รหัสควบคุม Locale ใช้ [$-409] สำหรับ USA และ [$-1070000] สำหรับไทย ซึ่งรหัสควบคุมนี้ใน Excel รุ่นใหม่จะต่างไปบ้าง

ถ้าใช้แฟ้มงานร่วมกัน ทางที่ดีที่สุดไม่ควรใช้ปีพ.ศ.เลยไม่ว่าจะใช้วิธีที่ถูกหรือผิด เพราะคนที่ใช้ Excel ไม่เก่งอาจลอกปีพ.ศ.ตามที่เห็นไปกรอกที่อื่นผิดต่อไปอีก ข้อมูลที่ใช้ภายในควรใช้ปีค.ศ.เสมอ เว้นแต่จดหมายที่ส่งให้ลูกค้า

การคำนวณวันที่และเวลา

ก่อนที่จะนำวันที่และเวลามาคำนวณได้ ต้องเริ่มจากการตรวจสอบว่าข้อมูลเกี่ยวข้องกับวันที่และเวลาซึ่งถูกบันทึกไว้ว่ามีโครงสร้างที่ถูกต้องตรงกับ Regional Setting หรือไม่ หากบันทึกไว้ผิด เช่น ใน Regional Setting กำหนดไว้เป็น USA แต่คนที่บันทึกข้อมูลพิมพ์วันที่ไว้ในแบบประเทศไทย เช่น พิมพ์ 4/2/2021 ซึ่งต้องการบันทึกแบบไทยให้หมายถึงวันที่ 4 เดือน 2 ปี 2021 จะพบว่าเมื่อนำแฟ้มนั้นมาเปิดบนเครื่องที่มี Regional Setting เป็นไทย เลขที่ของวันจะสลับกับเลขที่ของเดือน จะแสดงเป็น 2/4/2021 แทน ทำให้กำหนดเวลาที่บันทึกไว้ผิดทั้งหมด ถ้าไม่เก่งสูตรเกี่ยวข้องกับวันที่และเวลาก็ต้องจัดการพิมพ์ทับใหม่ทั้งหมด

นอกจากนี้ต้องตรวจสอบต่อไปอีกว่า ค่าของวันที่ซึ่งบันทึกไว้เป็นข้อมูลที่มีค่าเป็นตัวเลข (Date Serial Number หรือ SN) หรือไม่ โดยเริ่มจากยกเลิกการจัดชิดซ้ายชิดขวาของเซลล์วันที่ทั้งหมด หากพบว่า ชิดขวาก็ใช้ได้ แต่ถ้าพบว่าชิดซ้ายแสดงว่ามีค่าเป็น Text ซึ่งไม่สามารถนำมาคำนวณต่อได้ในทันที จำเป็นต้องอาศัยสูตร Left, Right, Mid แยกตัวเลขแต่ละส่วนที่เป็นวันเดือนปีออกจากกันแล้วใช้สูตรวันที่และเวลามาช่วยแก้ไขให้มีค่าเป็น SN

หากคุณอยากจะเก่งสูตรคำนวณเรื่องวันที่และเวลา ต้องรู้จักสูตรหา SN หรือสูตรที่สามารถแปลงเลขที่ของวันเดือนปีที่มนุษย์เข้าใจไปเป็นค่า SN ที่ Excel รู้จัก เช่น สูตร Now(), Today(), Date(Year,Month,Day), Time(Hour,Minute,Second)

จากนั้นต้องสามารถใช้สูตรแปลงค่า SN กลับมาเป็นเลขที่ของวันเดือนปี เช่น สูตร Day(SN), Month(SN), Year(SN), และ WeekDay(SN) หรือหาเลขที่ของเวลา เช่น Hour(SN), Minute(SN), Second(SN)

สูตรแปลงเลขที่ของวันเดือนปีและเวลาที่มนุษย์เข้าใจ
ไปเป็นค่า Serial Number

เพื่อช่วยให้เข้าใจสูตรได้ง่ายขึ้น ขอสมมติว่าปัจจุบัน คือ วันที่ 14 เดือนกุมภาพันธ์ ปีค.ศ. 2021 เวลา 12 นาฬิกา 30 นาที 45 วินาที

  1. =NOW() จะได้วันเดือนปีและเวลาปัจจุบัน เช่น 14/2/2021 12:30:45
  2. =Today() จะได้เฉพาะวันเดือนปีปัจจุบัน เช่น 14/2/2021
  3. =Date(2021,2,14) จะได้ 14/2/2021
  4. =Date( Year(Today()), Month(Today())+1, 0) จะได้วันเดือนปีของวันสุดท้ายของเดือนปัจจุบัน คือ 28/2/2021 หรือถ้าใช้ Excel 2010 เป็นต้นมามีสูตร =EOMonth(Today(),0) โดยเลข 0 สามารถเปลี่ยนเป็นเลขบวกลบตามเดือนถัดไปหรือก่อนหน้า
  5. =Time(12,30,45) จะได้เวลา 12:30:45
  6. =Time(12,30,45)+1 จะได้ระยะเวลา 36:30:45 ซึ่งต้องใช้ Format [h]:mm:ss ด้วย

สูตรแปลงค่า Serial Number
กลับมาเป็นเลขที่ของวันเดือนปีและเวลาที่มนุษย์เข้าใจ

สมมติว่าเซลล์ A1 มีสูตร =NOW() ซึ่งแสดงออกมาเป็น 14/2/2021 12:30:45 (ถ้าต้องการแสดงค่าออกมาเป็น SN โดยการเปลี่ยน Format เป็น General จะพบว่า เซลล์ A1 มีค่า SN เท่ากับ 44241.5213541667 ซึ่งเป็นตัวเลขที่ Excel รู้จักแต่เราไม่รู้จัก)

  1. =Day(A1) จะได้เลขวันที่ 14
  2. =Month(A1) จะได้เลขเดือน 2
  3. =Year(A1) จะได้เลขปี 2021
  4. =WeekDay(A1) จะได้เลขของวันในสัปดาห์ 1=Sunday, 2=Tuesday,…7=Saturday
  5. =Hour(A1) จะได้เลขชั่วโมง 12
  6. =Minute(A1) จะได้เลขนาที 30
  7. =Second(A1) จะได้เลขวินาที 45