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

การแปลบทความ การแปลบทความ
หมายเลขบทความ (Article ID): 214094 - ผลิตภัณฑ์ที่เกี่ยวข้องในบทความนี้
 

สำหรับ Microsoft Office Excel 2007 ไปที่ วิธีใช้แบบออนไลน์ของ Office Online Excel 2007 และเว็บเพจวิธีใช้
ขยายทั้งหมด | ยุบทั้งหมด

เนื้อหาบนหน้านี้

สรุป

บทความแบบทีละขั้นตอนนี้อธิบายวิธีใช้ฟังก์ชันวันที่และเวลาที่มีอยู่แล้วภายในของ Microsoft Excel เพื่อคำนวณวันที่และเวลาแบบซับซ้อน

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

ในระบบนี้ เลขอนุกรม 1 จะหมายถึงวันที่ 1/1/1900 12:00:00 a.m. เวลาจะถูกเก็บเป็นเลขทศนิยมระหว่าง .0 และ .99999 โดยที่ .0 คือ 00:00:00 และ .99999 คือ 23:59:59 เศษส่วนจำนวนเต็มวันที่และทศนิยมเวลาสามารถรวมกันเพื่อสร้างเลขที่มีทศนิยมและส่วนที่เป็นจำนวนเต็มได้ ตัวอย่างเช่น เลข 32331.06 ใช้แทนวัน และเวลา 7/7/1988 1:26:24 a.m.

เพื่อช่วยให้คุณคำนวณวันที่และเวลาแบบซับซ้อนได้ Excel จึงมีฟังก์ชันวันที่และเวลาที่มีอยู่แล้วภายในหลายแบบ

ฟังก์ชัน TODAY ( ) และฟังก์ชัน NOW ( )

ฟังก์ชัน TODAY จะส่งกลับเลขของวันที่วันนี้ตามเวลาของระบบของคุณ และไม่ได้รวมถึงเวลา ฟังก์ชัน NOW จะส่งกลับเลขของวันที่วันนี้ และรวมถึงเวลาด้วย

วิธีการเรียงลำดับวันที่ตามเลขอนุกรม

ใน Excel วันที่จะถูกจัดเรียงตามเลขอนุกรมของวันที่แทนการเรียงลำดับตามเลขที่แสดง ดังนั้น เมื่อคุณเรียงลำดับวันที่ใน Excel คุณอาจไม่ได้รับผลลัพธ์ที่คุณคาดหวังไว้

ตัวอย่างเช่น ถ้าคุณเรียงลำดับชุดของวันที่ที่แสดงในรูปแบบวันที่ mmmm (เพื่อให้มีการแสดงเฉพาะเดือน) เดือนก็จะไม่เรียงลำดับตามตัวอักษร แต่วันที่จะถูกเรียงลำดับตามเลขอนุกรมของวันที่แทน

วิธีการเปรียบเทียบวันที่ ตาม เลขอนุกรม

เนื่องจากเลขอนุกรมจะใช้ในการเปรียบเทียบวันที่และเวลาด้วย ผลลัพธ์ที่เกิดขึ้นจริงจึงอาจแตกต่างจากสิ่งที่คุณคาดหวังไว้ (ขึ้นอยู่กับค่าที่แสดง)

ตัวอย่างเช่น เมื่อคุณใช้ฟังก์ชัน NOW เพื่อเปรียบเทียบวันที่กับวันที่ปัจจุบันตามสูตร
   =IF(NOW()=DATEVALUE("10/1/92"),TRUE,FALSE)
 สูตรจะส่งกลับค่า FALSE แม้ว่าวันที่ปัจจุบันจะเป็น 10/1/92 จะส่งกลับค่า TRUE เมื่อวันที่เป็น 10/1/92 12:00:00 a.m. เท่านั้น หากคุณกำลังเปรียบเทียบสอง

วันที่ในสูตร และคุณไม่ต้องการให้มีเวลารวมอยู่ในผลลัพธ์ คุณก็สามารถหลีกเลี่ยงปัญหานี้โดยใช้ฟังก์ชัน  TODAY แทน:
   =IF(TODAY()=DATEVALUE("10/1/92"),TRUE,FALSE)

วิธีการทำงานกับสูตรวันที่

วิธีการหาจำนวนวันระหว่างวันนี้และวันที่ในอนาคต

เมื่อต้องการหาจำนวนวันระหว่างปัจจุบันและวันที่ในอนาคต ให้ใช้สูตรต่อไปนี้
   ="mm/dd/yy"-NOW()
โดยที่ "mm/dd/yy" เป็นวันในอนาคต ใช้รูปแบบทั่วไปเพื่อจัดรูปแบบเซลล์ที่มีสูตร

วิธีการหาจำนวนของวัน เดือน และปีระหว่างสองวันที่

เมื่อต้องการคำนวณจำนวนวัน เดือน และปีระหว่างสองวันที่ โดยที่วันที่เริ่มต้นและวันที่สิ้นสุดถูกป้อนเข้าไปในเซลล์ A1 และ A2 ตามลำดับ ให้ทำตามขั้นตอนต่อไปนี้:

1. สร้างสมุดงานใหม่
2. คลิก สมุดงานเปล่า
3. พิมพ์ข้อมูลต่อไปนี้ลงในสมุดงาน:

A1:03/25/94A2: 05/01/98
พิมพ์สูตรต่อไปนี้ในเซลล์ D1:
 =YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1), DAY(A2)<DAY(A1))),1,0)&" ปี, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2) <=MONTH(A1),DAY(A2)<DAY(A1)),11,IF(AND(MONTH(A2)<MONTH(A1),DAY(A2) >=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-1)))&" เดือน, "&A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" วัน"

หมายเหตุ: ถ้าคุณคัดลอก และวางสูตรนี้ โปรดตรวจสอบให้แน่ใจว่า จะไม่มีตัวแบ่งบรรทัด ถ้ามีตัวแบ่งบรรทัด สูตรจะไม่ทำงาน

4. ถ้าคุณพิมพ์สูตรได้อย่างถูกต้อง เซลล์ D1 แสดง:

4 ปี 1 เดือน 6 วัน


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

หมายเหตุ หากคุณคัดลอกและวางสูตรเหล่านี้ ตรวจสอบให้แน่ใจว่าไม่มีการขึ้นบรรทัดใหม่ ไม่เช่นนั้นสูตรจะไม่ทำงาน
   เซ็กเมนต์เวลาสูตร
   ------------------------------------------------------------------------
   จำนวนคงเหลือ = A2-DATE(YEAR(A2),MONTH(A2) -
   จำนวนวันระหว่างสองวันที่ IF(DAY(A2) < DAY(A1),1,0),DAY(A1)) & "วัน"
   ละเว้นปีและเดือน
   

   จำนวนคงเหลือ = MONTH(A2) - MONTH(A1)+IF(AND(MONTH(A2)
   เดือนระหว่างสองวันที่ < = MONTH(A1), DAY(A2) < DAY(A1)), 11
   ละเว้นปีถึงวัน IF(AND(MONTH(A2) < MONTH(A1),DAY(A2) > =
                                    DAY(A1)),12,IF(AND(MONTH(A2) > MONTH(A1)
                                    DAY(A2) < DAY(A1)),-1))) & " เดือน"

   จำนวนทั้งหมดของปี =YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2) <
   ระหว่างสองวันที่ MONTH(A1),AND(MONTH(A2)=MONTH(A1)
                                    DAY(A2)<DAY(A1))),1,0)&" ปี"
หมายเหตุ ในสูตรก่อนหน้านี้สามารถเลือกระบุ &" วัน" &" เดือน" และ &" ปี" ได้ สิ่งเหล่านี้ช่วยให้คุณสามารถแยกแยะความแตกต่างของผลลัพธ์ตามวัน เดือน และปีได้

