วิธีจัดการวันที่และเวลาอย่างถูกต้องใน Excel: คำแนะนำทีละขั้นตอนฉบับสมบูรณ์

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

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

วันที่และเวลาทำงานอย่างไรใน Excel

Excel ถือว่าวันที่และเวลาเป็นหมายเลขลำดับ ทำให้สามารถคำนวณโดยตรงได้ แต่ละวันที่สอดคล้องกับจำนวนเต็ม โดยปกติจะเริ่มต้นด้วย 1 มกราคม ค.ศ. 1900 (ซึ่งจะเป็นวันที่ 1) ดังนั้นวันที่ 2 มกราคม ค.ศ. 1900 จึงเป็นวันที่ 2 และต่อๆ ไป วิธีการทำงานนี้ทำให้การบวกวัน การคำนวณความแตกต่าง และการจัดการข้อมูลเวลาทำได้ง่ายขึ้นอย่างแม่นยำ

บทความที่เกี่ยวข้อง:

วิธีการเรียงลำดับและกรองข้อมูลอย่างผู้เชี่ยวชาญใน Excel: คู่มือระดับมืออาชีพและใช้งานได้จริง

ในทางกลับกัน ชั่วโมงจะแสดงเป็นเศษส่วนทศนิยมของวัน ตัวอย่างเช่น 6 น. เทียบเท่ากับ 00 เนื่องจากหมายถึงหนึ่งในสี่ของวันทั้งวัน ดังนั้น หากคุณป้อน '0,25/1/1 2024:6' ลงในเซลล์ Excel จะจัดเก็บค่า 00 (โดยที่ 45157,25 คือหมายเลขซีเรียลของวันที่ 45157 มกราคม 1 และ 2024 สอดคล้องกับ 0,25:6 น.)

รูปแบบวันที่และเวลาใน Excel: ประเภทและการปรับแต่ง

Excel จะใช้รูปแบบวันที่และเวลาเริ่มต้นโดยอิงตามการตั้งค่าภูมิภาคของระบบปฏิบัติการของคุณ อย่างไรก็ตาม คุณสามารถปรับเปลี่ยนได้อย่างง่ายดายตามความต้องการหรือข้อกำหนดในการนำเสนอของคุณ ตัวอย่างเช่น คุณสามารถแสดง '2/2' เป็น '2-ก.พ.', '2 กุมภาพันธ์ 2009' หรือ '02/02/09'

• การเปลี่ยนรูปแบบของเซลล์เลือกเซลล์หรือช่วง ไปที่แท็บ ‘หน้าแรก’ ส่วน ‘ตัวเลข’ และคลิกตัวเปิดใช้กล่องโต้ตอบขนาดเล็ก คุณสามารถกด CTRL+1 บนแป้นพิมพ์เพื่อเข้าถึงกล่อง 'จัดรูปแบบเซลล์' โดยตรงได้
• บนแท็บ 'ตัวเลข' เลือก 'วันที่' 'เวลา' หรือ 'กำหนดเอง' และเลือกรูปแบบที่เหมาะกับคุณที่สุด คุณสามารถดูตัวอย่างได้ในช่อง 'ตัวอย่าง' ก่อนที่จะยอมรับ
• หากคุณต้องการใช้รูปแบบภาษาอังกฤษ ให้เปลี่ยนการตั้งค่าภูมิภาคในกล่องเดียวกันเพื่อปรับการแสดงผล

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

ตัวอย่างเช่น หากต้องการแสดงวันที่ 2 มีนาคม 2024 เป็น 'วันเสาร์ที่ 02 มีนาคม 2024' คุณจะใช้รูปแบบดังนี้: ดิ๊ดดด ดิ๊ด-ดด-ป๊าบ-ป๊าบ.

สูตรที่จำเป็นสำหรับการจัดการวันที่และเวลาใน Excel

Excel มีฟังก์ชันเฉพาะสำหรับการรวม การแยก การคำนวณ และการจัดรูปแบบวันที่และเวลา บางส่วนที่ใช้มากที่สุดคือ:

ฟังก์ชั่น DATE

DATE(ปี; เดือน; วัน) ช่วยให้คุณสามารถสร้างวันที่ได้โดยการรวมค่าของปี เดือน และวันเข้าด้วยกัน มีประโยชน์มากเมื่อคุณมีข้อมูลอยู่ในคอลัมน์แยกกัน หรือต้องการแก้ไขวันที่ไม่สม่ำเสมอ

• ตัวอย่างพื้นฐาน: =วันที่(2024; 4; 5) กลับหมายเลขซีเรียลที่สอดคล้องกับวันที่ 5 เมษายน 2024
• หากเดือนมากกว่า 12 หรือวันที่ของเดือน Excel จะปรับวันที่โดยอัตโนมัติ: =วันที่(2024, 14, 10) กลับมาในวันที่ 10 กุมภาพันธ์ พ.ศ.2025
• หากเดือนหรือวันเป็นค่าลบ Excel จะย้อนกลับไปยังเดือนหรือปีก่อนหน้าตามความเหมาะสม

ฟังก์ชันเดือน

MONTH(date) ดึงเดือนจากวันที่ที่กำหนด โดยส่งคืนตัวเลขตั้งแต่ 1 ถึง 12 เหมาะสำหรับการแบ่งหรือจัดกลุ่มข้อมูลเป็นรายเดือน

• ตัวอย่าง: =เดือน("20/05/2023") กลับ 5 (พ.ค.)
• คุณสามารถรวมเข้ากับ TEXT เพื่อรับชื่อเดือนได้: =TEXT(A1, "อืมมม") → ‘พฤษภาคม’.

ฟังก์ชั่นข้อความ

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

• ตัวอย่าง: =TEXT(A1, "วว/ดด/ปปปป") แสดงวันที่ A1 เป็น '20/05/2023' แม้ว่าภายในจะเป็นหมายเลขซีเรียลก็ตาม
• เพื่อแสดงเฉพาะชื่อวัน: =ข้อความ(A1, “dddd”) → ‘วันพุธ’

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

NOW() คืนวันที่และเวลาปัจจุบันตามระบบ วันนี้() เพียงแค่วันที่ จะอัปเดตโดยอัตโนมัติทุกครั้งที่มีการคำนวณแผ่นงานใหม่

• ตัวอย่าง: =ตอนนี้() อาจกลับ '02/07/2024 10:54'.
• หากคุณคัดลอกผลลัพธ์และวางเป็นค่า ค่าเวลาจะไม่เปลี่ยนแปลงอีกต่อไป

ฟังก์ชั่นวันทำงาน

DAYS.WORK(start_date, end_date, ) คำนวณจำนวนวันทำงานระหว่างสองวันโดยไม่รวมสุดสัปดาห์และวันหยุด

• ตัวอย่าง: =DIAS.LAB(«01/09/2023», «15/09/2023») คืนค่า 11 หากมีวันหยุดสุดสัปดาห์สองวันอยู่ระหว่างนั้น
• คุณสามารถเพิ่มรายการวันหยุดเป็นอาร์กิวเมนต์ที่สาม โดยระบุช่วงของเซลล์

ฟังก์ชัน YEARFRAC

YEARFRAC(วันที่เริ่มต้น, วันที่สิ้นสุด, ) คำนวณเศษส่วนของปีระหว่างสองวันที่ มีประโยชน์สำหรับการคำนวณดอกเบี้ย, อายุ และอื่นๆ

• ตัวอย่างพื้นฐาน: =YEARFRAC(«01/01/2023», «31/12/2023») ส่งคืน 1.
• หากคุณเปลี่ยนอาร์กิวเมนต์ฐาน คุณสามารถใช้วิธีการนับวันที่แตกต่างกันได้: 0 สำหรับ US 30/360, 1 สำหรับ real/real, 2 สำหรับ real/360, 3 สำหรับ real/365, 4 สำหรับ European 30/360

ข้อผิดพลาดทั่วไปและปัญหาทั่วไปเกี่ยวกับวันที่และเวลาใน Excel

ปัญหาใหญ่ประการหนึ่งเกี่ยวกับวันที่คือความสับสนของรูปแบบระหว่างภูมิภาค ตัวอย่างเช่น '08/12/2024' ได้รับการตีความว่าเป็นวันที่ 8 ธันวาคมในประเทศส่วนใหญ่ แต่เป็นวันที่ 12 สิงหาคมในสหรัฐอเมริกา ขอแนะนำ:

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

El ความผิดพลาด #VALUE! มักจะปรากฏขึ้นเมื่อ Excel ไม่รู้จักค่าเป็นวันที่ หรือเมื่อคุณพยายามทำงานกับข้อความที่ดูเหมือนวันที่แต่ไม่ใช่

#NUM! จำนวน ข้อผิดพลาด สิ่งนี้จะเกิดขึ้นหากคุณป้อนวันที่นอกช่วงที่อนุญาต (ก่อนวันที่ 1 มกราคม พ.ศ. 1900 หรือหลังวันที่ 31 ธันวาคม พ.ศ. 9999) สิ่งนี้เกิดขึ้นเมื่อทำการแปลงวันที่ในประวัติศาสตร์หรือเมื่อป้อนปีที่ติดลบหรือมีค่ามากเกินไป

