Làm sạch dữ liệu có chứa giá trị ngày

Dữ liệu bạn lấy từ các nguồn khác nhau có thể chứa các giá trị ngày tháng. Trong chương này, bạn sẽ hiểu cách chuẩn bị dữ liệu chứa các giá trị dữ liệu để phân tích.

Bạn sẽ học về -

Định dạng ngày
- Ngày ở định dạng nối tiếp
- Ngày ở các Định dạng Tháng-Ngày-Năm khác nhau
Chuyển đổi Ngày ở Định dạng Nối tiếp sang Định dạng Tháng-Ngày-Năm
Chuyển đổi Ngày ở Định dạng Tháng-Ngày-Năm sang Định dạng Nối tiếp
Lấy ngày hôm nay
Tìm một ngày làm việc sau những ngày được chỉ định
Tùy chỉnh định nghĩa về một ngày cuối tuần
Số ngày làm việc giữa hai ngày nhất định
Trích xuất Năm, Tháng, Ngày từ Ngày
Trích ngày trong tuần từ ngày
Ngày lấy từ Năm, Tháng và Ngày
Tính số năm, tháng và ngày giữa hai ngày

Định dạng ngày

Excel hỗ trợ Date giá trị theo hai cách -

Định dạng nối tiếp
Ở các Định dạng Năm-Tháng-Ngày khác nhau

Bạn có thể chuyển đổi -

A Date ở Định dạng nối tiếp thành một Date ở định dạng Năm-Tháng-Ngày
A Date ở Định dạng Năm-Tháng-Ngày thành Date ở định dạng nối tiếp

Ngày ở định dạng nối tiếp

A Date ở định dạng sê-ri là một số nguyên dương đại diện cho số ngày từ ngày đã cho đến ngày 1 tháng 1 năm 1900. Cả Datevà ngày 1 tháng 1 năm 1900 được tính vào số lượng. Ví dụ: 42354 là mộtDate đại diện cho ngày 16/12/2015.

Ngày theo định dạng Tháng-Ngày-Năm

Excel hỗ trợ khác nhau Date Các định dạng dựa trên Locale(Vị trí) bạn chọn. Do đó, trước tiên bạn cần xác định tính tương thích củaDateđịnh dạng và Phân tích dữ liệu trong tầm tay. Lưu ý rằng nhất địnhDate định dạng có tiền tố là * (dấu hoa thị) -

Date các định dạng bắt đầu bằng * (dấu hoa thị) đáp ứng các thay đổi trong cài đặt ngày và giờ khu vực được chỉ định cho hệ điều hành
Date các định dạng không có dấu * (dấu hoa thị) không bị ảnh hưởng bởi cài đặt hệ điều hành

Để hiểu rõ mục đích, bạn có thể giả định United States là Locale. Bạn tìm thấy những điều sauDate định dạng để chọn cho Date- 8 ^ngày tháng Sáu, 2016 -

* 6/8/2016 (bị ảnh hưởng bởi cài đặt hệ điều hành)
* Thứ 4, ngày 8 tháng 6 năm 2016 (bị ảnh hưởng bởi cài đặt hệ điều hành)
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

Nếu bạn chỉ nhập hai chữ số để đại diện cho một năm và nếu -

Các chữ số là 30 hoặc cao hơn, Excel giả định các chữ số đại diện cho các năm trong thế kỷ XX.
Các chữ số nhỏ hơn 30, Excel giả định các chữ số đại diện cho các năm trong thế kỷ XXI.

Ví dụ: 1/1/29 được coi là ngày 1 tháng 1 năm 2029 và 1/1/30 được coi là ngày 1 tháng 1 năm 1930.

Chuyển đổi Ngày ở Định dạng Nối tiếp sang Định dạng Tháng-Ngày-Năm

Để chuyển đổi ngày từ định dạng tuần tự sang định dạng Tháng-Ngày-Năm, hãy làm theo các bước dưới đây:

