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

date & time

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

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

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

จริงๆ แล้ว Excel ทำงานเกี่ยวกับวันเวลาด้วยแนวคิดที่ง่ายมากๆ ครับ นั่นคือ มัน แทนวันที่ 1/1/1900 (1 มกราคม คศ. 1900) ด้วยเลข 1 แล้วมันก็แทนวันที่ 2/1/1900 ( 2 มกราคม คศ. 1900) ด้วยเลข 2.. ทำอย่างนี้ไปเรื่อยๆ

โดย Excel จะเทียบปฏิทินกับตัวเลขที่เรียงไล่ลำดับซึ่งเรียกว่า Serial Number ตัวอย่างเช่น

วันที่   คือเลข  หมายเหตุ
1/1/1900 => 1
2/1/1900 => 2
3/1/1900 => 3
4/1/1900 => 4
30/1/1900 => 30
31/1/1900 => 31
ขึ้นเดือนใหม่… 1/2/1900 => 32 แม้ข้ามเดือน ก็จะไล่เลขต่อไปเรื่อยๆ…
2/2/1900 => 33
มายุคปัจจุบัน… 20/11/2013 => 41598
21/11/2013 => 41599

ในทำนองเดียวกัน ถ้าเลขนั้นลงละเอียดถึงระดับของเวลา (เช่น 14 นาฬิกา 29 นาที 30 วินาที) การแทนเลขจะแทนด้วยทศนิยมแทน
โดยเลข 0.5 คือเวลาเที่ยงตรง (เที่ยงวัน 12:00) ดังนั้น หากเราเห็น เลข 1.5 มันคือวันที่  1/1/1900 เวลา 12:00:00 

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

สมการง่ายๆ

Date part + Time part = จำนวนเต็ม + ทศนิยม
วันที่ เวลา คือเลข  หมายเหตุ
1/1/1900 0:00 น. => 1.00  0 คือ เวลา 0.00 น.
1/1/1900 12:00 น. => 1.50 0.5 คือเที่ยงวัน (12:00) เพราะ 0.5 วัน x 24 ชม./วัน = 12 ชม.
1/1/1900 18:00 น. => 1.75 0.75 คือ 6 โมงเย็น (18:00)  เพราะ 0.75 วัน x 24 ชม./วัน = 18 ชม.
2/1/1900 0:00 น. => 2.00
1/1/1900 2:24 น. => 1.10 0.1 คือ 2 ชม. 24 นาที เพราะ 0.1 วัน x 24 ชม./วัน = 2.4 ชม. => แบ่งเป็น 2 ชม. + 0.4 ชม.
และ 0.4 ชม. คือ 24 นาที เพราะ 0.4 ชม. X 60 นาที/ชม. = 24 นาที
รวมเป็น 2 ชม. 24 นาที

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

กรณีที่ตั้งค่า Regional Setting ใน Control Panel เป็น Thai จะเห็นแบบนี้ (คนส่วนใหญ่จะตั้งเป็นค่านี้อยู่แล้วแหละ)

รูปแบบวันที่
ที่
Excel รู้จัก
สิ่งที่พิมพ์ลงไป Excel รับรู้ว่าเป็นวันที่
dd/mm/yyyy 21/10/2016 21/10/2016
dd-mm-yyyy 21-10-2016 21/10/2016
yyyy/mm/dd 2016/10/21 21/10/2016
yyyy-mm-dd 2016-10-21 21/10/2016
dd/mm 21/10 21/10/ปีปัจจุบัน
dd-mm 21-10 21/10/ปีปัจจุบัน
dd-mmm-yyyy 21-Oct-2016 21/10/2016

อยากรู้ว่าวันที่ไหนคือเลขอะไร

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

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

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

  • A1 = 3/11/2013 => คือเลข 41581
  • A2 =30/10/2013 => คือเลข 41577
  • A1-A2 = 4
    • เพราะเปรียบเสมือน =41581-41577 นั่นเอง

