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

Thật không may, điều này diễn ra khá phổ biến, đặc biệt là khi nhiều người dùng nhập các thông tin này, sao chép và dán từ các hệ thống khác hay nhập từ cơ sở dữ liệu.

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

Ngày tháng có ký tự dừng

Có lẽ một trong những lỗi phổ biến nhất mà nhiều người dùng mắc phải khi nhập giá trị ngày tháng vào Excel là nhập ký tự dừng (dấm chấm) để phân tách ngày, tháng và năm.

Excel sẽ không nhận ra đây là giá trị ngày tháng và sẽ tiếp tục và lưu trữ dữ liệu này dưới dạng văn bản. Tuy nhiên, người dùng có thể giải quyết vấn đề này bằng công cụ Tìm và Thay thế (Find and Replace). Bằng cách thay thế ký tự dừng bằng dấu gạch chéo (/), Excel sẽ tự động xác định các giá trị theo định 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ế.

Bấm Trang chủ (Home) > Tìm & Chọn (Find & Select) > Thay thế (Replace) hoặc nhấn tổ hợp phím Ctrl H.

Trong cửa sổ Tìm và Thay thế, nhập ký tự dừng (.) trong trường Tìm (Find what) và nhập ký tự dấu gạch chéo (/) trong trường Thay thế bằng (Replace with). Sau đó, nhấp vào Thay thế tất cả (Replace All).

Tất cả các ký tự dừng đã được chuyển đổi thành dấu gạch chéo, và Excel sẽ nhận ra định dạng mới là một giá trị ngày tháng.

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 kịch bản 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, do đó không thể tự chuyển đổi nó thành định dạng ngày. Hàm VALUE sẽ chuyển đổi giá trị văn bản thành giá trị số.

Các kết quả được hiển thị dưới đây. Giá trị cần được định dạng thành một ngày tháng.

Bạn có thể thực hiện việc này bằng cách sử dụng danh sách “Định dạng Số” (Number Format) trên Home tab.

Ví dụ về ký tự dừng trong bài là một trường hợp rất điển hình và phổ biến. Nhưng bạn có thể sử dụng cùng một kỹ thuật để thay thế bất kỳ ký tự phân cách nào.

Chuyển đổi định dạng yyyymmdd (năm, tháng, ngày)

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.

Định dạng này được coi là 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 được nó.

Đối với giải pháp thủ công nhanh, bạn có thể sử dụng Chuyển Văn bản thành Cột (Text to Columns).

Chọn phạm vi giá trị bạn cần chuyển đổi, sau đó bấm Dữ liệu (Data) > Văn bản thành Cột (Text to Columns).

Khi Trình hướng dẫn Text to Columns xuất hiện. Nhấp vào Tiếp theo ở hai bước đầu tiên để bạn đi đến bước ba như trong hình bên dưới. Chọn Ngày (Date) và sau đó chọn định dạng ngày tháng được sử dụng trong các ô từ danh sách. Trong ví dụ này, các chuyên gia đang xử lý một định dạng YMD (Năm, tháng, ngày).

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

Hàm này sẽ được sử dụng cùng với các chức năng văn bản Trái, Giữa và Phải để trích xuất ba phần của một ngày tháng (ngày, tháng, năm) từ nội dung của ô.

Biểu dưới đây cho thấy công thức này sử dụng từ dữ liệu mẫu của bài viết.

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

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

Hàm DATEVALUE và VALUE

Đôi khi vấn đề không phải do một ký tự phân cách mà dữ liệu có cấu trúc ngày lạ chỉ vì nó được lưu dưới dạng văn bản.

Dưới đây là danh sách các ngày tháng trong một loạt các cấu trúc, và người dùng đều có thể nhận ra chúng thể hiện giá trị ngày tháng. Thật không may, chúng đã được lưu trữ dưới dạng văn bản và cần chuyển đổi.

Đối với các kịch bản này, thật 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, chuyên gia muốn đề cập đến hai chức năng để xử lý các tình huống này. Các hàm đó chính là DATEVALUE và VALUE.

Hàm DATEVALUE sẽ chuyển đổi văn bản thành giá trị ngày tháng, trong khi hàm VALUE sẽ chuyển đổi văn bản thành giá trị số nói chung. Sự khác biệt giữa chúng là không đáng kể.

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

Công thức DATEVALUE dưới đây sẽ chuyển đổi từng giá trị thành giá trị ngày.

=DATEVALUE(A2)

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

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

=VALUE(A2)

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

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

Trương Ngọc Huyền