Excel Serial Date Converter: คู่มือแปลง Serial Number เป็นวันที่ใน Excel อย่างถูกต้อง

หากคุณเคยส่งออกรายงานจาก Excel เป็นไฟล์ CSV แล้วเปิดด้วยโปรแกรมอื่น คุณอาจเคยเจอสิ่งประหลาดที่คอลัมน์วันที่ที่เคยเรียบร้อยกลับกลายเป็นแถวตัวเลขอย่าง 43831, 43832 หรือ 43840 ตัวเลขเหล่านี้ไม่ใช่ข้อมูลเสียหาย แต่คือ Excel serial date ซึ่งเป็นรูปแบบภายในที่ Excel ใช้เก็บวันที่ทุกวันในรูปของการนับจำนวนวัน และด้วย Excel Serial Date Converter ฟรีจาก Online Tools Forge คุณสามารถแปลงตัวเลขกลุ่มนี้กลับเป็นวันที่ที่อ่านเข้าใจได้ภายในคลิกเดียว รวมถึงแปลงย้อนทางจากวันที่จริงกลับไปเป็น serial number ได้เช่นกัน

Converter ตัวนี้รองรับ date system ทั้งสองแบบของ Excel ได้แก่ 1900 date system ซึ่งเป็นค่าเริ่มต้นบน Windows และ 1904 date system ซึ่งเป็นค่าเริ่มต้นบน Excel บน Mac รุ่นเก่า นอกจากนี้ยังจัดการกับบั๊กที่มีชื่อเสียงที่สุดในประวัติศาสตร์วงการสเปรดชีตอย่าง 1900 leap year bug ที่ทำให้ serial number 60 ถูกตีความว่าคือวันที่ 1900-02-29 ซึ่งไม่มีอยู่จริงในปฏิทิน สคริปต์แปลงวันที่ทั่วไปจำนวนมากมองข้ามจุดนี้และให้ผลลัพธ์คลาดเคลื่อนไปหนึ่งวันสำหรับวันที่ช่วงต้นปี 1900 แต่เครื่องมือนี้คำนวณได้ถูกต้อง

ในบทความนี้เราจะพาคุณไปทำความเข้าใจว่า serial date ทำงานอย่างไร เหตุใดจึงมี date system อยู่สองแบบ วิธีใช้งาน converter ทีละขั้นตอน และการนำไปใช้กับงานจริง เช่น การเก็บกวาดไฟล์ CSV การย้ายไฟล์ระหว่าง Windows กับ Mac และการดีบั๊กสูตรคำนวณวันที่ในสเปรดชีต

ทำไมต้องใช้ Excel Serial Date Converter?

• ได้คำตอบทันทีโดยไม่ต้องพึ่งสูตร ไม่ต้องจำสูตร DATEVALUE หรือเทคนิค TEXT ใด ๆ เพียงวาง serial number ลงไปแล้วอ่านวันที่ที่ปรากฏ
• จัดการ leap year bug ได้ถูกต้อง serial 60 จะถูกตีความเป็นวันที่สมมติ 1900-02-29 ไม่ใช่ปล่อยให้วันที่หลังกลางเดือนกุมภาพันธ์ 1900 เพี้ยนไปหนึ่งวันโดยไม่รู้ตัว
• รองรับทั้งสอง date system serial เดียวกันให้วันที่ต่างกันในระบบ 1900 และ 1904 ตัวเลือก date system ช่วยให้ตีความตรงกับไฟล์ที่คุณมี
• แปลงได้สองทาง ถอดรหัส serial เป็นวันที่จากไฟล์ export หรือแปลงวันที่เป็น serial เพื่อสร้างชุดข้อมูลทดสอบและตรวจสอบสูตรได้
• ใช้ได้กับ Google Sheets Sheets เก็บวันที่ด้วยระบบ serial แบบเดียวกับ Excel จึงใช้ตัวเลขและกฎเดียวกันได้ทันที
• เป็นส่วนตัวและรวดเร็ว ทุกการคำนวณทำงานในเบราว์เซอร์ของคุณ ไม่มีการอัปโหลด ไม่ต้องสมัครสมาชิก และไม่ต้องเปิดโปรแกรมสเปรดชีต

คุณสมบัติหลัก

รายละเอียดที่ควรรู้เพิ่มเติม:

