11 วิธีปรับรูปแบบ Format ของวันที่

Back to: Excel Expert Fast and Easy

1. ในการบันทึก ให้พิมพ์ให้ครบทั้งวันเดือนปี อย่าบันทึกเฉพาะวันที่หรือเฉพาะเดือนหรือเฉพาะปีเท่านั้น เช่น 14/2/2020 โดยจะพิมพ์ส่วนของเวลาต่อท้ายด้วยหรือไม่ก็ได้ เช่น 14/2/2020 12:00
2. ให้บันทึกโครงสร้างของวันที่ลงไปในเซลล์ตามแบบที่กำหนดไว้ใน Regional Setting ของ Windows เช่น ถ้ากำหนดไว้ใน Regional Setting เป็น Thai ก็ต้องบันทึกวันที่ตามแบบของประเทศไทยซึ่งใช้ลำดับตามวันก่อนเดือนก่อนปี แต่ถ้ากำหนดใน Regional Setting เป็น USA ก็ต้องบันทึกตามลำดับเดือนก่อนวันก่อนปี
3. แม้จะใช้ Regional Setting เป็น Thai แต่ในการบันทึกในส่วนของเลขปีต้องใช้ปีค.ศ.ในการบันทึกเท่านั้น จากนั้นให้ใช้ Format ปรับการแสดงปีค.ศ.ให้เป็นปีพ.ศ.ในภายหลัง เช่น ให้บันทึก 14/2/2020 (ห้ามใช้ปีพ.ศ.แทนอย่างเด็ดขาด) จากนั้นเมื่อต้องการแสดงเป็นปีไทย 14/2/2563 ให้ใช้ Format [$-1070000]d/mm/yyyy ซึ่งใน Excel รุ่นใหม่อาจมีรหัสประเทศในวงเล็บ [] ต่างไป รหัสแบบเดิมยังใช้งานได้ใน Excel ทุกรุ่น
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/2020 12:00:00 มีค่า SN เท่ากับ 43875.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/2020 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 รุ่นใหม่จะมีรหัสใหม่ต่างไป แต่รหัสเดิมยังใช้งานได้

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

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

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

ที่น่าห่วงมาก ทั้ง 4/2/2020 และ 2/4/2020 ล้วนชิดข้างขวาของเซลล์เหมือนกันเพราะมีค่าเป็นตัวเลข แต่อาจไม่ใช่วันที่ตามต้องการ

หากคุณอยากจะเก่งสูตรคำนวณเรื่องวันที่และเวลา ต้องรู้จักสูตรหา 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 เดือนกุมภาพันธ์ ปีค.ศ. 2020 เวลา 12 นาฬิกา 30 นาที 45 วินาที

1. =NOW() จะได้วันเดือนปีและเวลาปัจจุบัน เช่น 14/2/2020 12:30:45
2. =Today() จะได้เฉพาะวันเดือนปีปัจจุบัน เช่น 14/2/2020
3. =Date(2020,2,14) จะได้ 14/2/2020
4. =Date( Year(Today()), Month(Today())+1, 0) จะได้วันเดือนปีของวันสุดท้ายของเดือนปัจจุบัน คือ 29/2/2020
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/2020 12:30:45 (ถ้าต้องการแสดงค่าออกมาเป็น SN โดยการเปลี่ยน Format เป็น General จะพบว่า เซลล์ A1 มีค่า SN เท่ากับ 43875.5213541667 ซึ่งเป็นตัวเลขที่ Excel รู้จักแต่เราไม่รู้จัก)

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