Có nhiều cách để chuyển đổi ngày tháng được định dạng dưới dạng văn bản thành ngày tháng thực tế trong Excel và hướng dẫn này nhằm mục đích khám phá tất cả chúng.
Khi bạn nhập dữ liệu vào Excel, dữ liệu có thể ở nhiều dạng khác nhau mà Excel không nhận dạng được. Đôi khi khi ngày tháng được nhập từ một nguồn bên ngoài, chúng sẽ có định dạng dưới dạng văn bản.
Cũng cần biết rằng Excel áp dụng các định dạng ngày tháng dựa trên cài đặt khu vực hệ thống của bạn. Vì vậy, khi bạn nhập dữ liệu vào Excel từ một quốc gia khác, Excel có thể không nhận ra chúng và lưu trữ chúng dưới dạng mục nhập văn bản.
Nếu bạn gặp sự cố này, có nhiều cách bạn có thể sử dụng để chuyển đổi văn bản thành ngày tháng trong Excel và chúng tôi sẽ trình bày tất cả chúng trong hướng dẫn này.
Các phương pháp chuyển đổi văn bản thành ngày tháng
Nếu trang tính của bạn chứa các ngày được định dạng dưới dạng văn bản, thay vì các ngày thực tế, thì bạn không thể sử dụng chúng trong bất kỳ phép tính nào. Vì vậy, bạn cần chuyển đổi chúng về ngày tháng thực tế.
Nếu bạn thấy ngày của mình được căn bên trái có nghĩa là chúng được định dạng dưới dạng văn bản vì văn bản được căn trái theo mặc định. Và số và ngày luôn được căn chỉnh sang phải.
Có một số cách khác nhau để bạn có thể chuyển đổi văn bản thành ngày tháng trong Excel, đó là:
- Kiểm tra lỗi tùy chọn
- Tính năng chuyển văn bản thành cột trong Excel
- Tìm thay thế
- Dán công cụ đặc biệt
- Công thức và hàm Excel
Chuyển đổi văn bản thành ngày tháng bằng tùy chọn kiểm tra lỗi
Excel có một tính năng kiểm tra lỗi tích hợp giúp phát hiện một số lỗi rõ ràng trong dữ liệu của bạn. Nếu nó tìm thấy bất kỳ lỗi nào, nó sẽ hiển thị cho bạn một hình tam giác nhỏ màu xanh lục (chỉ báo lỗi) ở góc trên bên trái của ô có lỗi. Nếu bạn chọn ô đó, một dấu hiệu cảnh báo với dấu chấm than màu vàng sẽ bật lên. Khi bạn di chuyển con trỏ qua dấu hiệu đó và Excel sẽ thông báo cho bạn về sự cố có thể xảy ra với ô đó.
Ví dụ: khi bạn nhập năm ở định dạng hai chữ số vào ngày tháng của mình, Excel sẽ giả định ngày đó dưới dạng văn bản và lưu trữ dưới dạng văn bản. Và nếu bạn chọn ô đó, một dấu chấm than sẽ xuất hiện với cảnh báo: ‘Ô này chứa chuỗi ngày tháng chỉ được biểu thị bằng 2 chữ số của năm’.
Nếu các ô của bạn hiển thị chỉ báo lỗi đó, hãy nhấp vào dấu chấm than và nó sẽ hiển thị một vài tùy chọn để chuyển đổi ngày được định dạng dưới dạng văn bản thành ngày thực tế. Excel sẽ hiển thị cho bạn các tùy chọn để chuyển đổi nó thành 19XX hoặc 20XX (19XX cho 1915, 20XX cho 2015). Chọn tùy chọn thích hợp.
Sau đó, văn bản sẽ được chuyển đổi thành định dạng ngày tháng thích hợp.
Cách bật tùy chọn kiểm tra lỗi trong Excel
Thông thường, tùy chọn Kiểm tra Lỗi được bật trong Excel theo mặc định. Nếu tính năng kiểm tra lỗi không hoạt động với bạn, bạn cần bật Kiểm tra lỗi trong Excel.
Để thực hiện việc này, hãy nhấp vào tab ‘Tệp’ và chọn ‘Tùy chọn’ trong bảng điều khiển bên trái.
Trên cửa sổ Tùy chọn Excel, nhấp vào 'Công thức' trong bảng điều khiển bên trái và trong bảng điều khiển bên phải, bật 'Bật kiểm tra lỗi nền' trong phần Kiểm tra lỗi. Và đánh dấu vào 'Các ô chứa năm được biểu thị bằng 2 chữ số' trong phần Quy tắc kiểm tra lỗi.
Chuyển đổi văn bản thành ngày tháng bằng cách sử dụng Excel văn bản thành tính năng cột
Văn bản thành cột là một tính năng tuyệt vời trong Excel cho phép bạn chia dữ liệu thành nhiều cột và nó cũng là một công cụ mạnh mẽ để chuyển đổi giá trị văn bản thành giá trị ngày tháng. Phương pháp này nhận dạng một số định dạng dữ liệu khác nhau và chuyển đổi chúng thành định dạng ngày tháng thích hợp.
Chuyển đổi chuỗi văn bản đơn giản thành ngày tháng
Giả sử ngày tháng của bạn được định dạng dưới dạng chuỗi văn bản như sau:
Bạn có thể sử dụng trình hướng dẫn Văn bản thành Cột để nhanh chóng định dạng lại tất cả chúng theo ngày tháng.
Đầu tiên, hãy chọn phạm vi mục nhập văn bản bạn muốn chuyển đổi thành ngày tháng. Sau đó, chuyển đến tab ‘Dữ liệu’ trong Ruy-băng và nhấp vào tùy chọn ‘Văn bản thành cột’ trong nhóm Công cụ dữ liệu.
Trình hướng dẫn Văn bản thành Cột sẽ xuất hiện. Trên bước 1 của trình hướng dẫn Chuyển văn bản thành cột, hãy chọn tùy chọn 'Phân cách' trong Loại dữ liệu gốc và nhấp vào 'Tiếp theo'.
Ở bước 2, bỏ chọn tất cả các hộp ‘Dấu phân cách’ và nhấp vào Tiếp theo.
Trong bước cuối cùng của trình hướng dẫn, hãy chọn ‘Ngày’ trong Định dạng dữ liệu cột và chọn định dạng ngày của bạn từ danh sách thả xuống tiếp theo ‘Ngày’ và nhấp vào nút ‘Kết thúc’. Trong trường hợp của chúng tôi, chúng tôi đang chuyển đổi ngày văn bản được biểu thị là “01 02 1995” (ngày tháng năm), vì vậy chúng tôi chọn ‘DMY’ từ danh sách thả xuống ‘Date:’.
Bây giờ, Excel chuyển đổi ngày văn bản của bạn thành ngày thực tế và hiển thị chúng được căn phải trong các ô.
Ghi chú: Trước khi bạn bắt đầu sử dụng tính năng Văn bản thành Cột, hãy đảm bảo tất cả các chuỗi văn bản của bạn có định dạng giống hệt nhau, nếu không văn bản sẽ không được chuyển đổi. Ví dụ: nếu một số ngày văn bản của bạn được định dạng như định dạng tháng / ngày / năm (MDY) trong khi những ngày khác là ngày / tháng / năm (DMY) và khi bạn chọn ‘DMY’ ở bước 3, bạn sẽ nhận được kết quả không chính xác. Giống như nó được hiển thị trong hình ảnh dưới đây.
Chuyển đổi chuỗi văn bản phức tạp thành ngày tháng
Tính năng Text to Columns rất hữu ích khi bạn muốn chuyển đổi các chuỗi văn bản phức tạp thành ngày tháng. Nó cho phép bạn sử dụng các dấu phân cách để xác định vị trí dữ liệu của bạn sẽ được phân chia và hiển thị trên 2 hoặc nhiều cột. Và kết hợp các phần chia nhỏ của ngày tháng thành toàn bộ ngày tháng bằng cách sử dụng hàm DATE.
Ví dụ: nếu ngày của bạn được hiển thị trong chuỗi văn bản nhiều phần, như sau:
Thứ tư, ngày 01 tháng hai năm 2020
Ngày 1 tháng 2 năm 2020, 4 giờ 10 chiều
Bạn có thể sử dụng trình hướng dẫn Văn bản thành cột để phân tách thông tin ngày, tháng và giờ được phân tách bằng dấu phẩy và hiển thị chúng trên nhiều cột.
Đầu tiên, chọn tất cả các chuỗi văn bản mà bạn muốn chuyển đổi thành ngày tháng. Nhấp vào nút "Văn bản thành cột" trên tab "Dữ liệu". Trên bước 1 của trình hướng dẫn Chuyển văn bản thành cột, hãy chọn tùy chọn 'Phân cách' trong Loại dữ liệu gốc và nhấp vào 'Tiếp theo'.
Trong bước 2 của trình hướng dẫn, chọn dấu phân cách các chuỗi văn bản của bạn chứa và nhấp vào 'Tiếp theo'. Các chuỗi văn bản mẫu của chúng tôi được phân tách bằng dấu phẩy và dấu cách - “Thứ Hai, ngày 1 tháng 2 năm 2015, 1:00 chiều”. Chúng ta nên chọn ‘Dấu phẩy’ và ‘dấu cách’ làm dấu phân cách để chia chuỗi văn bản thành nhiều cột.
Ở bước cuối cùng, hãy chọn định dạng ‘Chung’ cho tất cả các cột trong phần Xem trước dữ liệu. Chỉ định vị trí các cột sẽ được chèn vào trường "Đích", nếu bạn không chèn, nó sẽ ghi đè lên dữ liệu ban đầu. Nếu bạn muốn bỏ qua một số phần của dữ liệu gốc, hãy nhấp vào phần đó trong phần Xem trước dữ liệu và chọn tùy chọn ‘Không nhập cột (bỏ qua)’. Sau đó, nhấp vào ‘Hoàn tất’.
Giờ đây, các phần của ngày (ngày trong tuần, tháng, năm, giờ) được chia thành các cột B, C, D, E, F và G.
Sau đó, kết hợp các phần ngày với nhau với sự trợ giúp của công thức DATE để có được toàn bộ ngày.
Cú pháp của hàm DATE trong Excel:
= DATE (năm, tháng, ngày)Trong ví dụ của chúng tôi, phần tháng, ngày và năm lần lượt nằm trong cột C, D và E.
Hàm DATE chỉ nhận dạng số không phải văn bản. Vì giá trị tháng của chúng tôi trong cột C là tất cả các chuỗi văn bản, chúng tôi cần chuyển đổi chúng thành số. Để làm được điều đó, bạn cần sử dụng chức năng THÁNG để đổi tên tháng thành số của tháng.
Để chuyển đổi tên của tháng thành số của tháng, hãy sử dụng hàm MONTH này bên trong hàm DATE:
= THÁNG (1 & C1)Hàm MONTH thêm 1 vào ô C2 chứa tên tháng để chuyển tên tháng thành số tháng tương ứng.
Đây là hàm DATE mà chúng ta cần sử dụng để kết hợp các phần ngày từ các cột khác nhau:
= DATE (E1, MONTH (1 & C1), D1)
Bây giờ sử dụng chốt điều khiển điền ở góc dưới cùng của ô công thức và áp dụng công thức cho cột.
Chuyển đổi văn bản thành ngày tháng bằng cách sử dụng phương pháp tìm và thay thế
Phương pháp này sử dụng dấu phân cách để thay đổi định dạng của văn bản thành ngày tháng. Nếu ngày, tháng và năm trong các ngày của bạn được phân tách bằng một số dấu phân cách khác với dấu gạch ngang (-) hoặc dấu gạch chéo (/), Excel sẽ không nhận ra chúng là ngày tháng và sẽ tiếp tục và lưu trữ chúng dưới dạng văn bản.
Để khắc phục sự cố này, hãy sử dụng tính năng Tìm và Thay thế. Bằng cách thay đổi dấu phân cách không chuẩn (.) Bằng dấu gạch chéo (/) hoặc dấu gạch ngang (-), Excel sẽ tự động thay đổi giá trị thành ngày tháng.
Đầu tiên, chọn tất cả các ngày văn bản bạn muốn chuyển đổi thành ngày tháng. Trên tab 'Trang chủ', nhấp vào nút 'Tìm & Chọn' ở góc ngoài cùng bên phải của Ruy-băng và chọn 'Thay thế'. Ngoài ra, hãy nhấn Ctrl + H để mở hộp thoại Tìm và Thay thế.
Trong hộp thoại Tìm và Thay thế, hãy nhập dấu phân cách mà văn bản của bạn chứa (trong trường hợp của chúng tôi là dấu chấm đầy đủ (.) Trong trường 'Tìm gì' và dấu gạch chéo lên (/) hoặc dấu gạch ngang (-) trong trường 'Thay thế bằng' Nhấp vào nút 'Thay thế Tất cả' để thay thế các dấu phân cách và nhấp vào 'Đóng' để đóng cửa sổ.
Bây giờ, Excel nhận ra rằng các chuỗi văn bản của bạn hiện là ngày tháng và nó tự động định dạng chúng thành ngày tháng. Ngày của bạn sẽ được căn ngay như hình bên dưới.
Chuyển đổi văn bản thành ngày tháng bằng cách sử dụng công cụ dán đặc biệt
Một cách nhanh chóng và dễ dàng khác để chuyển đổi chuỗi văn bản thành ngày tháng là thêm 0 vào chuỗi văn bản bằng cách sử dụng tùy chọn dán đặc biệt. Việc thêm 0 vào giá trị sẽ chuyển văn bản thành số sê-ri của ngày mà bạn có thể định dạng thành ngày.
Đầu tiên, chọn một ô trống và sao chép nó (chọn nó và nhấn Ctrl + C sao chép).
Sau đó, chọn các ô chứa ngày văn bản bạn muốn chuyển đổi, nhấp chuột phải và chọn tùy chọn ‘Dán đặc biệt’.
Trong hộp thoại Dán Đặc biệt, chọn ‘Tất cả’ trong phần Dán, chọn ‘Thêm’ trong phần Thao tác và nhấp vào ‘OK’.
Bạn cũng có thể thực hiện các phép tính số học khác trừ / nhân / chia giá trị trong ô đích với giá trị đã dán (như nhân các ô với 1 hoặc chia cho 1 hoặc trừ 0).
Khi bạn chọn 'Thêm' trong Thao tác, nó sẽ thêm 'không' vào tất cả các ngày văn bản đã chọn, vì việc thêm '0' không thay đổi giá trị, bạn sẽ nhận được số sê-ri cho mỗi ngày. Bây giờ tất cả những gì bạn phải làm là thay đổi định dạng của các ô.
Chọn số sê-ri và trên tab ‘Trang chủ’, nhấp vào danh sách thả xuống ‘Định dạng số’ trong nhóm Số. Chọn, tùy chọn 'Ngày ngắn hạn' từ trình đơn thả xuống.
Như bạn có thể thấy bây giờ các con số được định dạng dưới dạng ngày tháng và căn chỉnh bên phải.
Chuyển đổi văn bản thành ngày tháng bằng công thức
Có hai hàm chủ yếu được sử dụng để chuyển đổi văn bản thành ngày: DATEVALUE và VALUE.
Sử dụng hàm DATEVALUE trong Excel
Hàm DATEVALUE trong Excel là một trong những cách dễ nhất để chuyển đổi ngày được biểu thị dưới dạng văn bản thành số sê-ri của ngày.
Cú pháp của hàm DATEVALUE:
= DATEVALUE (date_text)Lý lẽ: date_text chỉ định chuỗi văn bản bạn muốn ẩn hoặc tham chiếu đến ô chứa ngày văn bản.
Công thức:
= DATEVALUE (A1)Hình ảnh sau đây minh họa cách hàm DATEVALUE xử lý một số định dạng ngày tháng khác nhau được lưu trữ dưới dạng văn bản.
Bạn đã có số sê-ri ngày, bây giờ bạn cần áp dụng định dạng Ngày cho những số này. Để làm điều đó, hãy chọn các ô có số sê-ri, sau đó chuyển đến tab ‘Trang chủ’ và chọn ‘Ngày ngắn’ từ danh sách thả xuống ‘Định dạng số’.
Bây giờ bạn có ngày tháng đã được định dạng trong cột C.
Ngay cả khi không có phần năm trong ngày văn bản của bạn (A8), DATEVALUE sẽ sử dụng năm hiện tại từ đồng hồ trên máy tính của bạn.
Hàm DATEVALUE sẽ chỉ chuyển đổi các giá trị văn bản giống như một ngày. Nó không thể chuyển đổi một văn bản giống với một số thành ngày tháng, cũng như không thể thay đổi một giá trị số thành ngày tháng, vì vậy bạn sẽ cần hàm VALUE của Excel.
Sử dụng hàm VALUE trong Excel
Hàm VALUE trong Excel có thể chuyển đổi bất kỳ chuỗi văn bản nào giống với ngày hoặc số thành giá trị số, vì vậy, hàm này thực sự hữu ích khi chuyển đổi bất kỳ số nào, không chỉ ngày.
Hàm VALUE:
= VALUE (văn bản)chữ- chuỗi văn bản mà chúng ta muốn chuyển đổi hoặc tham chiếu đến ô chứa chuỗi văn bản.
Công thức ví dụ để chuyển đổi ngày văn bản:
= VALUE (A1)Công thức VALUE dưới đây có thể thay đổi bất kỳ chuỗi văn bản nào giống ngày thành một số như được hiển thị bên dưới.
Tuy nhiên, hàm VALUE không hỗ trợ tất cả các loại giá trị ngày. Ví dụ: nếu ngày sử dụng vị trí thập phân (A11), nó sẽ trả về giá trị lỗi #VALUE! lỗi.
Khi bạn đã có số sê-ri cho ngày của mình, bạn sẽ cần phải định dạng ô có số sê-ri ngày để làm cho nó giống như một ngày như chúng ta đã thực hiện cho hàm DATEVALUE. Để làm điều đó, hãy chọn các số sê-ri và chọn tùy chọn ‘Ngày’ từ trình đơn thả xuống ‘Định dạng Số’ trong tab ‘Trang chủ’.
Vậy đó, đây là 5 cách khác nhau để bạn có thể chuyển đổi ngày được định dạng dưới dạng văn bản thành ngày tháng trong Excel.