• serial 43831 เท่ากับวันที่ 2020-01-01 ในระบบ 1900 เป็นตัวเลขที่ควรจำไว้ใช้ตรวจสอบความถูกต้องได้ง่าย
• serial ที่มีทศนิยมคือเวลา เช่น 43831.5 คือเที่ยงวันของวันนั้น เพราะ Excel เก็บเวลาเป็นเศษส่วนของวัน 24 ชั่วโมง
• สองระบบต่างกัน 1,462 วัน การเลือกระบบผิดจึงทำให้วันที่ที่ถอดรหัสได้เลื่อนไปราวสี่ปีทั้งชุด

วิธีใช้งาน Excel Serial Date Converter

1. เปิด Excel Serial Date Converter ในเบราว์เซอร์ของคุณ
2. เลือก date system ให้ตรงกับไฟล์ต้นทาง ใช้ 1900 สำหรับ Excel บน Windows, Google Sheets และไฟล์ยุคใหม่ส่วนใหญ่ และใช้ 1904 สำหรับไฟล์ Mac รุ่นเก่า
3. วาง serial number ที่ต้องการถอดรหัสลงไป เช่น 43831 แล้ววันที่จริงที่ตรงกันจะปรากฏขึ้นทันที
4. หากต้องการแปลงทางกลับ ให้ป้อนวันที่ เช่น 2020-01-01 แล้วอ่านค่า serial number ที่ Excel เก็บไว้ภายใน
5. คัดลอกผลลัพธ์ไปใช้ในสคริปต์ ไฟล์ import หรือสเปรดชีตของคุณ แล้วทำซ้ำกับค่าถัด ๆ ไปตามต้องการ

ทำความเข้าใจ Date System ของ Excel

สิ่งที่คุณเห็นบนหน้าจอไม่ใช่สิ่งที่ Excel เก็บจริง เมื่อคุณพิมพ์วันที่ลงในเซลล์ Excel จะบันทึกมันเป็นตัวเลขธรรมดาที่เรียกว่า serial date แล้วใช้รูปแบบการแสดงผล (format) ในการวาดตัวเลขนั้นกลับมาเป็นวันที่อีกครั้ง ในระบบ 1900 serial 1 คือวันที่ 1900-01-01 serial 2 คือ 1900-01-02 ต่อ ๆ กันไปวันละหนึ่งหน่วย

จุดที่น่าสนใจคือ epoch จริง หรือวันที่ตรงกับ serial 0 คือวันที่ 1899-12-30 ไม่ใช่ 1899-12-31 สาเหตุมาจากบั๊กที่เก่าแก่กว่าอายุของวิศวกรหลายคนที่กำลังดีบั๊กมันอยู่ Lotus 1-2-3 โปรแกรมสเปรดชีตที่ Excel ต้องเอาชนะในยุคทศวรรษ 1980 ถือว่าปี 1900 เป็น leap year ทั้งที่ความจริงแล้วไม่ใช่ เพราะปีที่หารด้วย 100 ลงตัวจะไม่ใช่ leap year เว้นแต่หารด้วย 400 ลงตัว ฝั่ง Microsoft เลือกที่จะสืบทอดบั๊กนี้มาแบบเหมือนกันทุกประการเพื่อให้ไฟล์ Lotus เก่าคำนวณให้ผลเหมือนเดิม ผลลัพธ์คือ serial 1 ถึง 59 แมปกับวันที่ 1900-01-01 ถึง 1900-02-28 serial 60 ถูกจองไว้ให้วันที่สมมติ 1900-02-29 และ serial ตั้งแต่ 61 เป็นต้นไปเดินต่อราวกับว่าวันพิเศษนั้นมีอยู่จริง เพราะ Excel และโปรแกรมรุ่นต่อยอดทำผิดพร้อมกันเหมือนกัน เลขคณิตของวันที่จึงยังสอดคล้องกันภายใน และสำหรับทุกวันที่หลังเดือนกุมภาพันธ์ 1900 serial ก็คือจำนวนวันนับจาก 1899-12-30 นั่นเอง

ส่วน 1904 date system เกิดขึ้นด้วยเหตุผลทางประวัติศาสตร์อีกแบบหนึ่ง Excel บน Mac ยุคแรกเริ่มนับจากวันที่ 1904-01-01 ซึ่งช่วยหลบปัญหา leap year ได้พอดี เพราะปี 1904 เป็น leap year จริง ๆ ทั้งสองระบบห่างกัน 1,462 วัน หรือสี่ปีกับหนึ่งวันพอดี serial 43831 คือ 2020-01-01 ในระบบ 1900 แต่กลายเป็น 2024-01-02 ในระบบ 1904 นี่คือเหตุผลที่ไฟล์ที่เดินทางข้ามแพลตฟอร์มบางครั้งแสดงวันที่เพี้ยนไปราวสี่ปีทั้งไฟล์ และเป็นเหตุผลว่าทำไมการยืนยันระบบก่อนถอดรหัสจึงสำคัญมาก