วิธีการหาจำนวนของวันธรรมดาระหว่างสองวันที่

เมื่อต้องการหาจำนวนของวันธรรมดาระหว่างสองวันที่ โดยที่วันที่เริ่มต้นและวันที่สิ้นสุดถูกพิมพ์ลงในเซลล์ A1 และ A2 ตามลำดับ ให้ทำตามขั้นตอนเหล่านี้:

1. สร้างสมุดงานใหม่
2. คลิกสมุดงานว่าง
3. พิมพ์ข้อมูลต่อไปนี้ลงในสมุดงาน:
A1: 03/25/94 A2: 05/01/98

4. ในเซลล์ D1 พิมพ์สูตรต่อไปนี้:

= NETWORKDAYS(A1,A2) "1071" ปรากฏเป็นผลลัพธ์ในเซลล์ D1

หมายเหตุ: ถ้าคุณเห็นผลลัพธ์ #NAME ให้คลิก Add-Ins ที่ เมนู เครื่องมือ ของ Excel 2003 คลิกเพื่อเลือกกล่องกาเครื่องหมาย Analysis ToolPak จากนั้นคลิก ใช่ ถ้าคุณได้รับการแจ้งเตือนว่าคุณต้องการติดตั้งหรือไม่ ใน Excel 2007 และ 2010 ToolPak การวิเคราะห์เพิ่มอินมีอยู่แล้วภายใน

วิธีการเพิ่มวันที่แบบเพิ่มหน่วย

เมื่อต้องการเพิ่มวันที่ตามจำนวนของปี เดือน หรือวัน ให้ใช้สูตรต่อไปนี้ โดยที่ การอ้างอิง เป็นค่าวันที่หรือการอ้างอิงเซลล์ที่มีวันที่และ ค่า 1ค่า 2 และ ค่า 3 เป็นค่าที่เพิ่มขึ้นซึ่งคุณต้องการเพิ่มปี เดือน และวันตามลำดับ
   =DATE(YEAR(reference)+value1,MONTH(reference)+value2,DAY(reference)+value3)


ตัวอย่างเช่น เมื่อต้องการเพิ่มวันหนึ่งของเดือน สูตรคือ:
   =DATE(YEAR(DATEVALUE("6/20/96")),MONTH(DATEVALUE("6/20/96")) +1
    DAY(DATEVALUE("6/20/96")))

วิธีการทำงานกับสูตรเวลา

วิธีการคำนวณเวลาที่ผ่านไป

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

ในตัวอย่างต่อไปนี้ ถ้าเซลล์ C2 และ D2 มีสูตร= B2-A2 และเซลล์ C2 มีการจัดรูปแบบในรูปแบบทั่วไป เซลล์ก็จะแสดงเลขทศนิยม (ในกรณีนี้คือ 0.53125 การแสดงเลขอนุกรมเป็น 12 ชั่วโมง และ 45 นาที)
   A1: เวลาเริ่ม   B1: เวลาสิ้นสุด    C1: ความแตกต่าง D1: ความแตกต่าง
                                        (ทั่วไป)       (h:mm)
   A2: 6:30 AM      B2: 7:15 PM     C2: 0.53125     D2: 12:45

ถ้าเที่ยงคืนอยู่ระหว่างเวลาเริ่มต้นและเวลาสิ้นสุดของคุณ คุณต้องนำมาพิจารณาสำหรับความแตกต่างของเวลา 24 ชั่วโมง คุณสามารถทำเช่นนี้ได้ โดยการเพิ่มเลข 1 ซึ่งแสดงถึงหนึ่งรอบระยะเวลา 24 ชั่วโมง ตัวอย่างเช่น คุณอาจตั้งค่าในตารางต่อไปนี้ ซึ่งช่วยให้สามารถออกเป็นเวลาหลังเที่ยงคืน
   A1: เวลาเริ่ม    B1: เวลาสิ้นสุด    C1: ความแตกต่าง D1: ความแตกต่าง
                                         (ทั่วไป)       (h:mm)
   A2: 7:45 PM       B2: 10:30 AM    C2: 0.614583333  D2: 14:45


เมื่อต้องการตั้งค่าตารางนี้ พิมพ์สูตรต่อไปนี้ในเซลล์ C2 และ D2:
   = B2-A2 + IF(A2>B2,1)

วิธีการสะสมจำนวนชั่วโมงและนาทีมากกว่า 24 ชั่วโมง

หากคุณต้องการแสดงเวลาเกินกว่า 24 ชั่วโมงอย่างถูกต้อง คุณสามารถใช้รูปแบบที่มีอยู่แล้วภายในเป็น 37:30:55 หากคุณต้องการใช้รูปแบบที่กำหนดเองแทน คุณต้องใส่พารามิเตอร์ชั่วโมงของรูปแบบในวงเล็บ ตัวอย่างเช่น:
   [h]:mm

วิธีการแปลงวันที่เป็นค่าเทียบเท่าจำนวนทศนิยม

เมื่อต้องการแปลงเป็นวันที่ที่เป็นชุดต่อเนื่องกัน (h:mm:ss) เป็นเลขทศนิยม (0.00) คุณต้องแปลงเลขอนุกรมเป็นทศนิยมโดยการแปลงเป็นรูปแบบ 24 ชั่วโมง คุณสามารถทำได้ โดยคูณเวลาด้วย 24 ดังนี้
   = เวลา * 24
โดยที่ เวลา เป็นเลขที่คุณต้องการแปลงจากรูปแบบเวลาเป็นเลขทศนิยม เลขนี้สามารถเป็นการอ้างอิงเซลล์หรือสตริงของเลขในฟังก์ชัน TIMEVALUE

ตัวอย่างเช่น ถ้าเซลล์ A1 ประกอบด้วยเวลาของ "4:30" เมื่อต้องการแสดงถึงสี่ชั่วโมง และ 30 นาที สูตรคือ:
   = A1 * 24
ผลลัพธ์คือ 4.5

หากเซลล์ประกอบด้วยทั้งวันและเวลา ใช้สูตรต่อไปนี้:
   =(Time-INT(Time)) * 24
ตัวอย่างเช่น ถ้าเซลล์ A1 อ่านค่าได้ "6/20/96 4:30 AM" สูตรคือ:
   =(A1-INT(A1)) * 24
ผลลัพธ์จะเป็น 4.5 เช่นเดิม

วิธีการแปลงเลขทศนิยมเป็นค่าเทียบเท่าวันที่

เมื่อต้องการแปลงเลขทศนิยม (0.00) เป็นค่าเทียบเท่าของวันที่แบบอนุกรม (h:mm:ss) คุณต้องแปลงเลขทศนิยมโดยการแปลงเวลาเป็นรูปแบบ 24 ชั่วโมง คุณสามารถทำได้ โดยหารเวลาด้วย 24 เป็นดังนี้
   = เวลา/24
โดยที่ เวลา คือเลขที่คุณต้องการแปลงจากเลขฐานสิบเป็นตัวเลขวันที่แบบอนุกรมและสามารถเป็นการอ้างอิงเซลล์หรือจำนวนจริงก็ได้ ตัวอย่างเช่น ถ้าคุณมีค่า 4.5 ที่เป็นตัวแทนของสี่ชั่วโมงและ 30 นาทีใน เซลล์ A1 สูตรก็จะเป็น:
   = A1/24
ผลลัพธ์คือ 4:30

วิธีการโอนไฟล์ระหว่าง Microsoft Excel สำหรับ Mac และ Excel สำหรับ Windows

