การคำนวณความแตกต่างระหว่างวันที่สองวันที่

การคำนวณความแตกต่างระหว่างวันที่สองวันที่

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

ระยะห่างระหว่างวัน

=DATEDIF(D9,E9,"d") ที่มีผลลัพธ์เป็น 856

ในตัวอย่างนี้ วันที่เริ่มต้นอยู่ในเซลล์ D9 และวันที่สิ้นสุดอยู่ในเซลล์ E9 สูตรอยู่ที่ F9 "d" จะส่งกลับจำนวนวันเต็มวันระหว่างทั้งสองวัน

ระยะห่างระหว่างสัปดาห์

=(DATEDIF(D13,E13,"d")/7) ผลลัพธ์ที่ได้คือ 122.29

ในตัวอย่างนี้ วันที่เริ่มต้นอยู่ในเซลล์ D13 และวันที่สิ้นสุดอยู่ในเซลล์ E13 "d" จะส่งกลับจำนวนวัน แต่โปรดสังเกตว่าจะมี /7 ตอนท้าย ที่หารจำนวนวันด้วย 7 เนื่องจากสัปดาห์หนึ่งมี 7 วัน โปรดสังเกตว่าผลลัพธ์นี้จำเป็นต้องจัดรูปแบบเป็นตัวเลขด้วย กด CTRL + 1 แล้วคลิก ตัวเลข > ตำแหน่งทศนิยม: 2.

ระยะห่างระหว่างเดือน

=DATEDIF(D5,E5,"m") ผลลัพธ์ที่ได้คือ 28

ในตัวอย่างนี้ วันที่เริ่มต้นอยู่ในเซลล์ D5 และวันที่สิ้นสุดอยู่ในเซลล์ E5 ในสูตร "m" จะส่งกลับจำนวนเดือนทั้งหมดระหว่างวันทั้งสอง

ระยะห่างระหว่างปี

=DATEDIF(D2,E2,"y") ผลลัพธ์ที่ได้คือ 2

ในตัวอย่างนี้ วันที่เริ่มต้นอยู่ในเซลล์ D2 และวันที่สิ้นสุดอยู่ในเซลล์ E2 "y" จะส่งกลับจำนวนปีเต็มระหว่างทั้งสองวัน

คำนวณอายุสะสมเป็นปี เดือน และวัน

คุณยังสามารถคำนวณอายุหรือเวลาบริการของบุคคลหนึ่งๆ ได้อีกด้วย ตัวอย่างผลลัพธ์ที่ได้ เช่น "2 ปี 4 เดือน 5 วัน"

1. ใช้ DATEDIF เพื่อค้นหาจำนวนปีรวม

=DATEDIF(D17,E17,"y") ผลลัพธ์ที่ได้คือ 2

ในตัวอย่างนี้ วันที่เริ่มต้นอยู่ในเซลล์ D17 และวันที่สิ้นสุดอยู่ในเซลล์ E17 ในสูตร "y" จะส่งกลับจำนวนปีเต็มระหว่างทั้งสองวัน

2. ใช้ DATEDIF อีกครั้ง ด้วย "ym" เพื่อคำนวณหาเดือน

=DATEDIF(D17,E17,"ym") ผลลัพธ์ที่ได้คือ 4

ในเซลล์อื่น ให้ใช้สูตร DATEDIF กับพารามิเตอร์ "ym" "ym" จะส่งกลับจำนวนเดือนที่เหลืออยู่ตั้งแต่ปีเต็มที่ผ่านมา

3. ใช้สูตรอื่นเพื่อหาจำนวนวัน

=DATEDIF(D17,E17,"md") ผลลัพธ์ที่ได้คือ 5

ในตอนนี้ เราต้องคำนวณหาจำนวนวันที่เหลือ เราจะทำโดยการเขียนสูตรชนิดอื่น ดังที่แสดงไว้ทางด้านบน สูตรนี้ลบวันแรกของเดือนที่สิ้นสุด (1/5/2016) จากวันสิ้นสุดเดิมในเซลล์ E17 (6/5/2016) นี่คือวิธีที่สูตรใช้ในการลบ: ก่อนอื่นฟังก์ชัน DATE จะสร้างวันที่ 1/5/2016 ขึ้นมา โดยสร้างขึ้นจากปีในเซลล์ E17 และเดือนในเซลล์ E17 จากนั้น 1 จะแทนวันแรกของเดือนนั้น ผลลัพธ์ของฟังก์ชัน DATE คือ 1/5/2016 จากนั้น เราจะลบผลลัพธ์นั้นจากวันสิ้นสุดเดิมในเซลล์ E17 ซึ่งคือ 6/5/2016 เมื่อ 6/5/2016 ลบ 1/5/2016 จะเท่ากับ 5 วัน