Google Sheets ทำให้เรื่องง่ายขึ้น เพราะใช้ระบบ serial แบบเดียวกับ 1900 system ของ Excel รวมถึง epoch 1899-12-30 ด้วย serial จึงย้ายไปมาระหว่าง Sheets กับ Excel ได้โดยไม่เปลี่ยนค่า และ converter ตัวเดียวใช้ได้กับทั้งคู่ ส่วนเวลาจะแถมมาเป็นเศษทศนิยม เช่น 0.5 คือเที่ยงวัน และ 0.25 คือหกโมงเช้า

ตัวอย่างการใช้งานจริง

เก็บกวาดไฟล์ CSV ที่วันที่กลายเป็นตัวเลข

วันที่มาเป็น serial ดิบ ๆ บ่อยครั้งเกิดขึ้นเมื่อคอลัมน์ถูกตั้ง format เป็น General หรือเครื่องมือ query ดึงค่าพื้นฐานออกมาตรง ๆ แทนที่จะจัด format ใหม่ใน Excel แล้วหวังว่า export รอบหน้าจะคงรูป ให้วาง serial บางส่วนลง converter ยืนยันวันที่จริง แล้วแก้คอลัมน์ที่ต้นทางเสียเลย การสุ่มตรวจด้วยคู่ค่าที่รู้จัก เช่น 43831 ต้องได้ 2020-01-01 จะบอกคุณได้ทันทีว่าทั้งคอลัมน์เพี้ยนหรือไม่

ย้ายข้อมูลระหว่างไฟล์ Windows และ Mac รุ่นเก่า

ไฟล์บัญชีเก่า ๆ หรืองานที่สร้างบน Excel บน Mac ก่อนปี 2016 มักใช้ระบบ 1904 ถ้าถอดรหัส serial เดิมด้วยสมมติฐานผิด ทุกวันที่จะหลุดไปราวสี่ปีจากความจริง ลองแปลงค่าที่คุณจำได้หนึ่งค่าด้วยแต่ละระบบในเครื่องมือ ดูว่าคำตอบไหนตรงกับเอกสาร แล้วค่อยประมวลผลข้อมูลที่เหลือด้วยตัวเลือกที่ถูกต้อง ออฟเซ็ต 1,462 วันจะกลายเป็นแค่การติ๊กเลือกเมนู ไม่ใช่หายนะของข้อมูล

ดีบั๊กสูตรคำนวณวันที่

สูตรหาผลต่างของวันที่ รายงานอายุหนี้ หรือการคำนวณกำหนดส่ง ล้วนทำงานบน serial อยู่เบื้องหลัง แม้เซลล์จะแสดงวันที่สวย ๆ ก็ตาม เมื่อการลบให้ผลลัพธ์ 1,462 แทนที่จะเป็น 0 หรือกำหนดส่งดันไปโผล่ปี 1904 สาเหตุเกือบทั้งหมดคือ date system ปนกันหรือ serial ดิบหลุดเข้าไปในการเปรียบเทียบ ลองแปลงค่าที่น่าสงสัย ยืนยันว่าแต่ละฝั่งของสูตรถือค่าอะไรกันแน่ แล้วบั๊กมักเผยตัวภายในไม่กี่วินาที

งาน Data Engineering และ Pipeline

งาน ETL ที่รับไฟล์จาก Excel หรือ Sheets ต้องแปลง serial ให้เป็นชนิดข้อมูลวันที่ที่ถูกต้องก่อนโหลดลงคลังข้อมูลเสมอ converter ใช้เป็นเครื่องมือตรวจสอบระหว่างที่คุณเขียน logic นี้ได้ดี คือถอดรหัส serial ตัวอย่าง ยืนยันว่าการแปลงของคุณให้ผลตรงกันทั้งสอง date system และบันทึก edge case เรื่อง leap year อย่าง serial 60 และ 61 เป็นชุดทดสอบประจำของ pipeline ไปเลย

Best Practices สำหรับการทำงานกับ Serial Date

