วิธีแปลง ค.ศ. เป็น พ.ศ. และ พ.ศ. เป็น ค.ศ. ใน Excel

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

Excel เก็บวันที่อย่างไร?

Excel เก็บวันที่จริงเป็นตัวเลข Serial Number แล้วใช้ Number Format กำหนดว่าจะให้แสดงเป็นวัน เดือน ปี แบบไหน ตัวอย่างเช่น วันที่ 31 สิงหาคม 2026 สามารถแสดงเป็น 31/08/2026 หรือ 31/08/2569 ได้ตามรูปแบบปฏิทินที่เลือก โดยยังเป็นวันเดียวกันครับ

=DATE(2026,8,31)

ตัวอย่างนี้ใช้ปี ค.ศ. 2026 เพื่อสร้างวันที่ 31 สิงหาคม 2026

ถ้าเซลล์ B2 เก็บวันที่นี้จริง เราสามารถตรวจสอบได้ด้วยสูตรต่อไปนี้

=ISNUMBER(B2)

=YEAR(B2)

โดยทั่วไป ISNUMBER จะได้ TRUE และ YEAR จะได้ 2026 แม้เซลล์จะถูกจัดรูปแบบให้แสดงปี พ.ศ. ก็ตามครับ อย่างไรก็ตาม ISNUMBER เพียงอย่างเดียวไม่ได้ยืนยันว่าตัวเลขนั้นเป็นวันที่ที่ถูกต้อง ต้องตรวจสอบค่าต้นทางด้วย

กรณีนี้ง่ายที่สุดครับ ถ้าข้อมูลเป็นตัวเลขปี 4 หลัก เช่น 2026 หรือ 2569 และต้องการแปลงเป็นอีกระบบหนึ่ง ให้บวกหรือลบ 543 ได้โดยตรง

กำหนดข้อมูลเริ่มที่แถว 2 โดย B เป็นปี ค.ศ. C เป็นปี พ.ศ. และ D ใช้ตรวจสอบการแปลงกลับ

ค.ศ. → พ.ศ. (ใส่ใน C2)

=B2+543

ถ้า B2 เท่ากับ 2026 ผลลัพธ์จะเป็น 2569 จากนั้นลากสูตรลงมาทั้งคอลัมน์ได้ครับ

พ.ศ. → ค.ศ. (ใส่ใน D2)

=C2-543

ถ้า C2 เท่ากับ 2569 ผลลัพธ์จะเป็น 2026 เหมาะกับข้อมูลที่เก็บปีเป็นตัวเลขแยก เช่น ปีงบประมาณ ปีการศึกษา หรือปีที่ต้องการใช้เป็นเงื่อนไขรายงาน โดยต้องตรวจสอบว่าปีต้นทางใช้ระบบใดครับ

=IF(B2="","",B2+543)

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

ถ้าใน Excel มีวันที่จริงอยู่แล้ว และต้องการให้แสดงปี พ.ศ. เช่น 31/08/2026 → 31/08/2569 ไม่จำเป็นต้องสร้างวันที่ใหม่ ครับ ให้เปลี่ยนรูปแบบการแสดงผลของเซลล์แทน

ตัวอย่างนี้กำหนดให้ B เป็นวันที่จริง ไม่ใช่ Text โดยใส่ B2 =DATE(2026,8,31) และใส่วันที่แถวอื่นด้วย DATE เช่นกัน

ดึงปี ค.ศ. จากวันที่จริง (C2)

=YEAR(B2)

แสดงตัวเลขปี พ.ศ. ในคอลัมน์ใหม่ (D2)

=YEAR(B2)+543

สูตรนี้คืนค่าเป็นตัวเลขปี พ.ศ. เหมาะกับการสร้างคอลัมน์ปีเพื่อสรุปรายงาน แต่ไม่ได้เปลี่ยนวันที่ใน B2 ครับ

สร้างข้อความวันที่ พ.ศ. สำหรับรายงาน (E2)

