Microsoft Excel Tips and Tricks: วัน เวลา ใน Excel

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

1. วัน เวลา ถูกเก็บอยู่ในรูปแบบตัวเลข


2. Excel จะเริ่มรับรู้วันแรกคือ 01/01/1900 ซึ่งเริ่มนับลำดับ 1 สำหรับวันปัจจุบันเป็นลำดับที่เท่าไร ทดสอบง่ายๆ คือในเซลล์ใดๆ คีย์ =Today() แล้วจัดรูปแบบเซลล์เป็น General


3. การนำวัน เวลา มาบวกลบกันสามารถทำได้เหมือนตัวเลขธรรมดา แต่หากนำวันที่ที่น้อยกว่าเป็นตัวตั้ง แล้วนำวันที่ที่มากกว่ามาเป็นตัวหัก ผลลัพธ์จะได้เป็นค่าลบ ซึ่ง Excel จะแสดงเครื่องหมาย # จนเต็มความกว้างเซลล์ อย่าเพิ่งตกใจ เพราะนี่เกิดจากการที่ Excel จำรูปแบบที่เป็นวันที่ เมื่อบวกลบกันแล้วก็จะจัดรูปแบบเป็นวันที่ให้เลย หากต้องการให้แสดงเป็นตัวเลขธรรมดา สามารถจัดรูปแบบเป็น General ได้
   
   สมมุติ A1=01/01/2008, A2=05/02/2008 หากอยากทราบผลต่างของวันกรณีนำวันทีที่น้อยกว่า ลบ วันที่ที่มากกว่าและหากว่าไม่ต้องการให้แสดง # จนเต็มความกว้างเซลล์ ให้คลิกขวาที่เซลล์ผลลัพธ์ > Format Cells > แถบ Number > General เป็นอันเรียบร้อย


4. การทำวันเดือนปีที่เป็น พ.ศ.ให้เป็น ค.ศ. สามารถนำตัวเลข 198327 ไปหักออกได้ เช่นมีวันที่ที่เป็น พ.ศ. อยู่ที่คอลัมน์ A และต้องการเปลี่ยนให้เป็นค.ศ. ทั้งหมด สามารถคีย์ 198327 ไว้ที่เซลล์ใดๆ > คลิกขวา > Copy > คลุมข้อมูลในคอลัมน์ A > คลิกขวา > Paste Special > Value > Subtract > OK สังเกตผลลัพธ์ที่ได้
   
   แต่หากข้อมูลมีความหลากหลาย การหักด้วย 198327 จะพบว่าวันที่จะคลาดเคลื่อนไปได้ในบางช่วงของบางปี แต่สามารถประยุกต์ 198327 มาใช้ได้ ยกตัวอย่างเช่น ต้องการปรับวันที่ที่คีย์เป็น พ.ศ. ให้เป็น ค.ศ. โดยสมมุติ 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)) 
      และหากต้องการทำวันเวลาที่เป็น ค.ศ.ให้เป็น พ.ศ.ทำอย่างไร? อย่าเพิ่งดีใจ จะบอกว่าเอา 198327 ไปบวกใช่ไหม ผิด! การคีย์วันที่ควรคีย์เป็น ค.ศ. หากต้องการเป็น พ.ศ. ให้จัดรูปแบบเอาครับพี่น้องครับ ไม่ใช่ไปบวกด้วย 198327 ครับ
      โปรดคำนึงว่าการแปลงวันที่ที่เป็น พ.ศ. ให้เป็น ค.ศ. นี่คือการแก้ไข เพราะวันที่ควรคีย์เป็น ค.ศ. และการนำ 198327 มาใช้แล้ว ได้วันที่ที่ตรงกันหรือไม่ ปัญหาไม่ใช่อยู่ที่ตัวเลข 198327 แต่โอกาสที่นำ 198327 ไปหักแล้วได้วันที่ที่ตรงกันนั้นมีมาก สำคัญที่การประยุกต์ใช้ เช่นถ้าได้วันที่ที่ไม่ตรงแล้วจะทำอย่างไร ใช้สูตรหรือฟังก์ชั่นใดช่วยได้ และถ้าใช้ 198327 หักแล้วได้วันที่ตรงกันก็ไม่จำเป็นต้องไปคิดฟังก์ชั่นให้ซับซ้อน
   
   
   
   


5. มาในเรื่องของเวลาบ้างใน 1 วันหรือ 24 ชม. แทนด้วยเลข 1 หรือก็คือ 24/24 หากคีย์ 1/24 แล้วจัดรูปแบบเป็นเวลาจะได้ 1:00:00 ทดลองคีย์เลขอื่นๆ สังเกตดูผล
   ทบทวนกันหน่อย
   
   
   1. 1 วัน 24 ชม.
   
   
   2. 1 ชม. 60 นาที
   
   
   3. 1 นาที 60 วินาที
   
   
   4. 1 วัน 1440 นาที (24 ชม. * 60 นาที)
   
   
   5. 1 วัน 86400 วินาที (24 ชม. * 60 นาที * 60 วินาที)
   
   
   
   


6. การคีย์เวลาต้องคีย์ตัวเลขคั่นด้วย : เสมอ เมื่อคีย์เวลาแล้วอยากรู้ว่าเป็นเลข Serial ใดก็ให้จัดรูปแบบเป็น General


7. การรวมเวลาที่เกิน 24 ชม. ต้องจัดรูปแบบของเซลล์ผลลัพธ์เป็น [h]:mm:ss หากไม่เอาส่วนที่เป็นนาที วินาที สามารถละไว้ได้


8. การแปลงเวลาในรูปแบบทศนิยมให้เป็นรูปแบบเวลา ยกตัวอย่างเช่น
   
   A1=1.5 ต้องการแปลงเป็น 1:30 ใช้สูตรดังนี้
   
   =Int(A1)/24+(A1-Int(A1))*60/1440
   
   ที่ต้องนำส่วนของนาทีคูณด้วย 60 ก่อนเพราะว่า 0.5 คือครึ่งหนึ่งของชม. เทียบบัญญัติไตรยางค์ได้ดังนี้
   
   
   1. 1 ชม. เท่ากับ 60 นาที
   
   
   2. 0.5 ชม. เท่ากับ 60/1*0.5 นาที ผลลัพธ์ คือ 30 นาที
   
   
   3. การทำนาทีให้เป็นวัน ก็ขึ้นไปอ่านจากหัวข้อที่ 5 ก็จะทราบว่าต้องนำ 1440 ไปหาร ก็เป็นอันเรียบร้อย
      
       เอ๋า...เอาเข้าไป ก็ในเมื่อรู้ว่า 1 วัน คือ 24/24 และรู้ว่า 1 ชม. คือ 1/24 แล้วทำไมไม่รู้ว่า 1.50 ชม. นั่นก็คือ 1.50/24 ไปทำให้ยากทำไมครับท่านผู้ชมครับ
      
      แล้วถ้า 1.30 ชม.ล่ะ จะแปลงเป็น 1:30 ชม. ได้อย่างไร?
      
      ง่ายมาก ก็เปลี่ยน . ให้เป็น : ครับ มาดูตัวอย่างกัน
      
      =Substitute(Text(A1,"0.00"),".",":")+0 หรือ
      
      =Int(A1)/24+(A1-Int(A1))*100/1440
      
      เนื่องจากเราทราบว่า 0.30 คือ 30 นาที ก็นำ 100 ไปคูณเพื่อให้เป็น 30 จากนั้นก็ทำให้เป็นวันโดยการหารด้วย 1440
      
      สังเกตเห็นอะไรไหมหนอ?
      
      
      1. ถ้าไม่เห็นก็โปรดสังเกตว่า ส่วนของชม.เราก็ทำให้เป็นวัน ส่วนของนาทีเราก็ทำให้เป็นวัน
      
      
      2. หลังจากคำนวณได้แล้วต้องจัดรูปแบบเป็นเวลา
      
      
      
      
   
   
   
   


9. ฟังก์ชั่นที่ควรศึกษาเกี่ยวกับวันเวลาได้แก่
   Datedif(), Hour(), Minute(), Secound(), Weekday(), Day(), Today(), Date(), Month(), Year() เป็นต้น