Nhấn vào Number tab trong Format Cells hộp thoại.
Nhấp chuột Date Dưới Category.
Lựa chọn Locale. Có sẵnDate định dạng sẽ được hiển thị dưới dạng danh sách dưới Type.
Nhấp vào một Format Dưới Type để xem bản xem trước trong hộp bên cạnh Sample.

Sau khi chọn Định dạng, hãy nhấp vào OK.

Chuyển đổi Ngày ở Định dạng Tháng-Ngày-Năm sang Định dạng Nối tiếp

Bạn có thể chuyển đổi ngày ở định dạng Tháng-Ngày-Năm sang định dạng Nối tiếp theo hai cách:

Sử dụng Format Cells hộp thoại
Sử dụng Excel DATEVALUE chức năng

Sử dụng hộp thoại Định dạng Ô

Nhấn vào Number tab trong Format Cells hộp thoại.
Nhấp chuột General Dưới Category.

Sử dụng hàm DATEVALUE trong Excel

Bạn có thể sử dụng Excel DATEVALUE chức năng chuyển đổi một Date đến Serial Numberđịnh dạng. Bạn cần bao gồmDateđối số trong "". Ví dụ,

= DATEVALUE ("6/8/2016") cho kết quả là 42529

Lấy ngày hôm nay

Nếu bạn cần thực hiện các phép tính dựa trên ngày hôm nay, chỉ cần sử dụng hàm TODAY () trong Excel. Kết quả phản ánh ngày nó được sử dụng.

Ảnh chụp màn hình sau đây của TODAY () sử dụng chức năng đã được thực hiện vào ngày 16 ^tháng tháng năm 2016 -

Tìm một ngày làm việc sau những ngày đã chỉ định

Bạn có thể phải thực hiện các phép tính nhất định dựa trên số ngày làm việc của mình.

Ngày làm việc không bao gồm ngày cuối tuần và bất kỳ ngày lễ nào. Điều này có nghĩa là nếu bạn có thể xác định ngày cuối tuần và ngày nghỉ của mình, bất kỳ phép tính nào bạn thực hiện sẽ dựa trên ngày làm việc. Ví dụ: bạn có thể tính toán ngày đến hạn hóa đơn, thời gian giao hàng dự kiến, ngày họp tiếp theo, v.v.

Bạn có thể sử dụng Excel WORKDAY và WORKDAY.INTL các chức năng cho các hoạt động đó.

Không.	Mô tả chức năng
1.	WORKDAY Trả về số sê-ri của ngày trước hoặc sau một số ngày làm việc cụ thể
2.	WORKDAY.INTL Trả về số sê-ri của ngày trước hoặc sau một số ngày làm việc cụ thể bằng cách sử dụng các tham số để cho biết ngày nào là ngày cuối tuần và bao nhiêu ngày là ngày cuối tuần

Ví dụ, bạn có thể chỉ định 15 ^ngày ngày làm việc kể từ ngày hôm nay (ảnh chụp màn hình dưới đây được chụp vào ngày 16 ^tháng tháng 5 năm 2016) bằng cách sử dụng chức năng TODAY và WORKDAY.

Giả sử 25 ^ngày tháng 5 năm 2016 và 1 ^st tháng sáu năm 2016 là ngày lễ. Sau đó, tính toán của bạn sẽ như sau:

Tùy chỉnh định nghĩa về một ngày cuối tuần

Theo mặc định, cuối tuần là thứ bảy và chủ nhật, tức là hai ngày. Bạn cũng có thể tùy chọn xác định ngày cuối tuần của mình vớiWORKDAY.INTLchức năng. Bạn có thể chỉ định ngày cuối tuần của riêng mình bằng một số cuối tuần tương ứng với các ngày cuối tuần như được cho trong bảng bên dưới. Bạn không cần nhớ những con số này, vì khi bạn bắt đầu nhập hàm, bạn sẽ nhận được danh sách các số và ngày cuối tuần trong danh sách thả xuống.