=TEXT(B2,"dd/mm/")&(YEAR(B2)+543)

ตัวอย่างใช้ dd/mm/ สำหรับวันและเดือน แล้วต่อด้วยปี พ.ศ. ที่คำนวณเอง จึงไม่ต้องพึ่งการแสดงปีของรูปแบบวันที่ในเครื่อง

ตัวอย่าง B2 เป็นวันที่จริง 31 สิงหาคม 2026 จะได้ข้อความ 31/08/2569 วิธีนี้เหมาะกับการแสดงบนรายงานหรือส่งออกข้อความ โดยไม่เปลี่ยนค่าต้นฉบับครับ

ถ้าต้องการแสดงกลับเป็น ค.ศ.

เลือกวันที่จริงเดิมแล้วเปลี่ยนรูปแบบการแสดงผลเป็นวันที่ ค.ศ. หรือใช้สูตร TEXT เมื่อจำเป็นต้องได้ข้อความสำหรับเอกสารครับ

=TEXT(B2,"dd/mm/")&YEAR(B2)

หากระบบต้นทางส่งข้อมูลแยกเป็น วัน เดือน และปี พ.ศ. เราสามารถนำปี พ.ศ. มาลบ 543 ก่อน แล้วใช้ DATE ประกอบเป็นวันที่จริงได้ครับ วิธีนี้เหมาะกับข้อมูลที่รู้โครงสร้างแน่นอน ไม่ใช่การเดาวันที่จากข้อความ

กำหนด B เป็นวัน C เป็นเดือน D เป็นปี พ.ศ. และ E เป็นวันที่จริงที่ต้องการสร้าง

สูตรสำหรับ E2

=DATE(D2-543,C2,B2)

Excel จะนำปี 2569 − 543 = 2026 แล้วประกอบกับเดือน 8 และวันที่ 31 จึงได้วันที่จริง 31 สิงหาคม 2026 จากนั้นกำหนด Format เป็น Date ตามต้องการครับ

อีกกรณีหนึ่งคือข้อมูลจากระบบอื่นมาเป็นข้อความ เช่น 31/08/2569 และ Excel ไม่รู้จักว่าเป็นวันที่จริง กรณีนี้ต้องแปลงจากรูปแบบต้นทางให้ชัดเจนครับ

แถว 5 เป็นตัวอย่างข้อมูลไม่ถูกต้องเพื่อสาธิตการตรวจสอบ DATE อาจปรับวันที่ที่เกินช่วงได้ จึงไม่ควรนำผลลัพธ์ไปใช้งานโดยไม่ตรวจสอบ

ขั้นที่ 1: แยกข้อความแล้วสร้างวันที่จริงใน C2

=IFERROR(DATE(--RIGHT(B2,4)-543,--MID(B2,4,2),--LEFT(B2,2)),"ตรวจสอบวันที่")

เครื่องหมาย -- ช่วยแปลงข้อความตัวเลขที่แยกออกมาให้เป็นตัวเลขก่อนส่งเข้า DATE หากข้อความไม่สามารถแปลงเป็นตัวเลขได้ IFERROR จะแสดง “ตรวจสอบวันที่” แทนครับ

ขั้นที่ 2: ตรวจสอบว่าผลลัพธ์ตรงกับข้อความต้นทาง

สูตร DATE อาจปรับวันที่ที่ไม่มีอยู่จริง เช่น 31 กุมภาพันธ์ ให้กลายเป็นเดือนถัดไป จึงควรตรวจสอบผลลัพธ์อีกชั้นหนึ่งครับ

=IF(B2="","",IFERROR(IF(TEXT(C2,"dd/mm/")&(YEAR(C2)+543)=B2,"ผ่าน","ตรวจสอบ"),"ตรวจสอบ"))

สูตรนี้นำวันที่จริงใน C2 มาแสดงกลับเป็นข้อความ พ.ศ. รูปแบบเดียวกับต้นทาง แล้วเปรียบเทียบกับ B2 หากตรงกันจะแสดง “ผ่าน” ถ้าไม่ตรงจะแสดง “ตรวจสอบ” ครับ