นอกจากนี้ ปีอธิกสุรทินอาจเปลี่ยนแปลงการคำนวณความแตกต่างของวันที่ได้หากไม่ได้คำนึงถึงอย่างถูกต้อง หากต้องการทราบว่าปีใดเป็นปีอธิกสุรทิน ให้ตรวจสอบว่าปีนั้นหารด้วย 4 ลงตัวหรือไม่: =MOD(ปี, 4)=0.

ปัญหาทั่วไปอีกประการหนึ่งคือความคลุมเครือของปีสองหลัก โดยค่าเริ่มต้น Excel จะตีความปี 00 ถึง 29 เป็น 2000 ถึง 2029 และปี 30 ถึง 99 เป็น 1930 ถึง 1999 ถึงแม้ว่าค่านี้อาจเปลี่ยนแปลงได้ขึ้นอยู่กับการตั้งค่าของคุณก็ตาม ควรกรอกปีด้วยตัวเลขสี่หลักเสมอเพื่อหลีกเลี่ยงความสับสน

เคล็ดลับและทางลัดที่เป็นประโยชน์สำหรับการทำงานเร็วขึ้นด้วยวันที่และเวลาใน Excel

• หากต้องการใช้รูปแบบวันที่หรือเวลาเริ่มต้นอย่างรวดเร็ว ให้เลือกเซลล์และกด CTRL+SHIFT+# (วันที่) หรือ CTRL+SHIFT+@ (เวลา)
• หากคุณเห็น ##### ในเซลล์ นั่นเป็นเพราะความกว้างไม่เพียงพอ: ขยายคอลัมน์จนกระทั่งเนื้อหาแสดงอย่างถูกต้อง
• หากต้องการป้อนวันที่ปัจจุบันลงในเซลล์ ให้วางเคอร์เซอร์แล้วกด CTRL+; (อัฒภาค). สำหรับเวลาปัจจุบัน ใช้ CTRL+SHIFT+; (อัฒภาค).
• คุณอาจต้องการปกป้องเซลล์ด้วยสูตรวันที่และเวลาเพื่อป้องกันการแก้ไขโดยไม่ได้ตั้งใจ เมื่อคุณสร้างสูตรเสร็จสิ้นแล้ว ให้เลือกช่วง ไปที่จัดรูปแบบเซลล์ (CTRL+1) ยกเลิกการเลือก "ล็อค" เฉพาะสำหรับเซลล์ที่สามารถแก้ไขได้ และสุดท้าย ป้องกันแผ่นงานจากแท็บ "ตรวจสอบ"

บทความที่เกี่ยวข้อง:

การใช้สูตรพื้นฐานของ Excel ให้เชี่ยวชาญ: คู่มือฉบับสมบูรณ์และใช้งานได้จริง

วิธีคำนวณชั่วโมงทำงานและสร้างตารางเวลาใน Excel

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

แท็กและโครงสร้างพื้นฐาน

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

การคำนวณชั่วโมงการทำงานต่อวัน

• ในการเพิ่มเวลาเช้าและบ่าย: =SUM(เช้า,เย็น)
• ตัวอย่าง: ถ้า 'ชั่วโมงเช้า' คือ D8 และ 'ชั่วโมงบ่าย' คือ F8 ใน 'ยอดรวมรายวัน' (G8) ให้ใส่: =ผลรวม(D8, F8)

การคำนวณชั่วโมงรายสัปดาห์และรายเดือน

• เพื่อรับยอดรวมรายสัปดาห์: ประเทศสหรัฐอเมริกา =ผลรวม(G8:G12) โดยที่ G8 ถึง G12 คือยอดรวมรายวันสำหรับแต่ละวันในสัปดาห์
• เพื่อคำนวณยอดรวมรายเดือน: เพิ่มเซลล์ผลรวมรายสัปดาห์: =ผลรวม(G14, G30, N14, N30).

โปรดจำไว้ว่าคุณสามารถป้องกันสูตรได้ด้วยการล็อคเซลล์ที่มีสูตรนั้นอยู่และปล่อยไว้เฉพาะเซลล์ป้อนข้อมูลเท่านั้น หากต้องการปลดล็อคเซลล์ ให้เลือกเซลล์ ไปที่จัดรูปแบบเซลล์ (CTRL+1) แล้วเลือกแท็บ "ป้องกัน" จากนั้นยกเลิกการเลือก "ล็อค" จากนั้นป้องกันแผ่นกระดาษเพื่อรักษาสูตรของคุณไว้

เพิ่ม ลบ และคำนวณความแตกต่างของเวลาใน Excel

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

ลบชั่วโมงภายในวันเดียวกัน