• ตรวจสอบ date system ของไฟล์ทุกครั้ง ก่อนแปลงข้อมูลจำนวนมาก ความต่างของตัวเลือกเดียวเท่ากับวันที่เลื่อนสี่ปีทั้งชุดข้อมูล
• ระวัง serial 60 มันไม่ใช่วันที่จริง ให้ถือเป็นสัญญาณของบั๊ก และอย่าเขียนสคริปต์เก็บกวาดที่เผลอแมปมันไปเป็น 1900-03-01
• เก็บวันที่เป็น ISO 8601 ตอน export ในรูปแบบ YYYY-MM-DD เพื่อให้ระบบปลายทางไม่ต้องเดา epoch กันอีกเลย
• จับตาออฟเซ็ต 1,462 วัน เมื่อข้อมูลข้ามจากระบบหนึ่งไปอีกระบบ ตัวเลขนี้คือลายนิ้วมือของการใช้ระบบไม่ตรงกัน
• อย่าลืมเศษเวลา serial ที่มีทศนิยมรวมเวลาในวันด้วย ควรปัดหรือแยกให้ชัดเจนก่อนนำไปเทียบกัน
• สุ่มตรวจทุกชุดงาน ด้วยคู่ค่าที่รู้จัก เช่น 43831 กับ 2020-01-01 ก่อนเชื่อผลลัพธ์ทั้งชุดเสมอ

พร้อมถอดรหัส serial number แรกของคุณหรือยัง เปิด Excel Serial Date Converter ฟรี วางค่าลงไป แล้วดูวันที่จริงปรากฏขึ้นทันที ไม่ต้องเขียนสูตร ไม่ต้องสมัครสมาชิก และไม่ต้องเปิดโปรแกรมสเปรดชีตเลย

เครื่องมือที่เกี่ยวข้องที่คุณอาจสนใจ:

• Unix Timestamp Converter — ถอดรหัสรูปแบบวันที่ตัวเลขที่โด่งดังอีกแบบ คือจำนวนวินาทีนับจากปี 1970
• Timezone Converter — แปลงวันที่ที่ได้เป็นเวลาท้องถิ่นที่ถูกต้องในทุกเขตเวลาทั่วโลก
• Age Calculator — เปลี่ยนวันที่สองค่าเป็นอายุและระยะเวลาที่แม่นยำ

ขอให้สนุกกับการแปลงวันที่!

คำถามที่พบบ่อย

ถ: Excel serial date number เป็นตัวเลขแบบไหนกันแน่? ตอบ: มันคือตัวเลขที่ Excel เก็บไว้เบื้องหลังวันที่ทุกค่า ในระบบ 1900 จะนับจำนวนวันจาก epoch 1899-12-30 เช่น 43831 หมายถึง 2020-01-01 และเศษทศนิยมคือเวลาภายในวันนั้น

ถ: ทำไม serial number 60 ถึงแสดงเป็นวันที่ 1900-02-29 ซึ่งไม่มีอยู่จริง? ตอบ: Excel สืบทอดบั๊กนี้มาจาก Lotus 1-2-3 ที่เข้าใจผิดว่าปี 1900 เป็น leap year Microsoft คงรูปแบบตัวเลขเดิมไว้เพื่อความเข้ากันได้ serial 60 จึงถูกจองถาวรให้วันที่สมมติวันนั้น และ serial ที่อยู่หลังจากนั้นล้วนเดินนำจำนวนวันจริงไปหนึ่งวัน

ถ: จะรู้ได้อย่างไรว่าไฟล์ใช้ date system 1900 หรือ 1904? ตอบ: ลองถอดรหัสวันที่ที่คุณจำได้จากบริบท เช่น วันที่ในใบแจ้งหนี้หรือวันเกิด ด้วยทั้งสองระบบใน converter คำตอบที่ตรงกับความจริงคือระบบของไฟล์นั้น ไฟล์ Mac รุ่นเก่ามักใช้ 1904 ส่วน Excel บน Windows และ Google Sheets ใช้ 1900

ถ: Google Sheets กับ Excel ใช้ serial number ชุดเดียวกันสำหรับวันที่หรือไม่? ตอบ: ใช่ Google Sheets ใช้ระบบแบบเดียวกับ 1900 system ของ Excel รวมถึง epoch 1899-12-30 และผลของ leap year bug ด้วย serial จึงใช้แทนกันได้ระหว่างสองโปรแกรม และ converter ตัวเดียวใช้ได้กับทั้งคู่