โดยค่าเริ่มต้น Excel สำหรับ Mac จะใช้ระบบวันที่ 1904 และ Excel สำหรับ Windows จะใช้ระบบวันที่ 1900 ซึ่งหมายความว่าเมื่อคุณพิมพ์เลขอนุกรม 1 ใน Excel สำหรับ Mac และจัดรูปแบบเป็นวันที่ Excel จะแสดงเป็น 1/2/1904 12:00 a.m. Excel สำหรับ Windows แสดงเลขอนุกรม 1 เป็น 1/1/1900 12:00 a.m.

หากคุณถ่ายโอนไฟล์จาก Excel สำหรับ Mac ไปยัง Excel สำหรับ Windows ความแตกต่างในระบบวันที่นี้ไม่ควรทำให้เกิดปัญหา เพราะระบบวันที่จะถูกเก็บไว้ในแต่ละไฟล์ แต่ถ้าคุณคัดลอกและวางระหว่างไฟล์ที่มีระบบวันที่ที่แตกต่างกันซึ่งมาจากแพลตฟอร์มที่แตกต่างกัน วันที่อาจมีการแสดงต่างออกไปสี่ปีและหนึ่งวันจากวันที่ที่ถูกต้อง

เมื่อต้องการเปลี่ยนไปใช้ระบบวันแบบ 1904 ใน Excel สำหรับ Windows ให้ทำตามขั้นตอนเหล่านี้:
  1. ในเมนู เครื่องมือ ใน Excel 2003 คลิก ตัวเลือก ใน Excel 2007 และรุ่นที่ใหม่กว่าไปยัง ตัวเลือก Excel
  2. ใน Excel 2003 คลิกแท็บ การคำนวณ แล้วคลิก เพื่อเลือกกล่องกาเครื่องหมาย ระบบวันที่ 1904 ใน Excel 2007 และรุ่นที่ใหม่กว่าให้คลิกตัวเลือก ขั้นสูง เลื่อนลงไปที่ เมื่อคำนวณสมุดงานนี้ เลือกกล่องกาเครื่องหมาย ใช้ระบบวันแบบที่ 1904 
เมื่อต้องการเปลี่ยนไปใช้ระบบวันแบบ 1904 ใน Excel สำหรับ Mac ให้ทำตามขั้นตอนเหล่านี้:  
  1. บนเมนู Excel ให้คลิก การกำหนดลักษณะ
  2. ภายใต้ สูตรและรายการ เลือก การคำนวณ
  3. ภายใต้ ตัวเลือกสมุดงาน คลิกเพื่อยกเลิกกล่องกาเครื่องหมาย ระบบวันที่ 1904

ข้อมูลอ้างอิง

สำหรับข้อมูลเพิ่มเติม ให้คลิกหมายเลขบทความต่อไปนี้เพื่อดูบทความใน Microsoft Knowledge Base:
214233 ข้อความหรือตัวเลขถูกแปลงไปเป็นรูปแบบตัวเลขไม่ได้ตั้งใจใน Excel
214386 ระยะเวลาสูงสุดใน Microsoft Excel (ซึ่งอาจจะเป็นภาษาอังกฤษ)
241072 วันและเวลาที่แสดงเป็นหมายเลขลำดับประจำสินค้าเมื่อดูสูตรใน Excel (ซึ่งอาจจะเป็นภาษาอังกฤษ)
264372 วิธีการควบคุม และทำความเข้าใจเกี่ยวกับการตั้งค่าในกล่องโต้ตอบจัดรูปแบบเซลล์ใน Excel

คุณสมบัติ

หมายเลขบทความ (Article ID): 214094 - รีวิวครั้งสุดท้าย: 8 พฤษภาคม 2557 - Revision: 4.0
ใช้กับ
  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
Keywords: 
kbnomt kbgraphxlink kbscreenshot kbdatetime kbautomation kbprogramming kbfunctions kbhowtomaster kbmigrate KB214094

ให้ข้อเสนอแนะ

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com