ในทางกลับกัน หากเราเอาเลข 10 ไปบวกด้วยค่าในช่อง A1 เช่น

  • ใน A3 ใส่ว่า =A1+10 =>จะได้ 13/11/2013
    • เพราะเป็นเหมือนการบวกค่า 41581 ด้วย 10 ได้ 41591
    • จากนั้น Excel ก็จะไปเทียบกับปฏิทินของมันว่าคือวันที่เท่าไหร่ ซึ่งก็คือ 13/11/2013 นั่นเอง

ฟังก์ชั่นเกี่ยวกับวันที่ที่น่าสนใจ

สมมติว่าช่อง A1 มีค่าเป็น 24/11/2013, สมมติว่าช่อง A2 มีค่าเป็น 15/8/2010

  • DATE ใช้ประกอบร่าง ตัวเลขปี เลขเดือน เลขวัน ให้กลายเป็นรูปแบบของวันที่
    DATE(year, month, day)

เช่น =DATE(2013,11,24) => 24/11/2013 (หรือ 41602 นั่นเอง)

Tips : ตัวนี้เราสามารถใส่ day เป็นเลขติดลบ หรือ เลขที่มากกว่า 31 วัน ได้ด้วยนะครับ วันที่มันจะเลื่อนกลายเป็นของเดือนอื่นไป และที่เจ๋งคือ ถ้าเราใส่เป็นเลข 0 มันจะกลายเป็นวันสุดท้ายของเดือนก่อนครับ !! => ตัวอย่าง http://www.inwexcel.com/last-day-of-month/

  • YEAR ใช่หาว่าเลขวันที่ที่กำหนด อยู่ในปีที่เท่าไหร่
    YEAR(serial_number)

    เช่น = YEAR(A1) จะได้ค่า 2013

  • MONTH ใช่หาว่าเลขวันที่ที่กำหนด อยู่ในเดือนที่เท่าไหร่
    MONTH(serial_number)

    เช่น = MONTH(A1) จะได้ค่า 11

  • DAY ใช่หาว่าเลขวันที่ที่กำหนด อยู่ในวันที่เท่าไหร่
    DAY(serial_number)

    เช่น = DAY(A1) จะได้ค่า 24

  • EDATE ใช้บวกจำนวนเดือนเพิ่มเข้าไปในวันที่ที่กำหนดแบบเดือนชนเดือน เช่น
    EDATE(start_date,months)

    ให้ C1 = 31/01/2013

    • EDATE(C1,1) => 28/2/2013 (ไม่มีวันที่ 31/2/2013 จึงปรับเป็นวันสิ้นเดือนให้)
    • EDATE(C1,2) => 31/3/2013
    • EDATE(C1,3) => 30/4/2013 (ไม่มีวันที่ 31/4/2013 จึงปรับเป็นวันสิ้นเดือนให้)
    • EDATE(C1,4) => 31/5/2013
    • EDATE(C1,-1) => 31/12/2012 (ติดลบแล้วจะถือว่าย้อนเวลากลับไปในอดีต)
  • NETWORKDAYS  ใช้หาว่าวันสองวันทีกำหนดห่างกันเท่าไหร่โดยไม่นับวันหยุด (สามารถเพิ่มวันหยุดพิเศษที่ไม่ใช่เสาร์อาทิตย์ได้)
    NETWORKDAYS(start_date, end_date, 
    [holidays]) *ตัว Holidays เราสามารถเลือกเป็นช่วงวันที่ที่เป็นวันหยุดพิเศษได้ ถ้าไม่ใส่ ก็จะถือว่าหยุดแต่เสาร์อาทิตย์

    เช่น D1= 28/2/2013, D2 = 4/3/2013
    =NETWORKDAYS( D1,D2) = 3 เพราะ นับ 28 กพ (พฤหัส) , นับ 1 มีนา (ศุกร์) , ไม่นับ 2 มีนา (เสาร์), ไม่นับ 3 มีนา (อาทิตย์), นับ 4 มีนา (จันทร์)

เป็นอย่างไรบ้างครับกับการทำงานเกี่ยวกับวันที่และเวลาใน Excel หากอ่านแล้วยังสงสัยตรงไหนก็ตามได้ตลอดเลยนะครับ (มาคุยกันใน Facebook ก็ได้ครับ)