Ngày cuối tuần	Số cuối tuần
Thứ bảy Chủ Nhật	1 hoặc bỏ qua
Chủ nhật thứ hai	2
Thứ hai thứ ba	3
Thứ ba thứ tư	4
Thứ tư thứ năm	5
Thư năm thư sau	6
Thứ sáu, thứ bảy	7
Chỉ chủ nhật	11
Chỉ thứ hai	12
Chỉ thứ ba	13
Chỉ thứ tư	14
Chỉ thứ năm	15
Chỉ thứ sáu	16
Chỉ thứ bảy	17

Giả sử, nếu ngày cuối tuần chỉ là thứ Sáu, bạn cần sử dụng số 16 trong hàm WORKDAY.INTL.

Số ngày làm việc giữa hai ngày nhất định

Có thể có một yêu cầu để tính số ngày làm việc giữa hai ngày, ví dụ, trong trường hợp tính toán khoản thanh toán cho một nhân viên hợp đồng được trả lương theo ngày.

Bạn có thể tìm số ngày làm việc giữa hai ngày bằng các hàm Excel NETWORKDAYS và NETWORKDAYS.INTL. Cũng giống như trong trường hợp WORKDAYS và WORKDAYS.INTL, NETWORKDAYS và NETWORKDAYS.INTL cho phép bạn chỉ định ngày nghỉ và với NETWORKDAYS.INTL, bạn có thể chỉ định thêm ngày cuối tuần.

Không.	Mô tả chức năng
1.	NETWORKDAYS Trả về số ngày làm việc của cả hai ngày
2.	NETWORKDAYS.INTL Trả về số ngày làm việc toàn bộ giữa hai ngày sử dụng các tham số để cho biết ngày nào và ngày nào là ngày cuối tuần

Bạn có thể tính toán số ngày làm việc từ hôm nay đến một ngày khác bằng các hàm TODAY và NETWORKDAYS. Trong ảnh chụp màn hình đưa ra dưới đây, hôm nay là 16 ^ngày tháng năm 2016 và ngày kết thúc là 16 ^ngày tháng sáu năm 2016. 25 ^ngày tháng năm 2016 và 1 ^st tháng 6 năm 2016 là ngày lễ.

Một lần nữa, cuối tuần được giả định là thứ bảy và chủ nhật. Bạn có thể có định nghĩa của riêng mình cho ngày cuối tuần và tính số ngày làm việc giữa hai ngày bằng hàm NETWORKDAYS.INTL. Trong ảnh chụp màn hình được đưa ra bên dưới, chỉ thứ Sáu được xác định là cuối tuần.

Trích xuất Năm, Tháng, Ngày từ Ngày

Bạn có thể trích xuất từ mỗi ngày trong danh sách ngày, ngày, tháng và năm tương ứng bằng cách sử dụng các hàm excel DAY, MONTH và YEAR.

Ví dụ, hãy xem xét những ngày sau:

Từ mỗi ngày này, bạn có thể trích xuất ngày, tháng và năm như sau:

Trích ngày trong tuần từ ngày

Bạn có thể trích xuất từ mỗi ngày trong danh sách ngày, ngày tương ứng trong tuần bằng hàm WEEKDAY của Excel.

Hãy xem xét cùng một ví dụ được đưa ra ở trên.

Ngày lấy từ Năm, Tháng và Ngày

Dữ liệu của bạn có thể có thông tin về Năm, Tháng và Ngày riêng biệt. Bạn cần lấy ngày kết hợp ba giá trị này để thực hiện bất kỳ phép tính nào. Bạn có thể sử dụng hàm DATE để nhận các giá trị ngày.

Hãy xem xét dữ liệu sau:

Sử dụng hàm DATE để nhận các giá trị DATE.

Tính toán năm, tháng và ngày giữa hai ngày

Bạn có thể phải tính toán thời gian trôi đi từ một ngày nhất định. Bạn có thể cần thông tin này dưới dạng năm, tháng và ngày. Một ví dụ đơn giản là tính tuổi hiện tại của một người. Nó thực sự là sự khác biệt giữa ngày sinh và ngày hôm nay. Bạn có thể sử dụng các hàm DATEDIF, TODAY và CONCATENATE trong Excel cho mục đích này.

Kết quả như sau: