คำนวณการเพิ่มและการลบเวลา (ส่วนต่างของเวลา, เวลาทำงาน) ใน Excel

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

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

ในบทช่วยสอนนี้ ฉันจะแสดงให้คุณเห็นวิธีการ ทำการคำนวณโดยใช้เวลาใน Excelหลายวิธี (เช่น การคำนวณความแตกต่างของเวลา การบวกและการลบเวลา การแสดงเวลาในรูปแบบต่างๆ และการคำนวณผลรวมของค่าเวลา).

Excel จัดการกับวันที่และเวลาอย่างไร

ดังที่ฉันได้กล่าวไปแล้ว วันที่และเวลาจะถูกเก็บไว้เป็นตัวเลขในเซลล์ใน Excelจำนวนเต็มหมายถึงทั้งวัน ส่วนเศษส่วนของตัวเลขแสดงถึงส่วนของวัน (สามารถแปลงเป็นค่าชั่วโมง นาที และวินาทีได้)

ตัวอย่างเช่น ค่า 1 หมายถึง 1900 มกราคม 1 ใน Excel ซึ่งเป็นจุดเริ่มต้นสำหรับ Excel เพื่อเริ่มพิจารณาวันที่

ดังนั้น 2 หมายถึง 1990 มกราคม 1 2 หมายถึง 3 มกราคม 1900 เป็นต้น 1 หมายถึง 3 มกราคม 44197

หมายเหตุ: Excel สำหรับ Windows และ Excel for Mac จะใช้วันที่เริ่มต้นต่างกัน1 ใน Excel สำหรับ Windows หมายถึง 1900 มกราคม 1 1 ใน Excel for Mac หมายถึง 1 มกราคม 1904

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

ตัวอย่างเช่น 44197.5 หมายถึง 2021 มกราคม 1 เวลา 1:12:00 น.

ดังนั้น หากคุณกำลังทำงานกับค่าของเวลาใน Excel คุณกำลังทำงานกับเศษส่วนของตัวเลข

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

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

สูตรคำนวณความแตกต่างของเวลาระหว่างสองครั้ง

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

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

มาดูตัวอย่างกัน

การลบอย่างง่ายสำหรับการคำนวณความแตกต่างของเวลาใน Excel

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

เวลาสิ้นสุด - เวลาเริ่มต้น

ผลลัพธ์ของการลบจะเป็นค่าทศนิยมแทนเวลาที่ผ่านไประหว่างค่าเวลาสองค่า

ด้านล่างนี้คือตัวอย่างที่ฉันมีเวลาเริ่มต้นและเวลาสิ้นสุด และฉันได้คำนวณความแตกต่างของเวลาด้วยการลบอย่างง่าย

เป็นไปได้ว่าผลลัพธ์ของคุณจะแสดงในรูปแบบเวลา (แทนที่จะเป็นค่าทศนิยมหรือชั่วโมง/นาที)ในตัวอย่างด้านบน ผลลัพธ์ในเซลล์ C2 แสดงเวลา 09:30 น. แทนที่จะเป็น 9.5

ซึ่งเป็นเรื่องปกติเพราะ Excel จะพยายามคัดลอกการจัดรูปแบบจากคอลัมน์ที่อยู่ติดกัน

หากต้องการแปลงเป็นทศนิยม ให้เปลี่ยนรูปแบบของเซลล์เป็น "ทั่วไป" (ตัวเลือกนี้อยู่ในแท็บ "หน้าแรก" ของกลุ่ม "ตัวเลข")

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

ต่อไปนี้คือรูปแบบต่างๆ ที่คุณสามารถใช้ได้:

รูปแบบ	มันทำอะไรได้บ้าง
hh	แสดงเฉพาะจำนวนชั่วโมงที่ผ่านไประหว่างสองวัน
mm	แสดงชั่วโมงเป็นตัวเลขสองหลัก (เช่น 04 หรือ 12)
hh: mm	แสดงชั่วโมงและนาทีที่ผ่านไประหว่างสองวัน เช่น 10:20
HH: mm: ss	แสดงชั่วโมง นาที และวินาทีที่ผ่านไประหว่างสองวัน เช่น 10:20:36

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