• ป้อนเวลาเริ่มต้นใน A2 และเวลาสิ้นสุดใน B2
• ใน C2 เขียน: = B2-A2
• จัดรูปแบบ C2 เป็น 'h:mm' หรือ 'h:mm:ss' เพื่อแสดงผลลัพธ์อย่างถูกต้อง

ลบชั่วโมงระหว่างวันต่างกันหรือมากกว่า 24 ชั่วโมง

• ป้อนวันที่และเวลาครบถ้วนใน A2 และ B2 โดยใช้รูปแบบ 'dd/mm/yyyy h:mm'
• ใน C2 เขียน: = (B2-A2) * 24 เพื่อให้ได้มูลค่ารวมของชั่วโมง
• นำการจัดรูปแบบตัวเลขไปใช้กับเซลล์ผลลัพธ์

เพิ่มชั่วโมงและระยะเวลา

• หากต้องการเพิ่มระยะเวลาหลาย ๆ ช่วงเข้าด้วยกัน ให้ใช้ =ผลรวม(B2:B3) หรือปุ่มผลรวมอัตโนมัติ (Σ)
• หากคุณจะเกิน 24 ชั่วโมง ให้กำหนดรูปแบบเซลล์ทั้งหมดเป็น ':mm;@'

สูตรอื่นๆ ที่มีประโยชน์สำหรับการคำนวณเวลา

โปรดจำไว้ว่าการจัดรูปแบบเซลล์มีความสำคัญเพื่อหลีกเลี่ยงข้อผิดพลาดหรือค่าที่ไม่ถูกต้องเมื่อทำงานกับชั่วโมงที่มากกว่า 24 หรือนาทีและวินาทีที่มากกว่าขีดจำกัดปกติ

การจัดการวันที่และเวลาขั้นสูง: โปรเจ็กต์ สูตร และคำแนะนำ

สำหรับสภาพแวดล้อมการจัดการระดับมืออาชีพและโครงการ Excel นำเสนอฟังก์ชันขั้นสูง เช่น วันทำงาน การคำนวณเวลา เศษส่วนของปี และรูปแบบสากลสำหรับการทำงานร่วมกันในระดับโลก

• วันทำงานระหว่างประเทศ ช่วยให้คุณสามารถปรับแต่งวันทำงานในแต่ละสัปดาห์ได้ มีประโยชน์หากบริษัทของคุณทำงานในวันหยุดสุดสัปดาห์หรือวันเฉพาะเจาะจง
• WORKDAY ช่วยให้คุณคำนวณวันที่เสร็จสิ้นงานโดยไม่รวมวันหยุดและวันหยุดสุดสัปดาห์ ช่วยเพิ่มประสิทธิภาพในการจัดการตารางเวลา
• ใช้ฟังก์ชัน YEARFRAC และช่องอัตราต่อปีเพื่อคำนวณดอกเบี้ย เงินคงค้าง หรือระยะเวลาตามสัดส่วนในทางการเงิน

หากคุณต้องการควบคุมการทำงานล่วงเวลา การลาป่วย วันหยุดพักร้อน หรือแนวคิดอื่น ๆ ให้เพิ่มคอลัมน์เฉพาะและใช้ ตารางแบบไดนามิก สรุปแยกตามบุคคลและช่วงเวลา

แนวทางปฏิบัติที่ดีและคำแนะนำที่สำคัญ

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

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

บทความที่เกี่ยวข้อง:

การใช้สูตรพื้นฐานของ Excel ให้เชี่ยวชาญ: คู่มือฉบับสมบูรณ์และใช้งานได้จริง

การเชี่ยวชาญการจัดการวันที่และเวลาใน Excel เกี่ยวข้องกับการทำความเข้าใจระบบหมายเลขซีเรียลภายใน การจัดการรูปแบบอย่างถูกต้อง การใช้สูตรที่ถูกต้อง และการคาดการณ์ข้อผิดพลาดที่อาจเกิดขึ้นจากการป้อนข้อมูลหรือการเปลี่ยนแปลงภูมิภาค โดยปฏิบัติตามหลักเกณฑ์เหล่านี้และใช้ฟังก์ชันขั้นสูง คุณสามารถคำนวณทุกอย่างตั้งแต่กำหนดการง่ายๆ ไปจนถึงกำหนดการที่ซับซ้อน วิเคราะห์ช่วงเวลาใดๆ ในสเปรดชีตของคุณอย่างละเอียด และรับรองความน่าเชื่อถือของการวิเคราะห์และรายงานของคุณ แบ่งปันข้อมูลนี้เพื่อให้ผู้ใช้ทราบเกี่ยวกับหัวข้อนี้มากขึ้น.