ขอเริ่มที่ปัญหาก่อนแล้วกันครับ
Excel จะมีความสามารถหนึ่งที่ทำให้ยุ่งยากได้ในบางครั้งเช่นครั้งนี้ นั่นคือ ถ้าหากมันสามารถแปลงตัวเลขที่อยู่ในรูปแบบ Text นั้นเป็นวันที่ได้มันจะประเมินว่าเป็นวันที่เมื่อเรานำไปคำนวณต่อ
วิธีแก้ไขคือ ก่อนการคีย์ค่าใด ๆ ในช่องที่ไม่ต้องการให้ Excel แปลงแบบอัตโนมัติให้กำหนดคอลัมน์นั้นเป็น Text เอาไว้ก่อน แต่มันก็มีบางโอกาสที่เรากำหนดเช่นนั้นไม่ได้ เช่น ได้รับไฟล์มาจากเพื่อนพนักงานอีกทอดหนึ่งหรือเป็นไฟล์ที่ได้มาจากระบบ เป็นต้น
จากสูตร
=IF(ISNUMBER(DATEVALUE(C2)),LEFT(C2,FIND("/",C2)-1)/RIGHT(C2,2),--C2)
สำหรับการแปลงเป็นวันที่ได้หรือไม่ได้ วิธีที่ผมใช้คือตรวจด้วยฟังก์ชัน DateValue หาก Excel แปลงค่าที่ตรวจสอบเป็นวันที่ได้มันจะแสดงผลเป็นตัวเลข หากแปลงไม่ได้มันจะติด Error เป็น #VALUE!
Isnumber จะเป็นการตรวจสอบว่าผลลัพธ์ที่ได้จาก DateValue เป็นตัวเลขหรือไม่ หากเป็นตัวเลขจะได้ True หากไม่ใช่จะเป็น False
ฟังก์ชั่น IF ที่ครอบอยู่ด้านนอกจะเป็นการตรวจสอบว่า หากเงื่อนไข ซึ่งก็คือ
ISNUMBER(DATEVALUE(C2))) เป็นจริง (True) จะแสดงผลลัพธ์ด้วยสูตร
LEFT(C2,FIND("/",C2)-1)/RIGHT(C2,2) หากเป็นเท็จ (False) จะแสดงผลลัพธ์ด้วยสูตร
--C2 ซึ่งหมายถึงการกลับเครื่องหมายของ C2 จำนวน 2 รอบ เพื่อแปลงตัวเลขที่เก็บเป็น Text ให้กลับมาเป็นตัวเลขแบบ Number
จากสูตร
LEFT(C2,FIND("/",C2)-1)/RIGHT(C2,2) หมายถึง ให้ตัดอักขระด้านซ้ายของ C2 มาตามจำนวนที่กำหนด ซึ่งจำนวนที่กำหนด เราหามาด้วย
FIND("/",C2)-1 หมายถึงให้หาว่าเครื่องหมาย / อยู่ในลำดับที่เท่าไรให้ลบค่าลำดับนั้นออกด้วย 1 ที่ต้องลบด้วย 1 เพราะเราจะไม่รวมลำดับของเครื่องหมาย / เข้าไปด้วย
นำผลลัพธ์จากวรรคก่อนมาหารด้วย
RIGHT(C2,2) หมายถึง หารด้วยอักขระด้านขวาของ C2 จำนวน 2 อักขระครับ