1. เลือกเซลล์ที่จะใช้รูปแบบวันที่กับ
2. กด Control ค้างไว้แล้วกด 1 (Command + 1 หากใช้ Mac)
3. ในกล่องโต้ตอบจัดรูปแบบเซลล์ที่เปิดขึ้น ให้คลิกแท็บตัวเลข (หากยังไม่ได้เลือกไว้)
4. ในบานหน้าต่างด้านซ้าย คลิกกำหนดเอง
5. ป้อนรหัสรูปแบบที่ต้องการในช่อง Type (ฉันกำลังใช้ hh:mm:ss ในตัวอย่างนี้)
6. คลิกตกลง

ขั้นตอนข้างต้นจะเปลี่ยนรูปแบบและแสดงค่าตามรูปแบบ

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

เคล็ดลับระดับมืออาชีพ: หากชั่วโมงทั้งหมดเกิน 24 ชั่วโมง ให้ใช้รูปแบบตัวเลขที่กำหนดเองต่อไปนี้แทน: [hh]:mm:ss

คำนวณความแตกต่างของเวลาเป็นชั่วโมง นาที หรือวินาที

เมื่อคุณลบค่าเวลา Excel จะส่งกลับตัวเลขทศนิยมที่แสดงความแตกต่างของเวลาที่เป็นผลลัพธ์

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

คำนวณความแตกต่างของเวลาเป็นชั่วโมง

สมมติว่าคุณมีชุดข้อมูลดังที่แสดงด้านล่าง และคุณต้องการคำนวณจำนวนชั่วโมงระหว่างค่าสองค่า

สูตรด้านล่างจะให้ความแตกต่างของเวลาเป็นชั่วโมง:

= (B2-A2) * 24

สูตรข้างต้นจะให้จำนวนชั่วโมงทั้งหมดที่ผ่านไประหว่างค่าเวลาสองค่า

บางครั้ง Excel จะพยายามช่วยเหลือและจะให้ผลลัพธ์ในรูปแบบเวลาด้วย (แสดงด้านล่าง)

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

หากคุณต้องการส่งคืนจำนวนชั่วโมงทั้งหมดที่ผ่านไประหว่างสองครั้ง (โดยไม่มีเศษส่วน) ให้ใช้สูตรนี้:

=INT((B2-A2)*24)

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

คำนวณความแตกต่างของเวลาเป็นนาที

ในการคำนวณความแตกต่างของเวลาเป็นนาที คุณต้องคูณค่าผลลัพธ์ด้วยจำนวนนาทีทั้งหมดในหนึ่งวัน (เช่น 1440 หรือ 24*60)

สมมติว่าคุณมีชุดข้อมูลดังที่แสดงด้านล่าง และคุณต้องการคำนวณจำนวนนาทีทั้งหมดที่ผ่านไประหว่างวันที่เริ่มต้นและวันที่สิ้นสุด

นี่คือสูตรที่สามารถทำได้:

=(B2-A2)*24*60

คำนวณความแตกต่างของเวลาเป็นวินาที

ในการคำนวณความแตกต่างของเวลาเป็นวินาที คุณต้องคูณค่าผลลัพธ์ด้วยจำนวนวินาทีทั้งหมดในหนึ่งวัน (เช่น 24*60*60 หรือ 86400)

สมมติว่าคุณมีชุดข้อมูลดังที่แสดงด้านล่าง และคุณต้องการนับจำนวนวินาทีทั้งหมดที่ผ่านไประหว่างวันที่เริ่มต้นและวันที่สิ้นสุด

นี่คือสูตรที่สามารถทำได้:

=(B2-A2)*24*60*60

คำนวณความแตกต่างของเวลาโดยใช้ฟังก์ชัน TEXT

อีกวิธีง่ายๆ ในการหาความแตกต่างของเวลาอย่างรวดเร็วโดยไม่ต้องกังวลกับการเปลี่ยนรูปแบบคือการใช้ฟังก์ชัน TEXT

ฟังก์ชัน TEXT ช่วยให้คุณสามารถระบุการจัดรูปแบบในสูตรได้

=TEXT(end_date-start_date, รูปแบบ)

พารามิเตอร์แรกคือการคำนวณที่คุณต้องการดำเนินการ และพารามิเตอร์ที่สองคือรูปแบบที่คุณต้องการแสดงผลการคำนวณ

สมมติว่าคุณมีชุดข้อมูลดังที่แสดงด้านล่าง และคุณต้องการคำนวณความแตกต่างของเวลาระหว่างสองครั้ง

นี่คือบางสูตรที่จะให้ผลลัพธ์ในรูปแบบต่างๆ

แสดงเวลาทำการเท่านั้น:

=TEXT(B2-A2,"hh")

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

แสดงนาทีทั้งหมด

=TEXT(B2-A2,"[mm]")

แสดงวินาทีทั้งหมด

=TEXT(B2-A2,"[ss]")

แสดงชั่วโมงและนาที

=TEXT(B2-A2,"[hh]:mm")

แสดงชั่วโมง นาที และวินาที

=TEXT(B2-A2,"hh:mm:ss")

หากคุณสงสัยว่าความแตกต่างระหว่าง hh และ [hh] ในรูปแบบ (หรือ mm และ [mm]) คืออะไร เมื่อคุณใช้วงเล็บเหลี่ยม คุณจะทราบจำนวนชั่วโมงทั้งหมดระหว่างวันที่สองวัน แม้ว่าค่าชั่วโมงก็ตาม มากกว่า 24ดังนั้น หากคุณกำลังลบค่าวันที่สองค่าที่ต่างกันมากกว่า 24 ชั่วโมง การใช้ [hh] จะให้จำนวนชั่วโมงทั้งหมดแก่คุณ ในขณะที่ hh จะให้เฉพาะจำนวนชั่วโมงที่ผ่านไปในวันที่สิ้นสุดเท่านั้น

รับความแตกต่างของเวลาในหนึ่งหน่วย (ชั่วโมง/นาที) โดยไม่สนใจผู้อื่น

หากคุณต้องการคำนวณความแตกต่างของเวลาระหว่างค่าเวลาสองค่าในหน่วยชั่วโมงหรือนาทีหรือวินาที คุณสามารถใช้ฟังก์ชัน HOUR, MINUTE หรือ SECOND เฉพาะได้

แต่ละฟังก์ชันเหล่านี้ใช้พารามิเตอร์หนึ่งตัว ค่าเวลา และส่งกลับหน่วยเวลาที่ระบุ

สมมติว่าคุณมีชุดข้อมูลดังรูปด้านล่าง และคุณต้องการคำนวณจำนวนชั่วโมงและวินาทีทั้งหมดที่ผ่านไประหว่างทั้งสอง

นี่คือสูตรในการทำสิ่งนี้:

คำนวณจำนวนชั่วโมงที่ผ่านไประหว่างสองครั้ง

=ชั่วโมง(B2-A2)

คำนวณนาทีตามผลลัพธ์ของค่าเวลา (ไม่รวมชั่วโมงที่เสร็จสมบูรณ์)

=นาที(B2-A2)

คำนวณวินาทีจากผลลัพธ์ค่าเวลา (ไม่รวมชั่วโมงและนาทีที่เสร็จสมบูรณ์)

=วินาที(B2-A2)

บางสิ่งที่ควรทราบเมื่อใช้สูตร HOURS, MINUTE และ SECOND เหล่านี้:

• ความแตกต่างระหว่างเวลาสิ้นสุดและเวลาเริ่มต้นต้องไม่เป็นค่าลบ (ซึ่งมักจะเป็นกรณีที่วันที่เปลี่ยนแปลง)ในกรณีนี้ สูตรเหล่านี้จะคืนค่า #NUM!ความผิดพลาด
• สูตรเหล่านี้ใช้เฉพาะส่วนของเวลา (และละเว้นส่วนของวัน) ของค่าเวลาที่เป็นผลลัพธ์ดังนั้นหากความแตกต่างระหว่างเวลาสิ้นสุดกับเวลาเริ่มต้นคือ 2 วัน 10 ชั่วโมง 32 นาที และ 44 วินาที สูตร HOUR จะให้ 10 สูตร MINUTE จะให้ 32 และสูตร SECOND จะให้ 44

คำนวณเวลาที่ผ่านไปตอนนี้ (จากเวลาเริ่มต้น)

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

ฟังก์ชัน NOW จะคืนค่าวันที่และเวลาปัจจุบันในเซลล์ที่ใช้เป็นหนึ่งในฟังก์ชันที่ไม่ยอมรับอาร์กิวเมนต์อินพุตใดๆ

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

=NOW() - เวลาเริ่มต้น

ด้านล่างนี้เป็นตัวอย่างที่ฉันมีเวลาเริ่มต้นในคอลัมน์ A และเวลาจนถึงคอลัมน์ B

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

คุณสามารถทำได้ด้วยสูตร TEXT ต่อไปนี้:

=TEXT(NOW()-A2,"dd hh:ss:mm")

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

