วิธีใช้วันที่และเวลา
วันที่และเวลา เป็นข้อมูลซึ่งใช้กันประจำ แทบหลีกเลี่ยงไม่ได้เลย โดยเฉพาะอย่างยิ่งสำหรับนักวางแผน เจ้าหน้าที่ขาย บริการลูกค้า เจ้าหน้าที่สินเชื่อ เจ้าหน้าที่บัญชี นักการเงิน ผู้บริหารการผลิต และผู้บริหารซึ่งทำงานเกี่ยวข้องกับกำหนดการ ใช้กำหนดเวลา ในการจัดตารางทำงาน ด้วย MS Excel 97 เป็นต้นมา สามารถรับข้อมูลวันที่และเวลาในช่วงตั้งแต่ 1 January 1900 จนสิ้นสุดปี 9999 โดยใช้ระบบตัวเลขพิเศษ เรียกว่า Date Serial Number (SN) กำกับวันที่และเวลาซึ่งถูกบันทึกลงไปในเซลล์ เพราะฉนั้น หัวใจของการใช้วันที่และเวลา อยู่ที่ความเข้าใจใน SN นี่เอง ส่วนเลขของวันเดือนปีและเวลา ซึ่งแสดงให้เห็นในเซลล์ เป็นแต่เพียงผลจากการจัด format ทำให้ Excel ตีความตัวเลข SN ออกมาเป็นภาษาที่มนุษย์เข้าใจ เมื่อเข้าใจในตัวเลข SN แล้ว จะช่วยให้ใช้ตัวเลข SN ในการคำนวณ หรือใช้ร่วมกับสูตรอื่นๆของ Excel ต่อไป
หลักการใช้วันที่และเวลา

a

