Prompt ใช้ Copilot ตรวจสอบวันที่ พ.ศ. หรือ ค.ศ. กันแน่ตรวจสอบคอลัมน์วันที่ทั้งหมดในตารางนี้
เป้าหมาย:
ทำให้ข้อมูลวันที่มีมาตรฐานเดียวกัน ถูกต้องตามความหมายเดิม และพร้อมใช้งานใน Excel
ขั้นตอนการทำงาน
1. ตรวจสอบก่อนว่าคอลัมน์เป็น
– Text
– หรือ Date Serial Number
2. ถ้าข้อมูลเป็น Text
2.1 ตรวจสอบรูปแบบวันที่ของระบบ
– ให้ใช้สูตร =TODAY()
– ต้องตรวจในเซลล์ว่างที่ไม่เคยถูกใช้งานหรือถูกกำหนด Format มาก่อน
– ใช้รูปแบบที่ TODAY() แสดงเป็นมาตรฐาน
2.2 วิเคราะห์รูปแบบวันที่ของข้อมูลทั้งคอลัมน์
– ห้ามดูเพียงเซลล์เดียว
– ใช้ค่าที่มากกว่า 12 เพื่อระบุว่าอะไรคือวันและอะไรคือเดือน
– ถ้าบางเซลล์กำกวม ให้ยึดตามรูปแบบที่พบมากที่สุดในคอลัมน์
2.3 ถ้าพบชื่อเดือน
เช่น
Jan
January
Feb
ม.ค.
มกราคม
ให้แปลงเป็นเลขเดือนทั้งหมด
2.4 ถ้าพบปี 2 หลัก
เช่น
67
66
68
ให้ขยายเป็นปี 4 หลักก่อน
2.5 ถ้าพบปี พ.ศ. 24xx หรือ 25xx
ให้แปลงเป็น ค.ศ.
โดยลบ 543
ตัวอย่าง
2567 → 2024
2566 → 2023
2.6 ปรับรูปแบบวันที่ทั้งหมดให้ตรงกับรูปแบบของ TODAY()
ตัวอย่าง
ถ้า TODAY() แสดง
21/08/2026
ให้จัดข้อมูลทั้งหมดเป็น
dd/mm/yyyy
2.7 ยังคงสถานะข้อมูลเป็น Text ไว้ก่อน
ห้ามแปลงเป็น Date ทันที
3. แสดงตัวอย่างผลลัพธ์หลังการทำความสะอาด
4. ถามผู้ใช้ก่อน
ต้องการแปลงเป็น Excel Date Serial Number หรือไม่
5. ถ้าผู้ใช้ยืนยันให้แปลงเป็น Date
ให้ใช้วิธีต่อไปนี้
– แยก Year
– แยก Month
– แยก Day
แล้วใช้
DATE(Year,Month,Day)
สร้าง Date ใหม่
ห้ามใช้ DATEVALUE()
6. หลังแปลงเป็น Date แล้ว
ให้กำหนด Format เป็น
dd/mm/yyyy
เพื่อให้เห็นปี ค.ศ. 4 หลักอย่างชัดเจน
7. ถ้าข้อมูลเป็น Date Serial Number อยู่แล้ว
7.1 เปลี่ยน Format เป็น
dd/mm/yyyy
ก่อนเสมอ
เพื่อดูปีจริงที่เก็บอยู่
7.2 ตรวจหาค่าวันที่ที่น่าสงสัย
เช่น
1967
1968
1969
2067
2068
2069
ซึ่งอาจเกิดจากการกรอกปี 2 หลักแล้ว Excel ตีความผิด
7.3 ห้ามแก้ไขข้อมูลเดิมทันที
ให้สร้างคอลัมน์ใหม่ชื่อ
Suggested Date
และใช้สูตร DATE() สร้างค่าที่เสนอแทน
เพื่อให้ผู้ใช้ตรวจสอบก่อน
8. ถ้าพบว่า Date Serial Number ตีความวันและเดือนสลับกัน
ห้ามแก้ด้วยการเปลี่ยน Format
ให้สร้าง Date ใหม่ด้วย DATE()
ตัวอย่าง
=DATE(YEAR(A2),DAY(A2),MONTH(A2))
เพื่อสลับวันและเดือนกลับมาอย่างถูกต้อง
9. ห้ามใช้วิธีต่อไปนี้กับวันที่ที่เป็น Text
*1
+0
VALUE()
หรือวิธีใด ๆ ที่ปล่อยให้ Excel เดาวันที่จาก Regional Setting
เพราะอาจเกิดการสลับวันและเดือนโดยไม่รู้ตัว
10. แสดงสรุปผลการตรวจสอบ
– จำนวนแถวที่เป็น Text
– จำนวนแถวที่เป็น Date
– จำนวนแถวที่พบปี พ.ศ.
– จำนวนแถวที่พบปี 2 หลัก
– จำนวนแถวที่น่าสงสัย
– จำนวนแถวที่ Copilot แนะนำให้ตรวจสอบเพิ่มเติม
หมายเหตุ
ถ้าพบปี พ.ศ. ก่อน 2484
ให้แจ้งเตือนผู้ใช้ว่าประเทศไทยเคยถือวันที่ 1 เมษายนเป็นวันขึ้นปีใหม่
ดังนั้นข้อมูลช่วงเดือนมกราคมถึงมีนาคมของบางปีอาจต้องใช้ผลต่าง 542 ปีแทน 543 ปี
อย่าแปลงอัตโนมัติในกรณีนี้