ข้อมูลประเภทวันที่และเวลา (Date-Time Format)

ช่วงนี้มีคำถามที่เกี่ยวข้องกับ รูปแบบข้อมูลประเภทวันที่และเวลา (Date-Time) เข้ามาบ่อยมาก และส่วนใหญ่จะพบว่า ผู้ใช้หลายคนยังขาดความเข้าใจที่ถูกต้อง ในเรื่องของวันที่และเวลาใน Excel วันนี้ผมจึงขออธิบายเกี่ยวกับเรื่องนี้ก็แล้วกัน



Excel เก็บข้อมูลวันที่ (Date) อย่างไร?

ใน Excel ข้อมูลที่เราเห็นเป็น วันที่ (Date) นั้น ค่าที่จริงแล้วเป็นเลขจำนวนเต็ม หรือที่เรียกว่า เลขอนุกรมเวลา (Serial date) ที่ถูกจัดรูปแบบให้แสดงเป็นวันที่ ดังที่เราเห็นกัน

ใน Excel for Windows เลขอนุกรมเวลา เริ่มต้นนับลำดับที่หนึ่ง จากวันที่ 1 มกราคม ค.ศ.1900 (สำหรับ Excel for Mac จะเริ่มนับหนึ่งจากวันที่ 1 มกราคม ค.ศ.1904) และบวกเพิ่มขึ้นมาทีละหนึ่งวัน ซึ่งวันที่ 1 กุมภาพันธ์ ค.ศ.1900 จึงเป็นวันลำดับที่ 32 และถ้าวันนี้ เป็นวันที่ 27 กันยายน ค.ศ.2010 ก็จะเป็นวันลำดับที่ 40448 นั่นเอง ดังนั้น ถ้าอยากทราบว่าอีก 120 วันถัดจากนี้ไป จะตรงกับวันที่เท่าไร ก็เอา 120 เข้าไปบวกกับวันปัจจุบัน (40448 + 120) จะได้ผลลัพธ์เท่ากับ 40568 ซึ่งก็คือวันที่ 25 มกราคม ค.ศ.2011


รูปที่ 1 ตารางเปรียบเทียบเลขอนุกรมเวลา (Serial date) กับวันที่ (Date format)


ผู้อ่านคงสงสัยว่า จะรู้ได้อย่างไรว่าวันที่ไหน ตรงกับเลขลำดับที่เท่าไร? จริงๆ ก็ไม่ยากอะไรหรอกครับ แค่เปิด Excel ขึ้นมา ลองพิมพ์สูตร ณ เซลล์ใดๆ =TODAY() แล้วกด Enter ซึ่งจะได้วันที่ปัจจุบัน

ให้คลิกขวาที่เซลล์นั้น เลือก Format cells.. (จัดรูปแบบเซลล์..) คลิกที่รูปแบบ General (ทั่วไป) แล้วคลิก OK (ตกลง) ก็จะเห็นว่า วันที่ที่เราเห็นนั้น ตอนนี้กลายเป็นเลขจำนวนเต็มค่าหนึ่ง

ด้วยเหตุผลนี้เอง เราจึงสามารถหาระยะห่างระหว่างวันที่สองค่าได้โดยนำมาลบกัน เช่น การหาอายุงานหรือการหาอายุตัว เป็นต้น

ทำนองเดียวกัน ถ้าต้องการทราบว่าอีกเท่านั้นเท่านี้วัน จะตรงกับวันที่เท่าไร ก็สามารถนำจำนวนวัน ไปบวกกับวันที่ที่เป็นวันตั้งต้น ก็จะทราบวันที่ที่ต้องการ เช่น การหาวันที่ครบทดลองงาน หรือการหาวันที่ครบกำหนดชำระเงิน ฯลฯ เป็นต้น

*** กรุณาอ่านเพิ่มเติมเรื่อง "การกำหนดรูปแบบเซลล์ประเภทวันที่ (Date format)" 



Excel เก็บข้อมูลเวลา (Time) อย่างไร?

อย่างที่ทราบไปในหัวข้อที่แล้วว่า วันที่ใน Excel นั้น ถูกเก็บอยู่ในรูปแบบของเลขจำนวนเต็ม การเพิ่มขึ้นหรือลดลงของเลขจำนวนเต็มนั้น หมายถึง จำนวนวัน ที่เพิ่มขึ้นหรือลดลงนั่นเอง

ในเมื่อ 1 วัน มี 24 ชั่วโมง, ดังนั้น
1/2 วัน (หรือครึ่งวัน) ก็มี 12 ชั่วโมง,
1/4 วัน มี 6 ชั่วโมง และ 1/8 วัน มี 3 ชั่วโมง เป็นต้น

