วัน เวลา ใน Excel

💡 จั่วหัวเรื่องวันเวลาเป็นเรื่องที่น่าสนใจและไม่ใช่เรื่องง่าย ๆ สำหรับเราชาวพลพรรค Excel เพราะจะต้องเข้าใจตามหัวข้อเหล่านี้เป็นตัวอย่าง

1. วัน เวลา ถูกเก็บอยู่ในรูปแบบตัวเลข Excel จะเริ่มรับรู้วันแรกคือ 01/01/1900 ซึ่งเริ่มนับลำดับ 1 สำหรับวันปัจจุบันเป็นลำดับที่เท่าไร ทดสอบง่ายๆ คือในเซลล์ใดๆ คีย์ =Today() แล้วจัดรูปแบบเซลล์เป็น General
2. การนำวัน เวลามาบวกลบกันสามารถทำได้เหมือนตัวเลขธรรมดา แต่หากนำวันที่ที่น้อยกว่าเป็นตัวตั้ง แล้วนำวันที่ที่มากกว่ามาเป็นตัวหัก ผลลัพธ์จะได้เป็นค่าลบ ซึ่ง Excel จะแสดงเครื่องหมาย # จนเต็มความกว้างเซลล์ 😮
   😉 อย่าเพิ่งตกใจ เพราะนี่เกิดจากการที่ Excel จำรูปแบบที่เป็นวันที่ เมื่อบวกลบกันแล้วก็จะจัดรูปแบบเป็นวันที่ให้เลย หากต้องการให้แสดงเป็นตัวเลขธรรมดา สามารถจัดรูปแบบเป็น Generalได้สมมุติว่า
   A1=01/01/2008, A2=05/02/2008
   หากอยากทราบผลต่างของวันกรณีนำวันทีที่น้อยกว่า ลบ วันที่ที่มากกว่าและหากว่าไม่ต้องการให้แสดง # จนเต็มความกว้างเซลล์ ให้คลิกขวาที่เซลล์ผลลัพธ์ > Format Cells > แถบ Number > General เป็นอันเรียบร้อย 😀
3. การทำวันเดือนปีที่เป็น พ.ศ.ให้เป็น ค.ศ. สามารถนำตัวเลข 198327 ไปหักออกได้
   เช่นมีวันที่ที่เป็น พ.ศ. อยู่ที่คอลัมน์ A และต้องการเปลี่ยนให้เป็นค.ศ. ทั้งหมดสามารถทำโดย
   คีย์ 198327 ไว้ที่เซลล์ใดๆ > คลิกขวา > Copy > คลุมข้อมูลในคอลัมน์ A > คลิกขวา > Paste Special > Value > Subtract > OK
   สังเกตผลลัพธ์ที่ได้แต่หากข้อมูลมีความหลากหลาย การหักด้วย 198327 จะพบว่าวันที่จะคลาดเคลื่อนไปได้ในบางช่วงของบางปี แต่สามารถประยุกต์ 198327 มาใช้ได้
4. หากต้องการปรับวันที่ที่คีย์เป็น พ.ศ. ให้เป็น ค.ศ. โดยสมมุติ A1:A65536 คือเซลล์ที่ต้องการแปลงวันที่ สามารถใช้สูตรได้ดังนี้
   1. ใช้ Datedif เช่น
      =Datedif(A1-198327,A1,"md")+A1-198327
      Enter > Copy ลงด้านล่าง
      การใช้สูตรนี้ เมื่อแปลงเป็น ค.ศ. แล้ว ปีใดเดือน ก.พ. มี 29 วัน วันที่ 1 ของเดือน มี.ค.จะถูกละไว้ ซึ่งจะเป็นการ Run วันที่ให้ใหม่ ไม่ต้องหาว่าปีใดเดือน ก.พ.มี 29 วัน
   2. หากต้องการวัน เดือน ตรงกับวันที่ที่ต้องการแปลง ไม่สนใจเรื่อง 29 วันแต่ต้องการให้วันและเดือนตรงกัน ใช้ฟังก์ชั่น Date
      =Date(Year(A1)-543,Month(A1),Day(A1))
      Enter แล้ว Copy ลงด้านล่าง
      การ ใช้สูตรนี้ หากเดือน ก.พ.ของ “ปีต้องการแปลง” มี 29 วัน แต่เดือน ก.พ.ของ “ปีที่แปลงมาเป็น” มีแค่ 28 วัน วันที่ 1 มี.ค. จะซ้ำ 2 ครั้ง เช่นปี พ.ศ. …2504, 2508, 2512, 2516, 2520 ฯลฯ
   3. ตัวอย่างสูตรการแปลงวันที่ในคอลัมน์ A โดยไม่เอาค่าซ้ำ
      =(Text(A1,"d/m/")&Text(A,"yyyy")-543)+0
      Enter แล้ว Copy ลงด้านล่าง
      สูตรนี้หากแปลงมาเป็นวันที่ที่ไม่มีอยู่จริงจะเกิดค่าผิดพลาด ซึ่งสามารถประยุกต์ค่าผิดพลาดเป็นค่าใด ๆ ตามต้องการ
   4. กรณีวันที่ต้องการแปลงอยู่ในช่วงปี 2499 ถึง 2562 สามารถใช้สูตร
      =A1-198327+(Day(A1-198327)<>Day(A1))