ข้อกำหนดสำคัญไม่ได้อยู่ในโปรแกรม Excel โดยตัวเลือกใน Regional Settings ของ Control Panel (Start > Settings > Control Panel > Regional Settings > Region และ Short Date Style) เป็นส่วนสำคัญที่สุดของการใช้วันที่และเวลาให้ถูกต้อง ใช้จัดรูปแบบเริ่มต้น ว่าระบบการรับวันที่ในเครื่องคอมพิวเตอร์นั้นๆ จะรับค่าแบบวันเดือนปี เดือนวันปี หรือตามรูปแบบที่ผู้ใช้ต้องการ ทั้งนี้ขอแนะนำให้กำหนด Region เป็นของประเทศไทย และกำหนดรูปแบบของปีเป็นเลขสี่หลัก โดยใช้รูปแบบเป็นd/mm/yyyy หรือ dd/mm/yyyy
เมื่อต้องการบันทึกวันที่ลงไปในเซลล์ ให้พิมพ์ วันที่/เลขที่เดือน/เลขปีค.ศ.เต็ม 4 หลัก เช่น 12/10/2001 สังเกตว่าใช้เครื่องหมาย / แบ่งคั่นแต่ละส่วน และใช้เลขปีค.ศ.เต็ม 4 หลัก ห้ามใช้เลขปีพ.ศ.เด็ดขาด เพราะถ้าบันทึก 12/10/2544 จะหมายถึงวันที่ 12 เดือนตุลาคม ปีค.ศ.2544 ต้องรออีกตั้ง 543 ปีกว่าจะถึง
เมื่อต้องการบันทึกเวลา ให้พิมพ์ เลขที่ชั่วโมง:เลขที่นาที:เลขที่วินาที เช่น 23:30:45 หากไม่ต้องการใช้วินาที จะพิมพ์แค่ 23:30 ก็ได้ ค่าของเวลาใน Excel เริ่มจาก 0:00:00 ถึง 23:59:59 ไม่มีเลข 24 นาฬิกา เพราะจะถูกตีค่าเป็น 0:00:00 ของวันใหม่
หากต้องการใช้ช่วงเวลาตั้งแต่ 24 ชั่วโมงขึ้นไป ต้องกำหนด Format > Cells > Number > Custom > Type เป็น [h]:mm (ในทำนองเดียวกัน หากต้องการใช้ข่วงเวลาเกิน 60 นาที ให้กำหนด format [mm]:ss)
เมื่อพิมพ์วันที่หรือเวลาที่ Microsoft Excel รู้จัก จะถือว่าวันที่และเวลา เป็นตัวเลข
รูปแบบของเซลล์จะเปลี่ยนจากรูปแบบตัวเลขทั่วไป เป็นรูปแบบวันที่หรือเวลา
และจะจัดตำแหน่งชิดขวาเซลล์
Excel จะจัดเก็บวันที่ทั้งหมดเป็นลำดับตัวเลข และจัดเก็บเวลาทั้งหมดเป็นเศษทศนิยม โดยถือว่า SN = 1 หมายถึง 1 January 1900 และค่าที่เป็นเลขทศนิยม น้อยกว่า 1 เป็นตัวเลขแสดงสัดส่วนของวัน หากต้องการตรวจสอบค่า SN ให้ใช้ format General (หรือกด Ctrl+Shift+~) จะพบตัวเลข SN ปรากฏขึ้นแทนรูปแบบวันที่หรือเวลา ขอให้จำไว้ว่า ตัวเลข SN มีหน่วยเป็นวัน
ถ้า Microsoft Excel ไม่รู้จักรูปแบบวันที่หรือรูปแบบเวลา จะถือว่าวันที่และเวลา เป็นข้อความ
ซึ่งจะจัดตำแหน่งชิดซ้ายเซลล์ เช่น พิมพ์ 12 10 2001 ใช้วรรคคั่นแต่ละส่วนของวันที่ อย่างนี้ใช้ไม่ได้ จะพบว่าตำแหน่งชิดซ้ายเซลล์
เมื่อต้องการพิมพ์วันที่และเวลาในเซลล์เดียวกัน ให้แยกวันที่และเวลาด้วยช่องว่าง เช่น 12/10/2001 23:30
ถ้าต้องการใช้เวลา ตามระบบนาฬิกาสิบสองชั่วโมง ให้พิมพ์ช่องว่างแล้วตามด้วย AM หรือ PM (หรือ A หรือ P) มิฉะนั้นจะใช้ระบบนาฬิกาเวลายี่สิบสี่ชั่วโมง เช่น พิมพ์ 3:00 PM หมายถึง เวลาบ่ายสามโมง แต่ถ้าพิมพ์ 3:00 เวลาจะจัดเก็บเป็น 3:00 AM ซึ่งหมายถึง เวลาตีสาม

ศึกษาข้อมูลเพิ่มเติมได้จาก Excel Expert Training //xls.i.am

Format ของวันที่และเวลา

ใช้เมนูคำสั่ง Format > Cells > Number > Custom > Type แล้วกำหนดรูปแบบที่ต้องการลงไป จะใช้รูปแบบเฉพาะแต่ละส่วน หรือผสมรูปแบบหลายส่วนเข้าด้วยกันก็ได้ เช่น

d	ได้เลขวันที่ เช่น 9
dd	ได้เลขวันที่ เช่น 09
ddd	ได้ตัวอักษรย่อของวัน เช่น Mon
dddd	ได้ตัวอักษรเต็มของวัน เช่น Monday
m	ได้เลขเดือน เช่น 5
mm	ได้เลขเดือน เช่น 05
mmm	ได้ตัวอักษรย่อของเดือน เช่น Jun
mmmm	ได้ตัวอักษรเต็มของเดือน เช่น June
d/m	ได้เลขวันที่/เลขที่เดือน เช่น 9/5
d/m/yyyy	ได้เลขวันที่/เลขที่เดือน/ปีค.ศ. เช่น 9/5/2001
mmmm dd	ได้ชื่อเดือน เลขวันที่ เช่น May 09
รูปแบบผสมอื่นๆ เช่น dddd d, m/y, mm-dd'yy, yy/mm, d-mm-yyyy ฯลฯ แล้วแต่จะจับแต่ละส่วนมาใช้ร่วมกัน ทำได้อีกเยอะแยะ