ในทางกลับกัน 1 ชั่วโมง = 1/24 วัน (หรือได้ผลลัพธ์เป็นเลขทศนิยม 0.0416666666666667)
ครึ่งชั่วโมง = 1/48 วัน,
15 นาที = 1/96 วัน ,
1 นาที = 1/1440 วัน (1 วันมี 1440 นาที)

โดยสรุปก็คือ เลขจำนวนเต็มหมายถึง วัน (Date) และเลขทศนิยมหมายถึง เวลา (Time) นั่นเอง

ถ้าเลขอนุกรมเวลา 40448 หมายถึงวันที่ 27 กันยายน ค.ศ.2010,
เลขอนุกรมเวลา 40448.5 ก็หมายถึง วันที่ 27 กันยายน ค.ศ.2010 เวลา 12:00 น. หรือเที่ยงวัน,
ทำนองเดียวกัน 40448.75 ก็หมายถึง วันที่ 27 กันยายน ค.ศ.2010 เวลา 18:00 น. หรือ 6 โมงเย็นนั่นเอง

ทดสอบด้วยการพิมพ์สูตร ณ เซลล์ใดๆ =NOW() แล้วกด Enter จะได้วันที่และเวลาปัจจุบัน
ให้คลิกขวาที่เซลล์นั้น เลือก Format cells.. (จัดรูปแบบเซลล์..) คลิกที่รูปแบบ General (ทั่วไป) แล้วคลิก OK (ตกลง) จะเห็นว่า วันที่และเวลาที่เราเห็นนั้น ตอนนี้กลายเป็นตัวเลขค่าหนึ่งที่มีทศนิยมด้วย



การพิมพ์ข้อมูลรูปแบบวันที่ (Date) ใน Excel

เนื่องจาก Excel เริ่มนับวันที่ 1 มกราคม ค.ศ.1900 (หรือ 1/1/1900) เป็นวันแรก และนับวันเพิ่มขึ้นต่อเนื่องมาทีละ 1 วันจนถึงปัจจุบัน (และนับต่อไปในอนาคต) และแปลงรูปแบบเลขอนุกรมเวลา ให้เป็นวันที่ในแบบปี ค.ศ. ดังนั้น เวลาที่เราจะพิมพ์ข้อมูลในรูปแบบวันที่ต้องพิมพ์เป็นปี ค.ศ. เท่านั้น (ถ้าพิมพ์ 27/9/2553 Excel จะแปลความหมายเป็น วันที่ 27 กันยายน ค.ศ.2553)

การที่จะพิมพ์แบบ d/m/yyyy หรือจะพิมพ์แบบ m/d/yyyy หรือจะรูปแบบอื่นๆ ก็สุดแล้วแต่การตั้งค่าในตัว Windows ของเครื่องนั้นๆ เช่น ถ้า Windows เครื่องนั้นกำหนดรูปแบบเป็น m/d/yyyy หากเราพิมพ์วันที่ 12/9/2010 (ในความต้องการคือ 12 กันยายน ค.ศ.2010) Excel จะเข้าใจว่าเป็นวันที่ 9 เดือน 12 (ธันวาคม) ค.ศ.2010

ในทำนองเดียวกัน ถ้าเราพิมพ์วันที่ 27/9/2010 (ในความต้องการคือ 27 กันยายน ค.ศ.2010) Excel จะเข้าใจว่าเป็น วันที่ 9 เดือน 27 ค.ศ.2010 แต่เนื่องจากเดือน 27 ไม่มีจริง Excel ก็จะถือว่า ข้อมูลดังกล่าวไม่ใช่รูปแบบวันที่ (Date) แต่ถือเป็นข้อความทั่วไป (Text) ดังนั้น จะสังเกตว่า 27/10/2010 จะชิดขอบเซลล์ด้านซ้าย (ซึ่งปกติแล้วข้อมูลประเภทตัวเลข วันที่ และเวลา จะชิดขอบเซลล์ด้านขวา) ดังนั้น ต้องระวังในเรื่องนี้ให้ดีด้วย

เราสามารถตรวจสอบได้ง่ายๆ ว่าเครื่องที่เราใช้งานนั้น มีการตั้งค่ารูปแบบวันที่อย่างไร โดยการพิมพ์สูตร ณ เซลล์ใดๆ =TODAY() แล้วกด Enter จะได้วันที่ปัจจุบัน ซึ่งจะมีรูปแบบวันที่ตามค่าปกติของเครื่องนั้นๆ ถ้ารูปแบบเป็น d/m/yyyy อยู่แล้ว เราก็สามารถพิมพ์วันที่ในรูปแบบ d/m/yyyy ได้เลย

แต่ถ้ารูปแบบเป็น m/d/yyyy เวลาที่เราพิมพ์ข้อมูลวันที่ เราก็ต้องพิมพ์ในรูปแบบ m/d/yyyy ด้วยเช่นกันแต่สามารถจัดรูปแบบเซลล์ ให้แสดงผลเป็น d/m/yyyy ได้ (แต่ค่าจริงๆ ยังคงเป็นแบบ m/d/yyyy เช่นเดิม)

หากต้องการตั้งค่ารูปแบบวันที่ใหม่ ให้เป็นรูปแบบที่เราต้องการอย่างถาวร เช่น d/m/yyyy ให้เข้าไปกำหนดที่ Control Panel ของ Windows เครื่องนั้นๆ

รูปที่ 2 แสดงการตั้งค่ารูปแบบวันที่ใน Control Panel ของ Windows



การพิมพ์ข้อมูลรูปแบบเวลา (Time) ใน Excel

ข้อมูลที่เป็นประเภทเวลา (Time) ให้พิมพ์ในรูปแบบ h:mm หรือ h:mm:ss เท่านั้น เช่น 8:30 หรือ 17:30 เป็นต้น (ให้ใช้เครื่องหมายจุดคู่ หรือที่เรียกว่า Colon) ห้ามพิมพ์ในรูปแบบทศนิยม เหมือนกับข้อมูลประเภทตัวเลขทั่วไป เช่น 8.30 หรือ 17.30 เพราะมันคนละความหมาย



การคำนวณเกี่ยวกับวันที่ (Date Calculate)

เนื่องจากข้อมูลประเภทวันที่นั้น แท้จริงแล้วก็คือเลขจำนวนเต็ม ดังนั้น ก็นำมาบวก-ลบ กันได้เหมือนเลขคณิตทั่วไป ผลลัพธ์ที่ได้จะเป็น "จำนวนวัน" เช่น 1/10/2010 (40452) ลบด้วย 1/9/2010 (40422) จะได้ผลลัพธ์เป็น 30 วัน

การบวกหรือลบ ก็ใช้หลักคณิตศาสตร์ทั่วไป คือไม่นับตัวตั้งต้น เช่น 5+3 หมายความว่า มีอยู่ห้า เอามาเพิ่มอีกสาม รวมกันเป็นแปด หรือ 5-3 ก็หมายความว่า มีอยู่ห้า เอาออกไปสาม เหลือสอง

แต่ในงานบางอย่าง เช่น การหาอายุงาน หรือการหาจำนวนวัน เช่น เริ่มงานวันที่ 1/9/2010 ถึงวันที่ 10/9/2010 ถามว่ามีอายุงานกี่วัน? ทุกท่านคงตอบได้ทันทีว่า อายุงานคือ 10 วัน เพราะเรานับวันแรกที่เริ่มทำงานด้วย

แต่ถ้าเราให้ Excel คำนวน 10/9/2010 (40431) ลบด้วย 1/9/2010 (40422) จะได้ผลลัพธ์แค่ 9 วันเท่านั้น ดังนั้น เมื่อต้องหาระยะเวลาเป็นจำนวนวัน ให้บวกเพิ่มอีก 1 วัน ตามเหตุผลที่กล่าวมา



การคำนวณเกี่ยวกับเวลา (Time Calculate)

ถ้าเราพิมพ์ข้อมูลเวลา ในรูปแบบของเวลา เช่น h:mm ดังที่กล่าวมาข้างต้น เราก็สามารถนำค่าเวลานั้น มาบวกหรือลบกันทันที และผลลัพธ์ก็จะถูกจัดรูปแบบเป็นเวลาโดยอัตโนมัติ เช่น เริ่มงานเวลา 8:15 เลิกงานเวลา 12:00 เมื่อจะหาว่ามีเวลาทำงานกี่ชั่วโมง ก็สามารถคำนวณได้จาก 12:00 - 8:15 จะได้ผลลัพธ์ 3:45

แต่ถ้าเราพิมพ์รูปแบบเป็นทศนิยม เช่น 12.00 - 8.15 จะได้ผลลัพธ์เป็น 3.85 ซึ่งไม่ถูกต้อง เพราะเป็นคนละรูปแบบกัน