หมายเหตุ: เราไม่แนะนำให้ใช้อาร์กิวเมนต์ "md" ของ DATEDIF เนื่องจากอาจคำนวณได้ผลลัพธ์ที่คลาดเคลื่อน

4. ตัวเลือกเพิ่มเติม: รวมทั้งสามสูตรในหนึ่งเดียว

=DATEDIF(D17,E17,"y")&" ปี, "&DATEDIF(D17,E17,"ym")&" เดือน, "&DATEDIF(D17,E17,"md")&" วัน" ผลลัพธ์ที่ได้คือ 2 ปี, 4 เดือน, 5 วัน

คุณสามารถวางการคำนวณทั้งสามไว้ในเซลล์เดียวดังเช่นตัวอย่างนี้ ใช้เครื่องหมายและ เครื่องหมายอัญประกาศ และข้อความ แม้จะต้องพิมพ์สูตรที่ยาวกว่าปกติ แต่อย่างน้อยก็จะได้สูตรทั้งหมดในเซลล์เดียว เคล็ดลับ: กด ALT+ENTER เพื่อใส่ตัวแบ่งบรรทัดในสูตรของคุณ ซึ่งทำให้อ่านง่ายขึ้น และกด CTRL+SHIFT+U ถ้าคุณไม่เห็นสูตรทั้งหมด

ดาวน์โหลดตัวอย่างของเรา

คุณสามารถดาวน์โหลดเวิร์กบุ๊กตัวอย่างที่มีตัวอย่างทั้งหมดในบทความนี้ คุณสามารถทำตาม หรือสร้างสูตรของคุณเอง

ดาวน์โหลดตัวอย่างการคำนวณวัน

การคำนวณวันและเวลาอื่นๆ

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

=DATEDIF(TODAY(),D28,"y") ผลลัพธ์ที่ได้คือ 984

โปรดทราบว่า เวลาในบทความนี้คือ 6 ตุลาคม 2016

ใช้ฟังก์ชัน NETWORKDAYS.INTL เมื่อคุณต้องการคำนวณจำนวนวันทำงานระหว่างวันที่สองวัน คุณยังสามารถให้ฟังก์ชันไม่รวมวันสุดสัปดาห์และวันหยุดด้วย

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

1. พิมพ์วันที่เริ่มต้นและวันที่สิ้นสุด

วันที่เริ่มต้นในเซลล์ D53 คือ 1/1/2016 ส่วนวันที่สิ้นสุดอยู่ในเซลล์ E53 คือ 31/12/2016

ในตัวอย่างนี้ วันที่เริ่มต้นอยู่ในเซลล์ D53 และวันที่สิ้นสุดอยู่ในเซลล์ E53

2. ในเซลล์อื่น ให้พิมพ์สูตรดังนี้:

= NETWORKDAYS INTL(D53,E53,1) ผลลัพธ์ที่ได้คือ 261

พิมพ์สูตรดังตัวอย่างข้างต้น เลข 1 ในสูตรจะตั้งวันเสาร์และวันอาทิตย์เป็นวันสุดสัปดาห์ และลบวันเหล่านั้นออกจากผลรวม

หมายเหตุ: Excel 2007 ไม่มีฟังก์ชัน NETWORKDAYS.INTL แต่มี NETWORKDAYS ตัวอย่างข้างต้นจะเป็นเช่นนี้ใน Excel 2007: =NETWORKDAYS(D53,E53) คุณไม่ต้องระบุเลข 1 เนื่องจาก NETWORKDAYS ถือว่าวันสุดสัปดาห์คือวันเสาร์และวันอาทิตย์

3. ถ้าจำเป็น ให้เปลี่ยน 1

รายการ Intellisense แสดง 2 - อาทิตย์, จันทร์; 3 - จันทร์, อังคาร; และอื่นๆ

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

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

4. พิมพ์ชื่อช่วงวันหยุด

= NETWORKDAYS INTL(D53,E53,1,MyHolidays) ผลลัพธ์ที่ได้คือ 252

ถ้าคุณสร้างชื่อช่วงวันหยุดในส่วน "ก่อนที่คุณจะเริ่ม" ด้านบน แล้วพิมพ์ที่ส่วนท้ายดังนี้ ถ้าคุณไม่มีวันหยุด คุณไม่จำเป็นต้องใส่เครื่องหมายจุลภาคและ MyHolidays ถ้าคุณใช้ Excel 2007 ตัวอย่างข้างต้นจะเป็นดังนี้แทน: =NETWORKDAYS(D53,E53,MyHolidays)

เคล็ดลับ: ถ้าคุณไม่ต้องการอ้างอิงชื่อช่วงวันหยุด คุณยังสามารถพิมพ์ช่วงแทนได้ เช่น D35:E:39 หรือ พิมพ์วันหยุดแต่ละวันลงไปภายในสูตร ตัวอย่างเช่น ถ้าวันหยุดของคุณคือ 1 และ 2 มกราคม 2016 คุณจะพิมพ์ลงไปดังนี้: =NETWORKDAYS.INTL(D53,E53,1,{"1/1/2016","2/1/2016"}) ใน Excel 2007 สูตรจะมีลักษณะดังนี้: =NETWORKDAYS(D53,E53,{"1/1/2016","2/1/2016"})

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