ถ้าได้รับไฟล์ประเภทนี้ซ้ำทุกเดือน สามารถนำหลักการเดียวกันไปสร้างขั้นตอนใน Power Query เพื่อแปลง ตรวจสอบ และ Refresh ได้ แต่ต้องกำหนดรูปแบบวันที่และปฏิทินต้นทางให้ถูกต้องก่อนครับ

บทความ Excel Speed Work เดิมเคยแนะนำการใช้ DATE ร่วมกับ YEAR, MONTH และ DAY โดยบวกหรือลบ 543 เพื่อแปลงปีครับ สูตรเหล่านี้สามารถสร้างวันที่ใหม่ได้จริง แต่มีข้อควรระวังสำคัญเมื่อใช้กับ Date ที่เป็นวันที่จริงอยู่แล้ว

=DATE(YEAR(B9)+543,MONTH(B9),DAY(B9))

=DATE(YEAR(B17)-543,MONTH(B17),DAY(B17))

หาก B9 เป็นวันที่จริง 31/08/2026 สูตรแรกจะสร้างวันที่ในปี ค.ศ. 2569 ซึ่งเป็นคนละวันในความหมายของข้อมูลวันที่ ไม่ใช่เพียงแสดงวันที่เดิมเป็นปี พ.ศ. ส่วนสูตรที่สองจะลบ 543 จากปีของวันที่จริงที่ Excel อ่านได้อยู่แล้ว จึงอาจทำให้ได้ปีที่ไม่ตรงกับเจตนาครับ

สำหรับข้อมูลเก่าก่อนปี พ.ศ. 2484 ควรตรวจสอบหลักเกณฑ์การนับปีของเอกสารต้นทางเพิ่มเติม เพราะการเปลี่ยนวันขึ้นปีใหม่ในประวัติศาสตร์ไทยอาจทำให้การเทียบปีในบางช่วงไม่ใช่การบวกหรือลบ 543 แบบตรงไปตรงมาเสมอ บทความนี้จึงใช้ตัวอย่างวันที่สมัยปัจจุบันครับ

ลองฝึกจากข้อมูลชุดเดียวกันครับ โดยเริ่มจากสร้าง Worksheet ใหม่ แล้วกรอกหัวตารางและข้อมูลตามตำแหน่งด้านล่าง

คอลัมน์ B ต้องเป็นวันที่จริง คอลัมน์ C, D, F เป็นตัวเลขปี และ E เป็นข้อความสำหรับแสดงผล

เตรียมวันที่จริงในคอลัมน์ B

เพื่อให้ข้อมูลตัวอย่างไม่ขึ้นกับการตีความข้อความวันที่ของแต่ละเครื่อง ให้ใช้ DATE สร้างวันที่จริงใน B2–B5 ตามลำดับครับ

=DATE(2026,8,31)

=DATE(2025,1,15)

=DATE(2024,2,29)

=DATE(2027,12,1)

จากนั้นจัดรูปแบบคอลัมน์ B ให้แสดงวันที่ ค.ศ. ตามต้องการครับ

เขียนสูตรที่แถว 2 แล้วลากลง

หากต้องการคัดลอกสูตรทีละตัว ใช้กล่องด้านล่างได้เลยครับ

=YEAR(B2)

=C2+543

=TEXT(B2,"dd/mm/")&D2

=D2-543

=ISNUMBER(B2)

เมื่อได้ผลลัพธ์แล้ว ลองเปลี่ยนรูปแบบวันที่ใน B2 เป็นปฏิทินไทย แล้วตรวจสอบว่าค่าใน C2, D2 และ F2 ยังถูกต้องตามเดิมครับ วิธีนี้จะช่วยให้เห็นชัดว่าการจัดรูปแบบกับการคำนวณปีเป็นคนละเรื่องกัน

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

บทความที่เกี่ยวข้อง