การทำความสะอาดข้อมูลที่มีค่าวันที่

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

คุณจะได้เรียนรู้เกี่ยวกับ -

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

รูปแบบวันที่

Excel รองรับ Date ค่าเป็นสองวิธี -

  • รูปแบบอนุกรม
  • ในรูปแบบปี - เดือน - วันที่แตกต่างกัน

คุณสามารถแปลง -

  • Date ในรูปแบบอนุกรมเป็น Date ในรูปแบบปี - เดือน - วัน

  • Date ในรูปแบบปี - เดือน - วันเป็นไฟล์ Date ในรูปแบบอนุกรม

วันที่ในรูปแบบอนุกรม

Date ในรูปแบบอนุกรมคือจำนวนเต็มบวกที่แสดงถึงจำนวนวันระหว่างวันที่ที่กำหนดถึง 1 มกราคม 1900 ทั้งปัจจุบัน Dateและ 1 มกราคม 1900 รวมอยู่ในการนับ ตัวอย่างเช่น 42354 คือไฟล์Date ซึ่งแสดงถึง 16 ธันวาคม 2558

วันที่ในรูปแบบเดือน - วัน - ปี

Excel สนับสนุนที่แตกต่างกัน Date รูปแบบตามไฟล์ Locale(สถานที่) ที่คุณเลือก ดังนั้นก่อนอื่นคุณต้องตรวจสอบความเข้ากันได้ของไฟล์Dateรูปแบบและการวิเคราะห์ข้อมูลในมือ โปรดทราบว่าบางอย่างDate รูปแบบขึ้นต้นด้วย * (ดอกจัน) -

  • Date รูปแบบที่ขึ้นต้นด้วย * (ดอกจัน) ตอบสนองต่อการเปลี่ยนแปลงการตั้งค่าวันที่และเวลาภูมิภาคที่ระบุไว้สำหรับระบบปฏิบัติการ

  • Date รูปแบบที่ไม่มี * (ดอกจัน) จะไม่ได้รับผลกระทบจากการตั้งค่าระบบปฏิบัติการ

เพื่อความเข้าใจในจุดประสงค์คุณสามารถสมมติว่าสหรัฐอเมริกาเป็นสถานที่ คุณพบสิ่งต่อไปนี้Date รูปแบบให้เลือกสำหรับไฟล์ Date- 8 THมิถุนายน 2016 -

  • * 6/8/2016 (ได้รับผลกระทบจากการตั้งค่าระบบปฏิบัติการ)
  • * วันพุธที่ 8 มิถุนายน 2559 (ได้รับผลกระทบจากการตั้งค่าระบบปฏิบัติการ)
  • 6/8
  • 6/8/16
  • 06/08/16
  • 8-Jun
  • 8-Jun-16
  • 08-Jun-16
  • Jun-16
  • June-16
  • J
  • J-16
  • 6/8/2016
  • 8-Jun-2016

หากคุณป้อนเพียงสองหลักเพื่อแสดงปีและถ้า -

  • ตัวเลขคือ 30 หรือสูงกว่า Excel จะถือว่าตัวเลขนี้แสดงถึงปีในศตวรรษที่ยี่สิบ

  • ตัวเลขต่ำกว่า 30 Excel จะถือว่าตัวเลขนี้แสดงถึงปีในศตวรรษที่ยี่สิบเอ็ด

ตัวอย่างเช่น 1/1/29 จะถือว่าเป็น 1 มกราคม 2029 และ 1/1/30 จะถือว่าเป็น 1 มกราคม 1930

การแปลงวันที่ในรูปแบบอนุกรมเป็นรูปแบบเดือน - วัน - ปี

ในการแปลงวันที่จากรูปแบบอนุกรมเป็นรูปแบบเดือน - วัน - ปีให้ทำตามขั้นตอนด้านล่าง -

  • คลิก Number ในแท็บ Format Cells กล่องโต้ตอบ

  • คลิก Date ภายใต้ Category.

  • เลือก Locale. ที่มีอยู่Date รูปแบบจะแสดงเป็นรายการภายใต้ Type.

  • คลิกที่ไฟล์ Format ภายใต้ Type เพื่อดูตัวอย่างในช่องที่อยู่ติดกับ Sample.

หลังจากเลือกรูปแบบแล้วให้คลิก OK.

การแปลงวันที่ในรูปแบบเดือน - วัน - ปีเป็นรูปแบบอนุกรม

คุณสามารถแปลงวันที่ในรูปแบบเดือน - วัน - ปีเป็นรูปแบบอนุกรมได้สองวิธี -

  • การใช้ Format Cells กล่องโต้ตอบ

  • ใช้ Excel DATEVALUE ฟังก์ชัน

ใช้กล่องโต้ตอบ Format Cells

  • คลิก Number ในแท็บ Format Cells กล่องโต้ตอบ

  • คลิก General ภายใต้ Category.

