“อยากแปลงคำว่า Aug 2019 ให้เป็นวันที่ใน Excel ต้องทำไงครับพี่?” จู่ๆตั้มก็โพล่งถามขึ้น
“ครอบด้วยฟังก์ชัน VALUE จบ!” ผมตอบ
“ง่ายยังงั้นเลยหรือพี่” ตั้มถามกลับเพราะคำตอบฟังดูง่ายเกิน
“ใช่ ง่ายยังงั้นเลย”
“สมมติผมเก็บค่า Aug 2019 ไว้ที่เซลล์ A4 แล้วไงต่อครับพี่”
“พิมพ์สูตร VALUE(A4) จบ!”

“เฮ้ย! ได้จริงๆด้วยอ่ะพี่ ไม่คิดว่าจะง่ายขนาดนี้” ตั้มร้องเสียงหลงหลังจากทดลองใช้สูตร
“แต่… ระวังนิดนึง” ผมเสริม
“คำตอบจะได้เป็นวันที่ 1 เสมอ เช่น 1 สิงหาคม 2019”
“ไม่เป็นไรครับพี่ ได้วันที่ 1 ก็ดีเหมือนกัน เพราะถ้าได้วันที่ 30 เดี๋ยวจะมีปัญหากับเดือนกุมภา” ตั้มตอบพลางส่งยิ้มกว้างด้วยความปีติ

“ว่าแต่ ฟังก์ชัน VALUE ใช้ทำอะไรหรือครับพี่”
“VALUE เป็นฟังก์ชันที่ใช้ตามชื่อเลย ใช้เปลี่ยน Text ให้เป็นตัวเลข”
โครงสร้างฟังก์ชันคือ
VALUE( text )
เวลาใช้ก็เอา VALUE ไปครอบ Text หรือครอบเซลล์ที่เก็บค่า text เช่น
VALUE(“Aug 2019”)
VALUE(A4)
ง่ายสุดๆ

“แล้วถ้าอยากได้เป็นวันที่ 16 ล่ะครับ เช่น เก็บตัวหนังสือ 16 Aug ไว้ที่เซลล์ A5” ตั้มถามต่อ
“ใช้สูตรเดิม VALUE(A5) จบ” ผมตอบ

“เฮ้ย! ได้จริงด้วยอ่ะพี่” ตั้มตกใจต่อเนื่อง
“ระวังนิดนึง สูตรนี้จะได้คำตอบเป็นวันที่ปีปัจจุบันเท่านั้นนะ”
“ยังไงครับ” ตั้มถาม
“เช่น ถ้าปีนี้คือปี 2019 จะได้คำตอบเป็น 16 สิงหาคม 2019
แต่ถ้าปีนี้คือปี 2020 จะได้คำตอบเป็น 16 สิงหาคม 2020″ ผมอธิบาย

“งั้น ถ้าค่าตั้งต้นมีทั้งวัน เดือน ปี น่าจะดีที่สุดใช่ไหมครับ
เช่น A6 เก็บค่า September 3, 2020 ที่เป็น Text เอาไว้” ต้้มเริ่มเกิดไอเดียบรรเจิด
“ใช่ ใช้สูตร VALUE(A6) จะได้ผลลัพธ์เป็น 3 กันยายน 2020″

“เอ.. ทำไมผมได้ผลลัพธ์เป็น 44077 ล่ะครับพี่ ทำไมไม่ได้เป็นวันที่”
คำถามนี้ดีมาก ขออธิบายเรื่องวันที่เพิ่มเติมเล็กน้อยครับ

จริงๆแล้ววันที่ใน Excel ก็คือตัวเลขนั่นเอง ที่เห็นเป็นวันที่คือการแสดงผลเท่านั้น แต่ค่าเนื้อในจริงๆเป็นตัวเลข
(อารมณ์เหมือนสังข์ทอง กายภายนอกเป็นเงาะป่า แต่กายภายในเป็นทอง ^^)

วันที่เก่าสุดที่ Excel รองรับได้คือวันที่ 1 มกราคม ค.ศ. 1900
ดังนั้น 1 จึงเทียบเท่าวันที่ 1 มกราคม 1900
2 เทียบเท่าวันที่ 2 มกราคม 1900
3 เทียบเท่าวันที่ 3 มกราคม 1900
32 เทียบเท่าวันที่ 1 กุมภาพันธ์ 1900
การเพิ่มขึ้น 1 ก็คือการเพิ่มขึ้น 1 วันนั่นเอง

1 ปี = 365
10 ปี = 3,652 (ทุก 4 ปี เดือนกุมภาพันธ์จะมี 29 วัน)
100 ปี = 36,525 (ทุก 4 ปี เดือนกุมภาพันธ์จะมี 29 วัน)
120 ปี = 43,830
ดังนั้นวันที่ในปี 2020 จะมีค่ามากกว่า 43,830 เพราะห่างจากปีเริ่มต้น (1900) 120 ปี
วันที่ 3 กันยายน 2020 จึงมีค่าที่แท้จริงเป็น 44,077 นั่นเอง ^__^

ถ้าอยากปรับให้แสดงรูปแบบเป็นวันที่ ก็เลือกรูปแบบเป็นวันที่ได้เลย

หรือถ้าเข้าใจเรื่อง custom number format จะปรับใน custom ตามใจชอบก็ได้ไม่ว่ากัน ^_^
เช่น อยากได้ผลลัพธ์เป็น 03-Sep-2020 ก็ปรับเป็น
[$-en-US]dd-mmm-yyyy;@

“en-US” คืออะไรครับพี่?” ตั้มถาม
en-US คือ locale code คือโค้ดที่ใช้ระบุรูปแบบวันที่และเวลา
อ่านรายละเอียดเพิ่มเติมได้จากเว็บนี้ครับ
ttps://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-lcid/a9eac961-e77d-41a6-90a5-ce1a8b0cdb9c

“แล้วถ้าเก็บค่า 3 Sep 2020 แบบเป็นตัวหนังสือไว้ในเซลล์ A7 ก็แปลงเป็นวันที่โดยใช้สูตร VALUE(A7) ได้เลยใช่ไหมครับ?” ตั้มพรั่งพรู
“ถูกต้องนะคร๊าบ” (กรุณานึกภาพคุณปัญญาพูดจะเพิ่มอรรถรสในการอ่านขึ้น 20% ^^)

“แล้วถ้าเก็บค่า 03-09-2020 แบบเป็น text ไว้ในเซลล์ A8 ก็แปลงเป็นวันที่โดยใช้ VALUE(A8) ได้เลยไหมครับพี่?” ตั้มซักต่อ
“ได้ และไม่ได้” ผมตอบ
“ยังไงอ่ะพี่ งง”
ที่ตอบว่าได้ เพราะถ้าเซ็ตรูปแบบวันที่ใน Windows เป็น วัน-เดือน-ปี

จะได้ผลลัพธ์เป็น 3 กันยายน 2020 ตามต้องการ

แต่ถ้าเซ็ตฟอร์แมตของวันที่ใน Windows เป็น เดือน-วัน-ปี

จะได้ผลลัพธ์เป็นวันที่ 9 มีนาคม 2020 แทน !

เพราะโปรแกรมจะมองเลขตัวแรก (03) เป็นเลขเดือน และมองเลขตัวที่สอง (09) เป็นเลขวัน โดยยึดตามค่าที่เซ็ตไว้ใน Windows
ดึงนั้น มีโอกาสที่คนสองคนเขียนสูตรแล้วได้ผลลัพธ์ไม่เท่ากัน

ถ้าเอาชัวร์ เก็บค่าวันที่ในรูปแบบ ปี-เดือน-วัน ดีที่สุด
เช่น 2020-09-03
เขียนสูตรเป็น VALUE(A9)

ได้คำตอบเป็น 3 กันยายน 2020 เสมอ
โปรแกรมจะเข้าใจรูปแบบนี้และแปลผลถูกต้อง ไม่ว่าจะเซ็ตรูปแบบวันที่ใน Windows เป็น วัน-เดือน-ปี หรือ เดือน-วัน-ปี ก็ตาม ^__^

หรือจะเก็บค่าเป็น ปี/เดือน/วัน ก็ได้เช่นกัน

คนไทยเราคุ้นเคยกับรูปแบบ วัน-เดือน-ปี มากกว่า เพื่อให้เข้าใจง่าย บทความนี้ขออธิบายโดยสมมติว่าเซ็ตค่าใน Windows เป็น วัน-เดือน-ปี (dd-mmm-yy) นะครับ ^__^

“แล้วถ้าค่าในเซลล์คือคำว่า Jan ต้องการแปลง Jan ให้เป็นวันที่ ต้องทำไงครับพี่” ไอเดียบรรเจิดของตั้มเริ่มหลั่งไหล
“ถ้าในเซลล์คือคำว่า Jan ใช้ฟังก์ชัน VALUE ครอบตรงๆไม่ได้ เพราะ Jan ไม่ใช่ Text ที่เป็นวันที่”
ถ้าจะแปลง ต้องปรับ Jan ให้หน้าตาคล้ายวันที่เสียก่อน
เช่น
จาก Jan ก็ปรับเป็น
1Jan
หรือใส่เลข 1 ไว้ด้านหน้านั่นเอง

สมมติเก็บค่า Jan ไว้ในเซลล์ A11 ก็เขียนสูตรเป็น
VALUE(1&A11)
จะได้ผลลัพธ์เป็น 1 มกราคม 2019

วิธีนี้จะได้ผลลัพธ์เป็นปีปัจจุบันเสมอ
ดังนั้นถ้าเขียนสูตรนี้ปีหน้า จะได้ผลลัพธ์เป็น 1 มกราคม 2020
ถ้าต้องการล็อคให้เป็นวันที่ปีใด ก็ให้พิมพ์ปีนั้นต่อท้ายได้เลย เช่น
จาก Jan เป็น Jan2020
หรือเขียนสูตรเป็น
VALUE(A12&2020)

ได้ผลลัพธ์เป็นวันที่ 1 มกราคม 2020
เทคนิคนี้จะได้ผลลัพธ์วันที่ 1 ของเดือนนั้นๆเสมอ