5. และหากต้องการทำวันเวลาที่เป็น ค.ศ.ให้เป็น พ.ศ.ทำอย่างไร? อย่าเพิ่งดีใจ จะบอกว่าเอา 198327 ไปบวกใช่ไหม? ผิด! 😕  การคีย์วันที่ควรคีย์เป็น ค.ศ. หากต้องการเป็น พ.ศ. ให้จัดรูปแบบเอาครับพี่น้องครับ ไม่ใช่ไปบวกด้วย 198327 ครับ
   💡 โปรดคำนึงว่าการแปลงวันที่ที่เป็น พ.ศ. ให้เป็น ค.ศ. นี่คือการแก้ไข เพราะวันที่ควรคีย์เป็น ค.ศ. และการนำ 198327 มาใช้แล้ว ได้วันที่ที่ตรงกันหรือไม่ ปัญหาไม่ใช่อยู่ที่ตัวเลข 198327 แต่โอกาสที่นำ 198327 ไปหักแล้วได้วันที่ที่ตรงกันนั้นมีมาก สำคัญที่การประยุกต์ใช้ ต้องคิดต่อว่าถ้าได้วันที่ที่ไม่ตรงแล้วจะทำอย่างไร ใช้สูตรหรือฟังก์ชั่นใดช่วยได้ กรณีที่ใช้ 198327 หักแล้วได้วันที่ตรงกันก็ไม่จำเป็นต้องเสียเวลาคิดฟังก์ชั่นให้ซับซ้อน 😆
6. มาในเรื่องของเวลาบ้างใน 1 วันหรือ 24 ชม. แทนด้วยเลข 1 หรือก็คือ 24/24 หากคีย์ 1/24 แล้วจัดรูปแบบเป็นเวลาจะได้ 1:00:00 ทดลองคีย์เลขอื่นๆ สังเกตดูผล
   💡 ทบทวนกันหน่อย
   1 วัน 24 ชม.
   1 ชม. 60 นาที
   1 นาที 60 วินาที
   1 วัน 1440 นาที (24 ชม. * 60 นาที)
   1 วัน 86400 วินาที (24 ชม. * 60 นาที * 60 วินาที)
7. การคีย์เวลาต้องคีย์ตัวเลขคั่นด้วย : เสมอ เมื่อคีย์เวลาแล้วอยากรู้ว่าเป็นเลข Serial ใดก็ให้จัดรูปแบบเป็น General
8. การรวมเวลาที่เกิน 24 ชม. ต้องจัดรูปแบบของเซลล์ผลลัพธ์เป็น [h]:mm:ss หากไม่เอาส่วนที่เป็นนาที วินาที สามารถละไว้ได้ การแปลงเวลาในรูปแบบทศนิยมให้เป็นรูปแบบเวลา
   💡 ยกตัวอย่างเช่น A1=1.5 ต้องการแปลงเป็น 1:30 สูตรคือ
   =Int(A1)/24+(A1-Int(A1))*60/1440 หรือ
   =1.50/24
   ทั้งนี้เพราะ 1 ชม. เท่ากับ 24/24 ดังนั้น 1.5 ชม. ก็เท่ากับ 1.5/24
   💡 แล้วถ้า 1.30 ชม.ล่ะ จะแปลงเป็น 1:30 ชม. ได้อย่างไร
   ง่ายมาก ก็เปลี่ยน . ให้เป็น : ครับ มาดูตัวอย่างกัน
   =Substitute(Text(A1,"0.00"),".",":")+0 หรือ
   =Int(A1)/24+(A1-Int(A1))*100/1440
   🙄 สังเกตเห็นอะไรไหมหนอ? ถ้าไม่เห็นก็โปรดสังเกตว่า
   1. ส่วนของชม.เราก็ทำให้เป็นวัน
   2. ส่วนของนาทีเราก็ทำให้เป็นวัน
   3. หลังจากคำนวณได้แล้วต้องจัดรูปแบบเป็นเวลา
9. ฟังก์ชั่นที่ควรศึกษาเกี่ยวกับวันเวลาได้แก่
   Datedif(), Hour(), Minute(), Secound(), Weekday(), Day(), Today(), Date(), Month(), Year() เป็นต้น 😉

Revised: January 28, 2017 at 10:52