กรณีที่ผลรวมของเวลา เกินกว่า 24 ชั่วโมงขึ้นไป Excel จะปัดขึ้นเป็นจำนวนวัน และจะแสดงเฉพาะเศษของเวลาที่ไม่เต็มวันเท่านั้น เช่น 8:00 + 12:00 + 6:25 รวมกันแล้วได้ 26 ชั่วโมง 25 นาที Excel จะแสดงผลลัพธ์เป็น 2:25 แต่ถ้าต้องการให้แสดงเป็น 26:25ต้องจัดรูปแบบเซลล์เป็น [h]:mm



การคำนวณข้อมูลเวลา (Time) กับตัวเลข (Number)

ขอยกตัวอย่างจากหัวข้อที่แล้ว เช่น เริ่มงาน 8:15, เลิกงาน 12:00, ดังนั้น จำนวนชั่วโมงทำงานคือ 12:00 - 8:15 = 3:45 ชั่วโมง สมมติว่า เราจ่ายค่าตอบแทนชั่วโมงละ 30 บาท ถามว่าจะได้ค่าตอบแทนทั้งหมดกี่บาท?

ตรงนี้แหละครับที่หลายคนสงสัย เพราะเมื่อเอา 3:45 ชั่วโมง คูณกับ 30 บาทแล้ว ทำไม? ผลลัพธ์จึงผิด เพราะผลลัพธ์ที่ได้คือ 4.6875 บาท แทนที่จะเป็น 112.50 บาท ทั้งนี้ก็เป็นเพราะ รูปแบบของข้อมูลนั่นเอง

ใน Excel นั้น ค่าจริงๆ ของข้อมูล กับรูปแบบที่เราเห็น อาจจะไม่ใช่ค่าเดียวกันเสมอไป อย่างที่บอกไปแล้วว่า เลข 1 หมายถึง 1 วัน (หรือ 24 ชั่วโมง) ดังนั้น เวลา 3:45 ชั่วโมง ก็ย่อมมีค่าไม่ถึง 1 วัน ซึ่งถ้าเปลี่ยนรูปแบบเวลาของ 3:45 ให้เป็นตัวเลขทั่วไป ก็จะเห็นว่ามีค่าเพียง 0.15625 เท่านั้น แน่นอนว่า เมื่อนำ 0.15625 ไปคูณกับ 30 จึงได้ผลลัพธ์เท่ากับ 4.6875 นั่นเอง

แล้วต้องคำนวณอย่างไร?

ถ้าคิดแบบหลักการพื้นฐาน ก็คือแยกจำนวนชั่วโมงและนาทีออกมาให้เป็นจำนวนเต็ม แล้วจึงนำมาคูณกับอัตราค่าตอบแทนต่อชั่วโมง(ซึ่ง Excel ก็มีฟังก์ชันที่ใช้แยกจำนวนชั่วโมง-นาที) แต่วิธีการง่ายๆ ก็คือ ให้เอาผลลัพธ์นั้นคูณด้วย 24 อีกครั้งหนึ่ง ก็จะได้ตัวเลขที่ถูกต้อง เช่น 3:45 x 30 x 24 เท่ากับ 112.50 บาท


รูปที่ 3 ตัวอย่างการคำนวณข้อมูลเวลา (Time) กับตัวเลขทั่วไป (Number)



หักเวลาพัก 1 ชั่วโมง จะเขียนสูตรอย่างไร?

เป็นอีกคำถามหนึ่งที่ถามเข้ามาบ่อย เช่น เข้างาน 8:00 เลิกงาน 17:00 เมื่อคำนวณหาจำนวนชั่วโมงการทำงานแล้ว ต้องหักเวลาพักกลางวัน 1 ชั่วโมงด้วย จะเขียนสูตรอย่างไรให้ได้ผลลัพธ์ที่ถูกต้อง

ส่วนใหญ่มักจะตั้งสูตรกันแบบนี้ 17:00 - 8:00 - 1 ซึ่งแน่นอนว่า ผลลัพธ์ที่ได้ ย่อมไม่ถูกต้อง ทั้งนี้เพราะเลข 1 ที่นำไปลบนั้น เป็นจำนวนเต็ม หรือหมายถึง 1 วัน (24 ชั่วโมง) นั่นเอง

ดังนั้น วิธีที่ถูก ต้องเขียนสูตรแบบนี้ครับ 17:00 - 8:00 - 1:00 หรือ 17:00 - 8:00 - (1/24) จึงจะได้ผลลัพธ์เป็น 8:00 ชั่วโมง


ข้อมูลเพิ่มเติม : 

ฟังก์ชันด้านวันที่และเวลา 
http://office.microsoft.com/th-th/excel-help/HP010079181.aspx?CTT=1