ถ้าเวลาเริ่มต้นของคุณมีเพียงส่วนเวลา Excel จะถือว่าเป็นวันที่ 1990 มกราคม 1

ในกรณีนี้ หากคุณใช้ฟังก์ชัน NOW เพื่อคำนวณเวลาที่ผ่านไปจนถึงตอนนี้ จะทำให้คุณได้ผลลัพธ์ที่ไม่ถูกต้อง (เพราะค่าผลลัพธ์จะมีจำนวนวันที่ผ่านไปทั้งหมดตั้งแต่วันที่ 1990 มกราคม 1 ด้วย)

ในกรณีนี้ คุณสามารถใช้สูตรต่อไปนี้:

=ตอนนี้()-INT(ตอนนี้())-A2

สูตรข้างต้นใช้ฟังก์ชัน INT เพื่อลบส่วนของวันออกจากค่าที่ส่งคืนโดยฟังก์ชัน now ซึ่งใช้ในการคำนวณความแตกต่างของเวลา

โปรดทราบว่า NOW เป็นฟังก์ชันผันผวนที่จะอัปเดตเมื่อชีตเปลี่ยนแปลง แต่ไม่ใช่แบบเรียลไทม์

คำนวณเวลาที่เปลี่ยนวันที่ (คำนวณและแสดงเวลาติดลบใน Excel)

วิธีการที่อธิบายไว้จนถึงขณะนี้ทำงานได้ดีถ้าเวลาสิ้นสุดของคุณช้ากว่าเวลาเริ่มต้น

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

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

ตัวอย่างเช่น หากคุณเริ่มทำงานเวลา 6:00 น. และเลิกงานและหยุดทำงานเวลา 9 น.

หากคุณใช้เฉพาะค่าเวลา การลบเวลาเริ่มต้นจากเวลาสิ้นสุดจะได้ค่าลบ 9 ชั่วโมง (9 - 18)

Excel ไม่สามารถจัดการกับค่าเวลาที่ติดลบได้ (และสำหรับมนุษย์นั้น เว้นแต่คุณจะสามารถเดินทางข้ามเวลาได้)

ในกรณีนี้ คุณต้องมีวิธีระบุวันที่มีการเปลี่ยนแปลง และควรคำนวณตามนั้น

โชคดีที่มีวิธีแก้ปัญหาที่ง่ายมาก

สมมติว่าคุณมีชุดข้อมูลด้านล่าง ซึ่งฉันมีเวลาเริ่มต้นและเวลาสิ้นสุด

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

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

= B2-A2

นี่คือสูตร IF หากค่าความแตกต่างของเวลาเป็นค่าลบ ถ้าเป็นค่าลบ จะส่งกลับผลลัพธ์ที่ถูกต้อง

=IF((B2-A2)<0,1-(A2-B2),(B2-A2))

แม้ว่าจะใช้งานได้ดีในกรณีส่วนใหญ่ แต่ก็ยังไม่เพียงพอหากเวลาเริ่มต้นและสิ้นสุดห่างกันมากกว่า 24 ชั่วโมงตัวอย่างเช่น มีคนเข้าสู่ระบบเวลา 1 น. ในวันที่ 9 และออกจากระบบเวลา 00 น. ในวันที่ 2

เนื่องจากเป็นเวลามากกว่า 24 ชั่วโมง จึงไม่มีทางรู้ได้ว่าบุคคลนั้นจะเลิกเล่นหลังจากผ่านไป 2 ชั่วโมงหรือ 26 ชั่วโมง

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

เพิ่มและลบเวลาใน Excel

จนถึงตอนนี้ เราได้เห็นตัวอย่างเวลาเริ่มต้นและสิ้นสุดแล้ว เราต้องหาความแตกต่างของเวลา

Excel ยังช่วยให้คุณเพิ่มหรือลบค่าเวลาคงที่จากค่าวันที่และเวลาที่มีอยู่ได้อย่างง่ายดาย

ตัวอย่างเช่น สมมติว่าคุณมีรายการงานที่อยู่ในคิว โดยที่งานแต่ละงานใช้เวลาตามที่ระบุ และคุณต้องการทราบว่างานแต่ละงานจะสิ้นสุดเมื่อใด

ในกรณีนี้ คุณสามารถเพิ่มเวลาที่แต่ละงานจะใช้เวลาเริ่มต้นได้อย่างง่ายดายเพื่อดูว่างานจะเสร็จเมื่อใด

