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

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

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

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

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

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 เพื่อจุดประสงค์นี้

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