Format แบบเต็มของระบบวันที่แบบอังกฤษ
dddd dd mmmm yyyy h:mm

ขอแนะนำให้ใช้รูปแบบวันที่แบบอังกฤษเท่านั้น

Format แบบเต็มของระบบวันที่แบบไทย
วววว dd ดดดด bbbb h:mm

ไม่แนะนำให้ใช้รูปแบบไทยๆนี้เลย
เพราะสามารถทำให้เกิดความสับสนในการใช้วันที่
ระหว่างปีค.ศ.ซึ่งใช้รูปแบบไทย
กับปีพ.ศ.ซึ่งใช้รูปแบบอังกฤษ

สูตรคำนวณหา Date Serial Number

สูตรกลุ่มนี้คืนค่าเป็นตัวเลข SN แล้วปรับรูปแบบให้เองเป็นส่วนของวัน เดือน ปี เวลา ตามแบบซึ่งกำหนดไว้ใน Regional Settings

เราจำเป็นต้องใช้สูตรคำนวณหา SN เพื่อใช้เป็นตัวเลขในการบวกลบคูณหาร กับตัวเลข SN อื่นๆ

=NOW()

คืนค่าเป็นวัน เดือน ปี และเวลาปัจจุบัน ตามเวลาซึ่งกำหนดไว้ในเครื่องคอมพิวเตอร์
ค่า SN มีค่าเป็นเลขจำนวนเต็มและมีเศษทศนิยม

=TODAY()

คืนค่าเป็นวัน เดือน ปี ตามเวลาซึ่งกำหนดไว้ในเครื่องคอมพิวเตอร์
ค่า SN มีค่าเป็นเลขจำนวนเต็ม ไม่มีเศษทศนิยม

=DATE(year,month,day)

คืนค่าเป็นวัน เดือน ปี ตามเลขที่ปี เลขที่เดือน และวันที่
ค่า SN มีค่าเป็นเลขจำนวนเต็ม ไม่มีเศษทศนิยม เช่น

	สูตรที่ใช้	ผลลัพธ์
	=DATE(2001,5,16)	16/5/2001
	=DATE(2001,6,0) เป็นสูตรหาวันที่สิ้นเดือน	31/5/2001
	=DATE(2001+1,5+2,16+3)	19/7/2002

สูตร Date นี้มีประโยชน์มากในการคำนวณหาวันเดือนปีสิ้นสุด นับจากวันเดือนปีที่กำหนด เช่น
=DATE(2001+1,5+2,16+3) ได้วันที่สิ้นสุดนับเพิ่มอีก 1 ปี 2 เดือน 3 วัน ซึ่งคือวันที่ 19/7/2002

=TIME(hour,minute,second)

คืนค่าเป็นเวลาชั่วโมง นาที วินาที ตามเลขที่ชั่วโมง เลขที่นาที และเลขที่วินาที
ค่า SN มีค่าเป็นเลขเศษทศนิยม เช่น

	สูตรที่ใช้	ผลลัพธ์
	=TIME(23,30,45)	23:30:45

สูตรคำนวณเปลี่ยน Date Serial Number เป็นวันที่เวลา

สูตรเหล่านี้ต้องอาศัยค่า SN ซึ่งมีอยู่แล้ว สมมติว่า เซลล์ A1 มีค่า 16/5/2001 23:30:45 (ซึ่งมีค่าเป็นตัวเลข SN = 37027.9796875) เมื่อใช้สูตรเปลี่ยน SN เป็นวันที่เวลา จะได้ตัวเลขของวันที่หรือเวลา ตามสูตรที่ใช้คำนวณ ผลลัพธ์ที่ได้ไม่เป็นตัวเลข SN อีกต่อไป ถูกเปลี่ยนเป็นตัวเลขมีค่าตามปกติตรงกับตัวเลขที่เห็นในเซลล์