1. พิมพ์เวลาเริ่มต้นและเวลาสิ้นสุด

วัน/เวลาเริ่มต้นที่ 7:15 AM, วัน/เวลาสิ้นสุดที่ 4:30 PM

ในตัวอย่างนี้ เวลาเริ่มต้นอยู่ในเซลล์ D80 และเวลาสิ้นสุดอยู่ในเซลล์ E80 อย่าลืมพิมพ์ชั่วโมง นาที และช่องว่างก่อน AM หรือ PM

2. ตั้งค่ารูปแบบ h:mm AM/PM

กล่องโต้ตอบการจัดรูปแบบเซลล์, คำสั่งแบบกำหนดเอง, ชนิด h:mm AM/PM

เลือกวันที่ทั้งสองวัน แล้วกด CTRL + 1 (หรือ รูปไอคอนปุ่ม Command ของ MAC + 1 บน Mac) อย่าลืมเลือก กำหนดเอง > h:mm AM/PM ถ้ายังไม่ได้ตั้งค่า

3. ลบทั้งสองเวลา

= E80-D80 ผลลัพธ์ที่ได้คือ 9:15 AM

ในเซลล์อื่น ให้ลบเซลล์เวลาเริ่มต้นจากเซลล์เวลาสิ้นสุด

4. ตั้งค่ารูปแบบ h:mm

กล่องโต้ตอบการจัดรูปแบบเซลล์, คำสั่งแบบกำหนดเอง, ชนิด h:mm

กด CTRL + 1 (หรือ รูปไอคอนปุ่ม Command ของ MAC + 1 บน Mac) เลือก กำหนดเอง > h:mm เพื่อที่ผลลัพธ์จะไม่รวม AM และ PM

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

1. พิมพ์วันและเวลาเต็มทั้งสอง

วันที่เริ่มต้นที่ 1/1/16 1:00 PM วันที่สิ้นสุดที่ 2/1/16 2:00 PM

ในเซลล์หนึ่ง ให้พิมพ์วัน/เวลาเริ่มต้นเต็ม และในอีกเซลล์หนึ่ง ให้พิมพ์วัน/เวลาสิ้นสุดเต็ม แต่ละเซลล์ควรมีทั้งเดือน วัน ปี ชั่วโมง นาที และช่องว่างก่อน AM หรือ PM

2. ตั้งค่ารูปแบบ 14/3/12 1:30 PM

กล่องโต้ตอบการจัดรูปแบบเซลล์, คำสั่งวันที่, 14/3/12, ชนิด 1:30 PM

เลือกทั้งสองเซลล์ แล้วกด CTRL + 1 (หรือ รูปไอคอนปุ่ม Command ของ MAC + 1 บน Mac) จากนั้นเลือก วัน > 14/3/12 1:30 PM นี่ไม่ใช่วันที่คุณจะตั้ง เป็นเพียงตัวอย่างที่แสดงว่ารูปแบบจะมีลักษณะอย่างไร โปรดทราบว่าในเวอร์ชันก่อนหน้า Excel 2016 รูปแบบนี้อาจจำเป็นวันที่ตัวอย่างแตกต่างกัน เช่น 14/3/01 1:30 PM

3. ลบทั้งสอง

=E84-D84 และผลลัพธ์เป็น 1.041666667

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

4. ตั้งค่ารูปแบบ [h]:mm

กล่องโต้ตอบการจัดรูปแบบเซลล์, คำสั่งแบบกำหนดเอง, ชนิด [h]:mm

กด CTRL + 1 (หรือ รูปไอคอนปุ่ม Command ของ MAC + 1 บน Mac) เลือก กำหนดเอง ในกล่อง ชนิด ให้พิมพ์ [h]:mm

ดูเพิ่มเติม

ฟังก์ชัน DATEDIF

ฟังก์ชัน NETWORKDAYS.INTL

NETWORKDAYS

ฟังก์ชันวันและเวลาอื่นๆ

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

ขยายทักษะของคุณ
สำรวจการฝึกอบรม
รับฟีเจอร์ใหม่ก่อนใคร
เข้าร่วม Office Insider

ข้อมูลนี้เป็นประโยชน์หรือไม่

ขอบคุณสำหรับคำติชมของคุณ!

ขอขอบคุณสำหรับคำติชมของคุณ! เราคิดว่าอาจเป็นประโยชน์ที่จะให้คุณได้ติดต่อกับหนึ่งในตัวแทนฝ่ายสนับสนุน Office ของเรา

×