แก้เวลาที่พิมพ์ผิดให้เป็นถูก ทำยังไง?

หากเราจะกรอกข้อมูลเวลา 7 โมง 45 นาที ในตอนเช้า เราจะต้องกรอกว่า 7:45 โดยใช้เครื่องหมาย : (Colon) ห้ามกรอกว่า 7.45 ที่ใช้เครื่องหมาย . (dot) เด็ดขาด เพราะมันเป็นคนละเรื่องกันโดยสิ้นเชิง

การเขียน 7:45 หากปรับ Number Format ให้เป็น general จะเห็นว่าค่าที่แท้จริงของเลขนั้นคือ 0.3229 กว่าๆ ไม่ใช่ 7.45 แน่นอน (ใครงงว่าทำไม ไปอ่านได้ที่นี่)

ซึ่งเลข 0.3229 นั้นก็คือเป็นการบอกว่าเวลา 7:45 คือ 0.3229 วันนั่นเอง (เวลาคือการบอกรายละเอียดว่าเป็นสัดส่วนเท่าไหร่ของ 1 วัน)

ได้ข้อมูลมาแบบผิดๆ

ตอนนี้เรารู้แล้วล่ะว่าควรจะกรอกด้วยเครื่องหมาย : ไม่ใช่จุด แต่ถ้าดันกรอกผิดไปแล้วตั้งเยอะ หรือมีคนกรอกมาให้อีกทีจะทำไงดีล่ะ?

วิธีแก้โดยเบื้องต้น คือ เราจะต้องเปลี่ยนเครื่องหมาย . ให้เป็น : ให้ได้ ซึ่งเราจะใช้ฟังก์ชัน SUBSTITUTE ในการทำ เช่น

อย่างไรก็ตามผลลัพธ์ของ SUBSTITUTE จะออกมาเป็น Text ดังนั้นเราต้องบังคับให้เป้นตัวเลขซะ ด้วยการ *1 เช่น

หลังจากนั้นก็เปลี่ยน Format ให้เป็นเวลาซะ

ผลลัพธ์ก็ออกมาเป็นเวลาที่ถูกต้องแล้ว

อย่างไรก็ตามสูตรแบบนี้มันยังใช้ไม่ได้กับทุกกรณี

วิธีเดิมยังมีจุดอ่อน

สมมติว่าเป็นเวลา 7:30 แล้วดันกรอกมาเป็น 7.30 จะเกิดอะไรขึ้น?

เลข 7.30 จะกลายเป็น 7.3 (เพราะ 0 หลังทศนิยมไม่มีควาหมาย) ทำให้เวลาที่ออกมากลายเป็น 7 โมง 3 นาทีไปซะอย่างงั้น!

ทางแก้ไข

ทางแก้ไขคือ ต้องใช้ฟังก์ชัน TEXT บังคับเปลี่ยนเลขให้เป็น Text ที่มีทศนิยม 2 ตำแหน่งซะก่อนที่จะ SUBSTITUTE จุดเป็น :

นั่นคือ จาก A1 ธรรมดา จะกลายเป็น TEXT(A1,”0.00″)

ทำให้สูตรสุดท้ายเป็นดังนี้

=SUBSTITUTE(TEXT(A1,"0.00"),".",":")*1

สูตรนี้จึงจะสมบูรณ์ ใช้ได้กับเลขทุกตัวครับ