=DAY(SN)

	สูตรที่ใช้	ผลลัพธ์
	=DAY(A1)	16

=MONTH(SN)

	สูตรที่ใช้	ผลลัพธ์
	=MONTH(A1)	5

=YEAR(SN)

	สูตรที่ใช้	ผลลัพธ์
	=YEAR(A1)	2001

=WEEKDAY(SN)

	สูตรที่ใช้	ผลลัพธ์
	=WEEKDAY(A1)	4
	กำหนดให้ 1 = วันอาทิตย์ 2 = วันจันทร์ 3 = วันอังคาร 4 = วันพุธ 5 = วันพฤหัส 6 = วันศุกร์ 7 = วันเสาร์

=HOUR(SN)

	สูตรที่ใช้	ผลลัพธ์
	=HOUR(A1)	23

=MINUTE(SN)

	สูตรที่ใช้	ผลลัพธ์
	=MINUTE(A1)	30

=SECOND(SN)

	สูตรที่ใช้	ผลลัพธ์
	=SECOND(A1)	45

ตัวอย่าง ต้องการคำนวณค่าแรง กำหนดให้ค่าแรงชั่วโมงละ 100 บาท

A1 ใส่ค่าวันเวลาเริ่มงาน 16/5/2001 7:00
A2 ใส่ค่าวันเวลาเลิกงาน 17/5/2001 12:00

A3 สร้างสูตร =A2-A1 จะได้ 1/1/1900 5:00

เปลี่ยน format A3 เป็น General จะแสดงตัวเลข 1.20833333333576
ตัวเลขนี้เป็น Date Serial Number มีหน่วยเป็นวัน

แต่ค่าแรง 100 บาท มีหน่วยเป็น บาทต่อชั่วโมง
ต้องเปลี่ยนเป็น บาทต่อวัน = 2400 บาทต่อวัน

A4 สร้างสูตร =A3*2400 จะได้คำตอบ
ค่าแรงจ่าย 2900 บาท

หากคำนวณหาจำนวนชั่วโมง
A5 สร้างสูตร =Trunc(A3) จะได้ 1 วัน
A6 สร้างสูตร =Hour(A3) จะได้ 5 ชั่วโมง
A7 สร้างสูตร =Minute(A3) จะได้ 0 นาที
A8 สร้างสูตร =(A5*24)+A6+A7/60 จะได้ 29 ชั่วโมง
หากนำไปคำนวณหาค่าแรงรวม =A8*100 จะได้ค่าแรงรวม 2900 เช่นกัน

หมายเหตุ

A5 สามารถใช้สูตร = Day(A3) แต่มีจุดอ่อนที่จำกัดจำนวนวันเพียง 31 วัน

A8 สามารถคำนวณหาจำนวนชั่วโมงได้อีกวิธีหนึ่ง = A3 *24

ตัวอย่างการใช้

ต้องการแปลง text ให้เป็นวันที่

แปลง 20100316 ให้เป็น 16/03/2010
=DATE((LEFT(cell,4)),(MID(cell,5,2)),(RIGHT(cell,2)))

         การหาจำนวนวัน
         นำวันที่มาลบกันเลย แล้วเปลี่ยน format เป็น general จะได้จำนวนวัน

หรือใช้สูตร
         =DateDif(วันเริ่ม,วันสิ้นสุด,"D")

ถ้าสิ้นสุดวันนี้ก็ใช้ TODAY() ได้เลย
         "D" เปลี่ยนเป็น "M","Y","YM","YD","MD" จะได้ผลลัพธ์เป็นจำนวนเดือนสะสม ปีสะสม เดือนที่เหลือเมือ่นำปีชนปี วันที่เหลือเมื่อนำเดือนชนเดือน

เครดิต:

SURIYA-BLOG

//suriyabox.blogspot.com/2010/03/excel.html

Excel วิธีใช้วันที่และเวลา

SURIYA-BLOG

Excel วิธีใช้วันที่และ เวลา