ถ้าต้องการระบุวันที่ด้วย ก็ใส่วันที่ลงไปในสูตรได้เลย เช่น
=VALUE(15&A13&2020)
ได้ผลลัพธ์เป็นวันที่ 15 มกราคม 2020 ^__^

“แล้วถ้ามีข้อมูลเป็นปี แต่อยากเปลี่ยนเป็นวันที่ ควรเขียนสูตรยังไงดีครับ?”
คำถามนี้ดีมาก ลองมาเดาคำตอบกันดีไหมครับ ^__^
ติ๊กต่อก ติ๊กต่อก ..
.
.

ใช่แล้วครับ
เปลี่ยนปีให้หน้าตาคล้ายวันที่นั่นเอง
เช่น จากเดิมคือ 2021
ก็เปลี่ยนเป็น Jan2021
=VALUE(“Jan”&A14)

เริ่มสนุกแล้วใช่ไหมล่ะ ^__^

“แล้วถ้าต้องการแปลง 09.12.2020 เป็นวันที่ ต้องทำยังไงครับ?”
เคสนี้ต้องใช้เทคนิคนิดนึง นั่นคือต้องเปลี่ยน . (จุด) ให้เป็น / (slash) ก่อน โดยใช้ฟังก์ชัน SUBSTITUTE
เช่น เขียนสูตรเป็น
=SUBSTITUTE(A15,”.”,”/”)

ผลลัพธ์ของ SUBSTITUTE จะได้เป็น Text หรือได้เป็น
09/12/2020
ต้องการแปลง 09/12/2020 ให้เป็นวันที่
ก็นำ VALUE ไปครอบ
=VALUE(SUBSTITUTE(A15,”.”,”/”))
ได้ผลลัพธ์เป็น 9 ธันวาคม 2020

ระวัง! ถ้าเซ็ตค่าใน Windows เป็น เดือน-วัน-ปี เคสนี้จะได้ผลลัพธ์เป็น 12 กันยายน 2020 (ไม่ใช่ 9 ธันวาคม 2020)

ถ้าเอาให้ชัวร์ เคสนี้ใช้ฟังก์ชัน DATE แปลงเป็นวันที่ดีกว่า

“ฟังก์ชัน DATE คืออะไร?“
ฟังก์ชัน DATE คือฟังก์ชันที่สามารถเปลี่ยนข้อมูลให้กลายเป็นวันที่ โดยระบุค่า 3 อย่างคือ ปี (year), เดือน (month) และวัน (day)

โครงสร้างของฟังก์ชัน DATE คือ
DATE( year, month, day )

year, month, day ต้องหน้าตาเป็นตัวเลข
เช่น เก็บค่า 09.12.2020 ไว้ในเซลล์ A16
ต้องการได้ year ก็ใช้สูตร RIGHT(A16,4)
ต้องการได้ month ก็ใช้สูตร MID(A16,4,2)
ต้องการได้ day ก็ใช้สูตร LEFT(A16,2)
รวมสูตรเป็น
=DATE( RIGHT(A16,4), MID(A16,4,2), LEFT(A16,2) )
ได้ผลลัพธ์เป็น 9 ธันวาคม 2020 ทุกกรณี

“แล้วถ้าต้องการแปลง 20190712 ให้เป็นวันที่ล่ะครับ ควรเขียนสูตรยังไง?”
ใช้วิธีเดียวกันได้เลยครับ ^__^

เช่น เก็บค่า 20190712 ไว้ในเซลล์ A17
ต้องการได้ year ก็ใช้สูตร LEFT(A17,4)
ต้องการได้ month ก็ใช้สูตร MID(A17,5,2)
ต้องการได้ day ก็ใช้สูตร RIGHT(A17,2)
รวมสูตรเป็น
=DATE(LEFT(A17,4),MID(A17,5,2),RIGHT(A17,2))
ได้ผลลัพธ์เป็น 12 กรกฎาคม 2019

“แล้วถ้าต้องการแปลง 12072019 ให้เป็นวันที่ล่ะครับ ต้องเขียนสูตรยังไง?”

ใช้เทคนิคเดียวกันได้เลยครับ แต่ปรับสูตรนิดหน่อยตอนคำนวณ วัน เดือน ปี
หรือเขียนสูตรเป็น
=DATE(RIGHT(A18,4),MID(A18,3,2),LEFT(A18,2))

ไม่ยากเลยใช่ไหมครับ ^__^

และนี่คือเทคนิคทั้งหมดของบทความนี้

น่าจะครอบคลุมกรณีการเก็บค่าวันที่เป็นตัวหนังสือ (Text) ทั้งหมด
คราวนี้เจอจะวันที่รูปแบบไหน แปลกประหลาดยังไง ก็ไม่ใช่ปัญหา
ปัญหาคือใช้เวลาเขียนสูตรกี่วินาทีเสร็จ
จริงไหมครับ ^__^

สำหรับท่านใดที่สนใจไฟล์ตัวอย่างของบทความนี้ สามารถดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟซบุ๊กแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^

Credit: https://www.ablebits.com/office-addins-blog/2015/03/26/excel-convert-text-date/