เนื่องจาก Excel เก็บค่าวันที่และเวลาเป็นตัวเลข คุณต้องแน่ใจว่าเวลาที่คุณพยายามเพิ่มอยู่ในรูปแบบที่ Excel ตามมาแล้ว

ตัวอย่างเช่น หากคุณบวก 1 เข้ากับวันที่ใน Excel วันที่ถัดไปจะให้คุณเนื่องจาก 1 หมายถึงเต็มวัน (เทียบเท่า 24 ชั่วโมง) ใน Excel

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

ใช้ฟังก์ชันเวลา

ฟังก์ชันเวลาใน Excel จะใช้ค่าชั่วโมง นาที และวินาที แล้วแปลงเป็นตัวเลขทศนิยมแทนเวลา

ตัวอย่างเช่น ถ้าฉันต้องการเพิ่มเวลาที่มีอยู่ 4 ชั่วโมง ฉันสามารถใช้สูตรต่อไปนี้:

= เวลาเริ่มต้น + เวลา (4,0,0)

สิ่งนี้มีประโยชน์หากคุณทราบชั่วโมง นาที และวินาทีที่จะบวกกับเวลาที่มีอยู่ และสามารถใช้ฟังก์ชัน TIME ได้โดยไม่ต้องกังวลเกี่ยวกับการแปลงเวลาเป็นค่าทศนิยมอย่างถูกต้อง

นอกจากนี้ โปรดทราบว่าฟังก์ชัน TIME จะพิจารณาเฉพาะส่วนจำนวนเต็มของค่าชั่วโมง นาที และวินาทีที่คุณป้อนเท่านั้นตัวอย่างเช่น ถ้าฉันใช้ 5.5 ชั่วโมงในฟังก์ชัน TIME มันก็แค่บวก 5 ชั่วโมงและละเว้นส่วนที่เป็นเศษส่วน

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

เช่นเดียวกันสำหรับส่วนนาทีและวินาที ฟังก์ชันจะพิจารณาเฉพาะค่าที่น้อยกว่า 60 นาทีและ 60 วินาทีเท่านั้น

เช่นเดียวกับที่ฉันใช้ฟังก์ชัน TIME ในการบวกเวลา คุณยังสามารถลบเวลาได้เพียงเปลี่ยนเครื่องหมาย + ในสูตรข้างต้นเป็นเครื่องหมายลบ

ใช้เลขคณิตพื้นฐาน

แม้ว่าฟังก์ชันเวลาจะใช้งานง่าย แต่ก็มีข้อจำกัดบางประการ (ตามที่กล่าวไว้ข้างต้น)

หากคุณต้องการการควบคุมมากขึ้น คุณสามารถใช้วิธีเลขคณิตที่จะกล่าวถึงที่นี่

แนวคิดนี้เรียบง่าย - แปลงค่าเวลาเป็นค่าทศนิยมแทนส่วนของวัน ซึ่งคุณสามารถเพิ่มค่าเวลาใดๆ ใน Excel ได้

ตัวอย่างเช่น ถ้าคุณต้องการเพิ่ม 24 ชั่วโมงให้กับค่าเวลาที่มีอยู่ คุณสามารถใช้สูตรต่อไปนี้:

= เวลาเริ่มต้น + 24/24

มันหมายความว่าฉันกำลังเพิ่มวันให้กับค่าเวลาที่มีอยู่

ตอนนี้ใช้แนวคิดเดียวกัน สมมติว่าคุณต้องการเพิ่ม 30 ชั่วโมงให้กับค่าเวลา คุณสามารถใช้สูตรต่อไปนี้:

= เวลาเริ่มต้น + 30/24

สูตรด้านบนทำแบบเดียวกัน โดยที่ส่วนจำนวนเต็มของ (30/24) แทนจำนวนวันทั้งหมดของเวลาที่คุณต้องการเพิ่ม และส่วนที่เป็นเศษส่วนแสดงชั่วโมง/นาที/วินาที

ในทำนองเดียวกัน ถ้าคุณต้องการจำนวนนาทีเฉพาะเพื่อเพิ่มค่าเวลา คุณสามารถใช้สูตรต่อไปนี้:

=เวลาเริ่มต้น +(เพิ่มนาที)/24*60

หากคุณมีวินาทีที่จะเพิ่ม คุณสามารถใช้สูตรนี้:

= เวลาเริ่มต้น + (นาทีเพิ่มเติม)/24*60*60

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

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

วิธีรวมเวลาใน Excel