ใช้ฟังก์ชัน Excel DATEVALUE

คุณสามารถใช้ Excel DATEVALUE ฟังก์ชันในการแปลงไฟล์ Date ถึง Serial Numberรูปแบบ. คุณต้องใส่ไฟล์Dateอาร์กิวเมนต์ใน“” ตัวอย่างเช่น,

= DATEVALUE ("6/8/2016") ได้ผลลัพธ์เป็น 42529

รับวันที่ของวันนี้

หากคุณต้องการคำนวณตามวันที่ของวันนี้เพียงแค่ใช้ฟังก์ชัน Excel TODAY () ผลลัพธ์จะแสดงถึงวันที่ที่ใช้

ภาพหน้าจอต่อไปนี้วันนี้ () ฟังก์ชั่นการใช้งานที่ได้รับการถ่ายเมื่อวันที่ 16 THพฤษภาคม 2016 -

การค้นหาวันทำงานหลังจากวันที่ระบุ

คุณอาจต้องทำการคำนวณบางอย่างตามวันทำงานของคุณ

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

คุณสามารถใช้ Excel WORKDAY และ WORKDAY.INTL ฟังก์ชันสำหรับการดำเนินการดังกล่าว

ส. ฟังก์ชั่นและคำอธิบาย
1.

WORKDAY

ส่งคืนหมายเลขประจำเครื่องของวันที่ก่อนหรือหลังจำนวนวันทำงานที่ระบุ

2.

WORKDAY.INTL

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

ตัวอย่างเช่นคุณสามารถระบุ 15 THวันทำการนับจากวันนี้ (ภาพด้านล่างจะนำมาในวันที่ 16 THพฤษภาคม 2016) โดยในวันนี้และวันทำงานฟังก์ชัน

สมมติว่า 25 ปีบริบูรณ์พฤษภาคม 2016 และวันที่ 1 เซนต์มิถุนายน 2016 เป็นวันหยุด จากนั้นการคำนวณของคุณจะเป็นดังนี้ -

การปรับแต่งคำจำกัดความของวันหยุดสุดสัปดาห์

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

วันหยุดสุดสัปดาห์ วันหยุดสุดสัปดาห์ - หมายเลข
เสาร์อาทิตย์ 1 หรือละเว้น
วันอาทิตย์วันจันทร์ 2
วันจันทร์วันอังคาร 3
วันอังคารวันพุธ 4
วันพุธวันพฤหัสบดี 5
วันพฤหัสบดีวันศุกร์ 6
วันศุกร์วันเสาร์ 7
วันอาทิตย์เท่านั้น 11
วันจันทร์เท่านั้น 12
วันอังคารเท่านั้น 13
วันพุธเท่านั้น 14
วันพฤหัสบดีเท่านั้น 15
วันศุกร์เท่านั้น 16
เฉพาะวันเสาร์ 17

สมมติว่าถ้าวันหยุดสุดสัปดาห์เป็นวันศุกร์เท่านั้นคุณต้องใช้เลข 16 ในฟังก์ชัน WORKDAY.INTL

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

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

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

ส. ฟังก์ชั่นและคำอธิบาย
1.

NETWORKDAYS

ส่งคืนจำนวนวันทำงานทั้งหมดระหว่างวันที่สองวัน

2.

NETWORKDAYS.INTL

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

คุณสามารถคำนวณจำนวนวันทำงานระหว่างวันนี้และวันอื่นด้วยฟังก์ชัน TODAY และ NETWORKDAYS ในภาพหน้าจอที่ระบุด้านล่างในวันนี้คือ 16 THพฤษภาคม 2016 และวันที่สิ้นสุดคือ 16 THมิถุนายน 2016 25 THพฤษภาคม 2016 และวันที่ 1 เซนต์มิถุนายน 2016 เป็นวันหยุด

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

แยกปีเดือนวันจากวันที่

คุณสามารถแยกจากแต่ละวันในรายการวันที่วันเดือนและปีที่เกี่ยวข้องโดยใช้ฟังก์ชัน excel DAY, MONTH และ YEAR

ตัวอย่างเช่นพิจารณาวันที่ต่อไปนี้ -

จากแต่ละวันเหล่านี้คุณสามารถแยกวันเดือนและปีได้ดังนี้ -

แยกวันในสัปดาห์จากวันที่

คุณสามารถแยกจากแต่ละวันในรายการวันที่ซึ่งเป็นวันที่สอดคล้องกันในสัปดาห์ด้วยฟังก์ชัน Excel WEEKDAY

ลองพิจารณาตัวอย่างเดียวกันที่ให้ไว้ข้างต้น

ได้รับวันที่จากปีเดือนและวัน

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

พิจารณาข้อมูลต่อไปนี้ -

ใช้ฟังก์ชัน DATE เพื่อรับค่า DATE

การคำนวณปีเดือนและวันระหว่างสองวัน

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

ผลลัพธ์มีดังนี้ -