Cách chuyển đổi văn bản thành giá trị ngày tháng trong Microsoft Excel

Phân tích dữ liệu kinh doanh thường yêu cầu làm việc với các giá trị ngày trong Excel để trả lời các câu hỏi như “hôm nay chúng ta kiếm được bao nhiêu tiền” hoặc “điều này so với cùng ngày trong tuần trước như thế nào?” Và điều đó có thể khó khăn khi Excel không nhận ra các giá trị là ngày tháng.

Thật không may, điều này không có gì lạ, đặc biệt khi nhiều người dùng đang nhập thông tin này, sao chép và dán từ các hệ thống khác và nhập từ cơ sở dữ liệu.

Trong bài viết này, chúng tôi sẽ mô tả bốn trường hợp khác nhau và các giải pháp để chuyển đổi văn bản thành giá trị ngày tháng.

Ngày có dấu “.” để phân tách ngày, tháng và năm

Có lẽ một trong những lỗi phổ biến nhất mà người mới bắt đầu mắc phải khi nhập ngày tháng vào Excel là làm như vậy với ký tự dừng đầy đủ để phân tách ngày, tháng và năm.

Excel sẽ không nhận ra đây là một giá trị ngày tháng và sẽ tiếp tục và lưu trữ nó dưới dạng văn bản. Tuy nhiên, bạn có thể giải quyết vấn đề này bằng công cụ Tìm và Thay thế. Bằng cách thay thế các điểm dừng đầy đủ bằng dấu gạch chéo (/), Excel sẽ tự động xác định các giá trị dưới dạng ngày tháng.

Chọn các cột mà bạn muốn thực hiện tìm và thay thế.

Ngày có dấu phân cách dừng đầy đủ

Nhấp vào Trang chủ> Tìm & Chọn> Thay thế — hoặc nhấn Ctrl + H.

Tìm và chọn các giá trị trong một cột

Trong cửa sổ Tìm và Thay thế, hãy nhập dấu chấm đầy đủ (.) Vào trường “Tìm gì” và dấu gạch chéo (/) trong trường “Thay thế bằng”. Sau đó, nhấp vào “Thay thế tất cả”.

điền vào các giá trị tìm và thay thế

Tất cả các điểm dừng đầy đủ được chuyển đổi thành dấu gạch chéo và Excel nhận dạng định dạng mới là ngày tháng.

Ngày có đầy đủ điểm dừng được chuyển đổi thành ngày thực

Nếu dữ liệu bảng tính của bạn thường xuyên thay đổi và bạn muốn có một giải pháp tự động cho trường hợp này, bạn có thể sử dụng hàm SUBSTITUTE .

= VALUE (SUBSTITUTE (A2, ".", "/"))

Hàm SUBSTITUTE là một hàm văn bản, vì vậy không thể tự chuyển đổi nó thành ngày tháng. Hàm VALUE sẽ chuyển giá trị văn bản thành giá trị số.

Công thức SUBSTITUTE để chuyển đổi văn bản thành ngày tháng

Bạn có thể làm điều này bằng cách sử dụng danh sách “Định dạng số” trên tab “Trang chủ”.

Định dạng số dưới dạng ngày tháng

Ví dụ ở đây về dấu phân cách đầy đủ là điển hình. Nhưng bạn có thể sử dụng kỹ thuật tương tự để thay thế hoặc thay thế bất kỳ ký tự phân tách nào.

Chuyển đổi định dạng yyyymmdd

Nếu bạn nhận được ngày ở định dạng hiển thị bên dưới, nó sẽ yêu cầu một cách tiếp cận khác.

Ngày ở định dạng yyyymmdd

Định dạng này khá tiêu chuẩn trong công nghệ vì nó loại bỏ bất kỳ sự mơ hồ nào về cách các quốc gia khác nhau lưu trữ giá trị ngày của họ. Tuy nhiên, Excel ban đầu sẽ không hiểu nó.

Để có giải pháp thủ công nhanh chóng, bạn có thể sử dụng Văn bản thành Cột .

Nút Văn bản thành Cột từ tab Dữ liệu

Trình hướng dẫn Văn bản thành Cột xuất hiện. Nhấp vào “Tiếp theo” ở hai bước đầu tiên để bạn đang ở bước thứ ba, như được hiển thị trong hình ảnh bên dưới. Chọn Ngày và sau đó chọn định dạng ngày được sử dụng trong các ô từ danh sách. Trong ví dụ này, chúng tôi đang xử lý định dạng YMD.

Văn bản thành cột để chuyển đổi số 8 chữ số thành ngày tháng

Nếu bạn muốn một giải pháp công thức, thì bạn có thể sử dụng hàm Date để xây dựng ngày.

Điều này sẽ được sử dụng cùng với các hàm văn bản Left, Mid và Right để trích xuất ba phần của một ngày (ngày, tháng, năm) từ nội dung ô.

Công thức dưới đây hiển thị công thức này bằng cách sử dụng dữ liệu mẫu của chúng tôi.

= DATE (LEFT (A2,4), MID (A2,5,2), RIGHT (A2,2))

Sử dụng công thức DATE với 8 chữ số

Sử dụng một trong các kỹ thuật này, bạn có thể chuyển đổi bất kỳ giá trị số có tám chữ số nào. Ví dụ: bạn có thể nhận được ngày ở định dạng ddmmyyyy hoặc định dạng mmddyyyy.

Hàm DATEVALUE và VALUE

Đôi khi sự cố không phải do ký tự phân tách mà có cấu trúc ngày khó xử đơn giản vì nó được lưu trữ dưới dạng văn bản.

Ngày được lưu trữ dưới dạng văn bản

Đối với những tình huống này, có thể dễ dàng chuyển đổi bằng nhiều kỹ thuật khác nhau.

Đối với bài viết này, tôi muốn đề cập đến hai chức năng để xử lý các tình huống này. Chúng là DATEVALUE và VALUE.

Hàm DATEVALUE sẽ chuyển đổi văn bản thành giá trị ngày tháng (có thể đã thấy điều đó sắp xảy ra), trong khi hàm VALUE sẽ chuyển văn bản thành giá trị số chung. Sự khác biệt giữa chúng là tối thiểu.

Trong hình trên, một trong các giá trị cũng chứa thông tin thời gian. Và đó sẽ là một minh chứng cho sự khác biệt nhỏ của các chức năng.

= DATEVALUE (A2)

Hàm DATEVALUE để chuyển đổi thành giá trị ngày tháng

Lưu ý rằng thời gian đã bị xóa khỏi kết quả ở hàng 4. Công thức này hoàn toàn chỉ trả về giá trị ngày. Kết quả sẽ vẫn cần được định dạng dưới dạng ngày tháng.

Công thức sau sử dụng hàm VALUE.

= VALUE (A2)

Hàm VALUE để chuyển đổi văn bản thành giá trị số

Công thức này sẽ tạo ra kết quả tương tự ngoại trừ ở hàng 4, nơi giá trị thời gian cũng được duy trì.

Sau đó, kết quả có thể được định dạng là ngày và giờ hoặc là ngày để ẩn giá trị thời gian (nhưng không xóa).

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *