สูตร excel บวก ลบ วันที่ ปัจจุบัน

เรื่องของวันและเวลา

วันที่และเวลาเป็นสิ่งที่เราอาจต้องใช้มันในการทำงานอยู่บ่อยๆ ใน Excel
เช่น หากว่าเราเอาวันที่สองวันมาลบกัน เราก็จะรู้ว่าทั้งสองวันห่างกันกี่วัน???
แต่จริงๆแล้ว Excel มันทำงานยังไงกันแน่?? ทำไมเอาวันเวลามาลบกันได้นะ??

หากเราเข้าใจการทำงานของ Excel เกี่ยวกับวันและเวลาแล้ว เราจะประยุกต์ใช้มันได้มากมายมหาศาลเลย ซึ่งบทความนี้จะเป็นผู้พาคุณเข้าสู่โลกของวันและเวลาของ Excel เองครับ

รูปแบบวันที่ ที่ Excel รู้จัก

กรณีที่ตั้งค่า Regional Setting ใน Control Panel เป็น Thai จะทำให้กรอกเป็น วัน/เดือน/ปี ได้แบบนี้  (ถ้าตั้งค่าเป็นอย่างอื่น อาจต้องกรอกเป็น เดือน/วัน/ปี ซึ่งอาจทำให้เรากรอกผิด เพราะคนไทยไม่คุ้นกับการกรอกแบบนี้)

Excel มองวันและเวลาเป็นตัวเลขธรรมดาๆ

จริงๆ แล้ว Excel ทำงานเกี่ยวกับวันเวลาด้วยแนวคิดที่ง่ายมากๆ ครับ นั่นคือ

วันที่คือจำนวนเต็ม

Excel จะแทนวันที่ 1/1/1900 (1 มกราคม คศ. 1900) ด้วยเลข 1
แล้วมันก็แทนวันที่ 2/1/1900 ( 2 มกราคม คศ. 1900) ด้วยเลข 2..

ทำอย่างนี้ไปเรื่อยๆ (วันเพิ่ม 1 วัน เลขเพิ่ม 1 หน่วย)

จนถึงวันในยุคปัจจุบัน จะได้ตัวเลขประมาณ 4 หมื่นกว่าๆ

เวลา คือ ทศนิยม

ทำนองเดียวกัน ถ้าเลขนั้นลงละเอียดถึงระดับของเวลา การแทนเลขจะแทนด้วยทศนิยมแทน
ถ้าหากเวลาผ่านไป 1 วัน เลขจะเพิ่มขึ้น 1 หน่วย
เมื่อเวลาผ่านไป ครึ่งวัน (เช่น ตอนเที่ยงตรง) เลขก็จะเพิ่มขึ้น 0.5 หน่วย นั่นเอง

ดังนั้น หากเราเห็น เลข 1.5 มันคือวันที่  1/1/1900 เวลา 12:00:00 

พอเริ่มเห็นภาพแล้วใช่มั๊ยครับ เรามาลุยต่อกันเลยครับ

อยากรู้ว่าวันที่/เวลา มีค่าที่แท้จริงคือเลขอะไร?

ให้ลองเปลี่ยน Format จาก Date => General หรือ Number ดูก็จะรู้ครับ

การคำนวณเกี่ยวกับวันที่

เมื่อเรารู้อย่างนี้แล้ว หากเราเอาวันที่ 2 ช่องลบกัน เราก็จะรู้ระยะห่างของวันทั้งสองได้เลย โดยที่หน่วยออกมาเป็น วัน เช่น

การคำนวณเวลา และการแปลงหน่วย

สมมติเรามีข้อมูลว่า…

เริ่มทำงาน ด้วยวันที่/เวลาในช่อง B1 เสร็จงาน ด้วยวันที่/เวลาในช่อง B2

อยากรู้ว่า ใช้เวลาทั้งหมดกี่นาที ในช่อง B3 ยังไง?

เมื่อ Excel แทนวันที่ด้วยจำนวนเต็ม ดังนั้น เวลา (ซึ่งคือส่วนหนึ่งของวัน) ก็จะถูกแทนด้วยเลขทศนิยมนั่นเอง เช่น

• วันที่ 16/10/2016 เวลา 0:00 คือเลข 42659

ทีนี้ถ้าเรากรอกเป็น 16/10/2016  13:25:00 แล้วลองเปลี่ยนเป็น Number จะได้เป็นเลข 42659.5590277778 (ลองเพิ่มทศนิยมดู) ซึ่งจริงๆ แล้วมันก็คือการแปลงวัน+เวลา ชั่วโมงและนาที ให้มีหน่วยเป็น “วัน” นั่นเอง

ดังนั้นถ้าเรามีเวลาเริ่มและจบแล้ว เราจะหาเวลาที่ผ่านไปได้ง่ายมากๆ โดยเอาค่าทั้งสองมาลบกัน

เช่น เอา B2-B1 จะได้ 1.784722222 แบบนี้ ซึ่งจะมีหน่วยเป็นวัน

ถ้าหากโจทย์ต้องการคำตอบที่หน่วยเป็นนาที เราก็แค่ต้องแปลงหน่วยจากวันเป็นนาที ซึ่งง่ายมากๆ

แค่เพียงเรารู้หลักการของการแปลงหน่วยเท่านั้น นั่นคือ การทำให้หน่วยตัดกันจนให้ได้ผลลัพธ์เป็นหน่วยสุดท้ายที่ต้องการ  โดยการแทนเลข *1 ด้วยอัตราส่วนที่เป็นความจริง เช่น

• 1 วัน = 24 ชม. หรือ 1 = 24 ชม./วัน
• 1 ชม. = 60 นาที หรือ 1 = 60 นาที/ชม.

ดังนั้น (B2-B1) วัน คือ

• = (B2-B1) วัน * 1 * 1 (คูณ 1 ค่าไม่เปลี่ยน ถือว่าทำแล้วค่ายังคงถูกต้อง)
• = (B2-B1) วัน * 24 ชม./วัน * 60 นาที/ชม.
  โดยที่จะเห็นว่าหน่วยสามารถตัดกันได้ จนเหลือแค่หน่วยนาที

• สุดท้ายจะได้ว่า เวลาที่ผ่านไปเป็นหน่วยนาที คำนวณได้จาก
• = (B2-B1)*24*60 นาที

เห็นมั้ยครับ? การคำนวณเรื่องวันที่และเวลาไม่ได้ยากอย่างที่คิดเลย แค่ต้องเข้าใจ Concept ของมันเท่านั้นเอง

ใครสนใจเรื่องวันที่และเวลา อย่าลืมอ่านต่อในบทความเรื่อง ฟังก์ชันเกี่ยวกับวันที่และเวลา นะครับ

แชร์ความรู้ให้เพื่อนๆ ของคุณ