บางครั้งคุณอาจต้องการเพิ่มค่าเวลาทั้งหมดใน Excel อย่างรวดเร็วการเพิ่มค่าเวลาหลายครั้งใน Excel เป็นเรื่องง่าย (แค่สูตร SUM ง่ายๆ)

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

มาดูตัวอย่างกัน

ด้านล่างนี้ ฉันมีรายการงานในคอลัมน์ B และเวลาที่แต่ละงานจะใช้เวลา และฉันต้องการเพิ่มเวลาเหล่านั้นอย่างรวดเร็วและทราบเวลาทั้งหมดสำหรับงานเหล่านี้ทั้งหมด

ในเซลล์ B9 ฉันใช้สูตร SUM อย่างง่ายในการคำนวณเวลาทั้งหมดที่ใช้สำหรับงานเหล่านี้ และให้ค่าเป็น 18:30 น. (หมายถึงใช้เวลา 18 ชั่วโมง 20 นาทีในการทำงานทั้งหมดให้เสร็จ)

จนถึงตอนนี้ดีมาก!

วิธีรวมกว่า 24 ชั่วโมงใน Excel

ตอนนี้ดูว่าเกิดอะไรขึ้นเมื่อฉันเปลี่ยนเวลาที่เสร็จสิ้นของภารกิจที่ 2 จาก 1 ชั่วโมงเป็น 10 ชั่วโมง

ผลลัพธ์จะแสดงเวลา 03:20 น. ซึ่งหมายความว่าจะใช้เวลา 3 ชั่วโมง 20 นาทีเพื่อทำงานเหล่านี้ให้เสร็จ

สิ่งนี้ไม่ถูกต้อง (ชัดเจน)

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

และเนื่องจากค่าผลลัพธ์ที่นี่เกิน 24 ชั่วโมง Excel ตัดสินใจแปลงส่วน 24 ชั่วโมงเป็นวัน ลบออกจากค่าที่แสดงให้ผู้ใช้เห็น และแสดงเฉพาะชั่วโมง นาที และวินาทีที่เหลือเท่านั้น

โชคดีที่มันแก้ไขได้ง่าย

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

ต่อไปนี้คือรูปแบบบางส่วนที่คุณสามารถใช้ได้:

รูปแบบ	ผลลัพธ์ที่คาดหวัง
[h]: มม	28:30
[ม]: เอสเอส	1710:00
d “ด” hh:mm	วันที่ 04 30:XNUMX
d "D" hh "Min" ss "วินาที"	1 วัน 04 นาที 00 วินาที
d "วัน" hh "นาที" ss "วินาที"	1 วัน 04 นาที 00 วินาที

คุณสามารถเปลี่ยนรูปแบบได้โดยไปที่ไดอะล็อก Format Cells และใช้รูปแบบที่กำหนดเอง หรือใช้ฟังก์ชัน TEXT และใช้รูปแบบใดๆ ข้างต้นในสูตรเอง

คุณสามารถใช้สูตร TEXT ต่อไปนี้เพื่อแสดงเวลาแม้ว่าจะเกิน 24 ชั่วโมง:

=TEXT(SUM(B2:B7),"[h]:mm:ss")

หรือถ้าคุณต้องการแปลงชั่วโมงมากกว่า 24 ชั่วโมงเป็นวัน ให้ใช้สูตรต่อไปนี้:

=TEXT(SUM(B2:B7),"dd hh:mm:ss")

ผลลัพธ์แสดงแฮช (###) แทนวันที่/เวลา (สาเหตุ + แก้ไข)

ในบางกรณี คุณอาจพบว่าแทนที่จะแสดงค่าเวลา Excel จะแสดงเครื่องหมายปอนด์ในเซลล์

สาเหตุที่เป็นไปได้และวิธีแก้ไขมีดังนี้

คอลัมน์กว้างไม่พอ

เมื่อเซลล์มีพื้นที่ไม่เพียงพอที่จะแสดงวันที่แบบเต็ม เซลล์อาจแสดงเครื่องหมายปอนด์

มีวิธีแก้ไขที่ง่าย - เปลี่ยนความกว้างของคอลัมน์และทำให้กว้างขึ้น

ค่าวันที่ติดลบ

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

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

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

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

ฉันหวังว่าคุณจะพบว่าบทช่วยสอนนี้มีประโยชน์

คำถามที่พบบ่อยเกี่ยวกับการคำนวณการบวกและการลบเวลาใน Excel