Cách Chuyển Đổi Định Dạng Text Sang Date Trong Excel: Hướng Dẫn Chi Tiết Và Toàn Diện

Table of Contents

Thời gian đọc ước tính: 15 phút

Những điểm chính

Hiểu bản chất dữ liệu: Phân biệt rõ ràng giữa ngày tháng được lưu dưới dạng số (Serial Number) và chuỗi văn bản (Text String).
Công cụ tích hợp: Sử dụng tính năng Text to Columns và Paste Special để xử lý hàng loạt dữ liệu mà không cần công thức phức tạp.
Hàm chuyên dụng: Áp dụng linh hoạt các hàm DATEVALUE, VALUE, và SUBSTITUTE để chuẩn hóa dữ liệu.
Xử lý dữ liệu thô: Kỹ thuật tách chuỗi nâng cao để xử lý các định dạng ngày tháng kèm ký tự lạ, tên thứ hoặc viết liền không dấu.
Khắc phục lỗi: Giải quyết triệt để các lỗi phổ biến khi Excel không nhận diện được định dạng ngày tháng do khác biệt vùng miền (Region Settings).

Làm việc với dữ liệu thời gian là một nhu cầu thiết yếu đối với bất kỳ ai sử dụng Excel. Tuy nhiên, một trong những thách thức lớn nhất mà người dùng thường xuyên gặp phải là dữ liệu ngày tháng bị định dạng dưới dạng văn bản (Text). Điều này thường xảy ra khi xuất dữ liệu từ các phần mềm ERP, CRM hoặc sao chép từ web.

Khi ngày tháng ở định dạng Text, bạn không thể thực hiện các phép tính cộng trừ ngày, sắp xếp theo thời gian hoặc sử dụng các hàm thời gian như MONTH, YEAR. Bài viết này sẽ cung cấp các giải pháp kỹ thuật từ cơ bản đến nâng cao để chuyển đổi dữ liệu Text sang Date một cách chính xác nhất.

Bản Chất Của Dữ Liệu Ngày Tháng Trong Excel

Trước khi đi vào các phương pháp chuyển đổi cụ thể, chúng ta cần hiểu cơ chế lưu trữ của Excel. Về bản chất, Excel lưu trữ ngày tháng dưới dạng các con số sê-ri (Serial Numbers). Hệ thống mặc định của Excel trên Windows bắt đầu tính từ ngày 01/01/1900 (tương ứng với số 1).

Ví dụ: Ngày 01/01/2023 được lưu trữ trong bộ nhớ là số 44927.

Khi một giá trị ngày tháng được nhập dưới dạng văn bản (ví dụ: “01.01.2023” hoặc “2023/01/01” trên máy tính thiết lập chuẩn ngày khác), Excel sẽ xem đó là một chuỗi ký tự vô nghĩa về mặt toán học.

Dưới đây là bảng so sánh sự khác biệt cơ bản giúp bạn nhận diện nhanh tình trạng dữ liệu:

Ngày được lưu dưới dạng Số (Date)	Ngày được lưu dưới dạng Văn bản (Text)
Căn lề: Mặc định nằm bên phải ô.	Căn lề: Mặc định nằm bên trái ô.
Tính toán: Có thể cộng trừ, dùng trong công thức.	Tính toán: Không thể tính toán trực tiếp, kết quả trả về lỗi #VALUE!.
Định dạng: Có thể thay đổi hiển thị (DD/MM/YYYY, MM-DD…).	Định dạng: Không thay đổi được hiển thị qua Format Cells.
Thanh trạng thái: Hiển thị Sum, Average khi bôi đen.	Thanh trạng thái: Chỉ hiển thị Count (Đếm) khi bôi đen.

Việc nhận diện đúng loại dữ liệu giúp bạn lựa chọn phương pháp xử lý tối ưu nhất trong các phần tiếp theo.

Chuyển Đổi Text Sang Date Khi Dữ Liệu Đã Có Cấu Trúc Chuẩn

Trường hợp này xảy ra khi chuỗi văn bản trông giống hệt một ngày tháng hợp lệ (ví dụ: “01-01-2023”) nhưng ô đó đang được định dạng là Text. Excel có thể hiển thị cảnh báo màu xanh lá cây ở góc ô.

Sử Dụng Hàm DATEVALUE

Hàm DATEVALUE được thiết kế chuyên biệt để chuyển đổi một chuỗi văn bản ngày tháng thành số sê-ri mà Excel có thể hiểu được. Đây là phương pháp tiêu chuẩn và an toàn nhất để xử lý dữ liệu.

Công thức:

=DATEVALUE(date_text)

Giả sử bạn có dữ liệu ngày tháng dạng văn bản tại cột A. Tại cột B, bạn nhập công thức sau:

=DATEVALUE(A2)

Dữ liệu ngày tháng dạng văn bản cần chuyển đổi bằng hàm DATEVALUE

Sau khi áp dụng công thức, kết quả trả về sẽ là một dãy số (ví dụ: 44927).

Kết quả trả về của hàm DATEVALUE là số sê-ri

Để hiển thị lại dưới dạng ngày tháng quen thuộc, bạn cần thực hiện bước định dạng cuối cùng:

Bôi đen vùng kết quả.
Truy cập tab Home trên thanh công cụ.
Trong nhóm Number, chọn định dạng Short Date hoặc Long Date.

Chọn định dạng Short Date hoặc Long Date trên thanh công cụ

Lúc này, các con số vô nghĩa đã trở thành định dạng ngày tháng chuẩn xác và có thể dùng để tính toán.

Dữ liệu đã được định dạng lại thành ngày tháng hoàn chỉnh

Sử Dụng Hàm VALUE

Hàm VALUE có chức năng tổng quát hơn DATEVALUE. Nó chuyển đổi bất kỳ chuỗi ký tự nào đại diện cho số (bao gồm cả ngày giờ, tiền tệ) thành giá trị số thực.

Công thức:

=VALUE(text)

Áp dụng cho ví dụ trên:

=VALUE(A2)

Sử dụng hàm VALUE để chuyển đổi text sang số sê-ri ngày tháng

Quy trình định dạng lại ô (Format Cells) sau khi dùng hàm VALUE hoàn toàn tương tự như với DATEVALUE. Bạn có thể sử dụng tổ hợp phím Ctrl + 1 để mở hộp thoại Format Cells và tùy chỉnh định dạng hiển thị mong muốn.

Sử Dụng Phép Toán Học Đơn Giản

Một kỹ thuật “đường tắt” được nhiều chuyên gia Excel sử dụng là thực hiện phép tính toán học không làm thay đổi giá trị gốc. Khi bạn thực hiện phép tính (+, -, *, /) lên một chuỗi văn bản chứa số, Excel sẽ tự động ép kiểu dữ liệu đó về dạng số (Implicit Coercion).

Bạn có thể sử dụng một trong các công thức sau tại cột bên cạnh:

Cộng với 0: =A2 + 0
Nhân với 1: =A2 * 1
Chia cho 1: =A2 / 1
Dấu trừ kép (Double Unary): =--A2

Sử dụng phép cộng 0 để chuyển đổi nhanh dữ liệu sang dạng số

Sau khi có kết quả dạng số, bạn chỉ cần định dạng lại ô thành Date như hướng dẫn ở trên. Phương pháp này xử lý rất nhanh và không đòi hỏi nhớ tên hàm.

Sử Dụng Kỹ Thuật Paste Special (Không Dùng Cột Phụ)

Nếu bạn muốn chuyển đổi trực tiếp trên vùng dữ liệu gốc mà không muốn tạo thêm cột phụ chứa công thức, tính năng Paste Special là lựa chọn tối ưu. Phương pháp này cũng dựa trên nguyên lý toán học (cộng thêm 0) nhưng thao tác trực tiếp lên bộ nhớ đệm.

Quy trình thực hiện:

Nhập số 0 vào một ô trống bất kỳ và sao chép (Ctrl + C) ô đó.
Bôi đen toàn bộ vùng dữ liệu ngày tháng dạng Text cần chuyển đổi.
Nhấn chuột phải, chọn Paste Special.

Chọn tùy chọn Paste Special từ menu chuột phải

Trong hộp thoại hiện ra, tại mục Paste chọn Values, tại mục Operation chọn Add.
Nhấn OK.

Cấu hình hộp thoại Paste Special để thực hiện phép cộng

Kết quả là Excel sẽ cộng 0 vào tất cả các ô đã chọn, ép chúng chuyển sang dạng số sê-ri.

Dữ liệu ngày tháng đã được chuyển sang dạng số sau khi dùng Paste Special

Cuối cùng, bạn chỉ cần định dạng lại vùng dữ liệu này sang kiểu Date (Short Date hoặc Long Date) để hoàn tất.

Định dạng lại hiển thị ngày tháng sau khi chuyển đổi

Lưu ý: Phương pháp này rất hữu ích khi bạn cần xử lý tại chỗ một lượng lớn dữ liệu mà không muốn làm nặng file bằng các công thức mảng.

Xử Lý Dữ Liệu Ngày Tháng Không Theo Định Dạng Chuẩn

Trong thực tế, dữ liệu nhận được thường không sạch và chứa các ký tự phân cách lạ như dấu chấm (.), khoảng trắng, hoặc định dạng không khớp với Region Settings của máy tính (ví dụ: máy tính cài đặt DD/MM nhưng dữ liệu là MM.DD).

Thay Đổi Ký Tự Phân Cách Bằng Find & Replace

Dấu chấm (.) thường được dùng làm dấu phân cách ngày tháng ở một số quốc gia châu Âu, nhưng Excel mặc định (thường là US hoặc UK) lại không hiểu định dạng này (ví dụ: 15.01.2023).

Chúng ta cần thay thế dấu chấm bằng dấu gạch chéo (/) hoặc dấu gạch ngang (-).

Các bước thực hiện:

Bôi đen vùng dữ liệu cột A chứa ngày tháng lỗi.
Nhấn tổ hợp phím Ctrl + H để mở hộp thoại Find and Replace.
Tại ô Find what: nhập dấu chấm ..
Tại ô Replace with: nhập dấu gạch chéo / hoặc gạch ngang -.

Thiết lập tìm và thay thế dấu chấm bằng dấu gạch ngang

Nhấn Replace All.

Dữ liệu sau khi thay thế đã trở thành định dạng ngày tháng hợp lệ

Sau thao tác này, Excel sẽ tự động nhận diện lại dữ liệu. Bạn sẽ thấy dữ liệu tự động căn lề phải, báo hiệu chúng đã được hiểu là ngày tháng hợp lệ.

Tách Dữ Liệu Với Text To Columns

Text to Columns là một công cụ mạnh mẽ trong tab Data, cho phép bạn ép kiểu dữ liệu cho một cột cụ thể mà không cần dùng công thức. Đây là giải pháp “cứu cánh” cho những trường hợp định dạng ngày tháng bị đảo lộn (DMY so với MDY).

Quy trình chi tiết:

Chọn vùng dữ liệu cột A.
Vào tab Data > chọn Text to Columns.

Vị trí nút Text to Columns trên thanh công cụ Data

Bước 1: Chọn Delimited > Next.

Bước 1 của trình hướng dẫn Text to Columns

Bước 2: Bỏ chọn tất cả các ô trong mục Delimiters (nếu bạn không muốn tách cột) > Next.

Bước 2 đảm bảo không có ký tự phân cách nào được chọn

Bước 3 (Quan trọng nhất):
- Tại mục Column data format, chọn Date.
- Trong menu thả xuống bên cạnh, chọn định dạng tương ứng với nguồn dữ liệu gốc của bạn (ví dụ: nếu dữ liệu là 15.01.2023 thì chọn DMY; nếu là 01.15.2023 thì chọn MDY).
- Chọn ô đích tại Destination để xuất kết quả (ví dụ: $B$2).

Bước 3 chọn định dạng Date và cấu trúc ngày tháng tương ứng

Nhấn Finish.

Kết quả trả về sẽ là dữ liệu ngày tháng chuẩn xác được Excel nhận diện đúng theo hệ thống máy tính của bạn.

Kết quả sau khi sử dụng Text to Columns

Kết Hợp Hàm SUBSTITUTE

Nếu bạn cần giữ nguyên dữ liệu gốc và tạo ra kết quả ở cột mới thông qua công thức động (tự cập nhật khi dữ liệu nguồn thay đổi), hàm SUBSTITUTE kết hợp với DATEVALUE là giải pháp lập trình phù hợp.

Công thức này sẽ thay thế các ký tự phân cách không hợp lệ (như dấu chấm) thành hợp lệ, sau đó chuyển đổi sang số.

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

Sử dụng hàm SUBSTITUTE để thay thế dấu chấm trước khi chuyển đổi

Sau khi có kết quả số sê-ri, bạn thực hiện Format Cells sang Short Date như thường lệ. Phương pháp này đảm bảo tính toàn vẹn của dữ liệu gốc và phù hợp để xây dựng các báo cáo tự động (Dashboard).

Sửa Lỗi Năm 2 Chữ Số Bằng Error Checking

Đôi khi, dữ liệu nhập vào chỉ có 2 chữ số cuối của năm (ví dụ: 01-01-23 thay vì 2023). Excel có thể cảnh báo lỗi bằng dấu tam giác màu xanh ở góc ô.

Để sửa nhanh hàng loạt:

Bôi đen vùng dữ liệu có cảnh báo.
Nhấn vào biểu tượng dấu chấm than vàng (Error Checking) hiện ra bên cạnh.
Chọn XX to 20XX (ví dụ: Convert XX to 20XX).

Sử dụng tính năng Error Checking để sửa lỗi năm 2 chữ số

Thao tác này giúp chuẩn hóa dữ liệu về định dạng 4 chữ số cho năm, tránh hiểu nhầm giữa năm 1923 và 2023.

Xử Lý Các Chuỗi Ngày Tháng Phức Tạp

Dữ liệu thực tế đôi khi kèm theo các thành phần thừa như tên thứ, giờ giấc hoặc các hậu tố tiếng Anh (st, nd, rd, th).

Loại Bỏ Tên Thứ Trong Chuỗi Ngày

Ví dụ dữ liệu: “Saturday, 08 Oct 2022”. Excel không thể hiểu chuỗi này là ngày tháng vì có chữ “Saturday”. Chúng ta cần cắt bỏ phần tên thứ này.

Logic xử lý: Tìm vị trí dấu cách (space) đầu tiên, sau đó lấy toàn bộ phần văn bản phía sau nó.

Công thức kết hợp RIGHT, LEN, và FIND:

=DATEVALUE(RIGHT(A2, LEN(A2) - FIND(" ", A2)))

FIND(" ", A2): Tìm vị trí của dấu cách đầu tiên.
LEN(A2): Tính tổng độ dài chuỗi.
RIGHT(...): Lấy phần ký tự bên phải bắt đầu từ sau dấu cách.
DATEVALUE(...): Chuyển chuỗi kết quả thành ngày.

Công thức loại bỏ tên thứ và giữ lại ngày tháng

Xử Lý Hậu Tố (st, nd, rd, th)

Ví dụ dữ liệu: “1st January 2023”. Các hậu tố này làm hỏng định dạng ngày. Bạn cần dùng hàm SUBSTITUTE lồng nhau để loại bỏ lần lượt từng hậu tố này.

Công thức mẫu:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"th",""),"st",""),"rd",""),"nd",""))

Công thức trên hoạt động như một bộ lọc nhiều tầng, lần lượt thay thế “th”, “st”, “rd”, “nd” bằng ký tự rỗng (“”), trả về chuỗi sạch để DATEVALUE xử lý.

Công thức lồng nhau để loại bỏ các hậu tố ngày tháng tiếng Anh

Chuyển Đổi Chuỗi 8 Số Liền Nhau (DDMMYYYY)

Một định dạng phổ biến khác từ các hệ thống ngân hàng là chuỗi 8 số liền nhau: 01012023 (tức ngày 01 tháng 01 năm 2023). Excel hiểu đây là con số hơn 10 triệu chứ không phải ngày tháng.

Để xử lý, chúng ta cần cắt chuỗi này thành 3 phần (Ngày, Tháng, Năm) và ghép lại bằng hàm DATE.

Công thức:

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

RIGHT(A2,4): Lấy 4 số cuối làm Năm (2023).
MID(A2,3,2): Lấy 2 số ở giữa (từ vị trí thứ 3) làm Tháng (01).
LEFT(A2,2): Lấy 2 số đầu làm Ngày (01).
DATE(Năm, Tháng, Ngày): Hợp nhất các giá trị trên thành ngày chuẩn.

Sử dụng hàm DATE kết hợp hàm cắt chuỗi để xử lý định dạng 8 số

Phương pháp này đảm bảo độ chính xác tuyệt đối vì bạn kiểm soát được vị trí của từng thành phần thời gian.

Câu Hỏi Thường Gặp (FAQ)

Tại sao công thức DATEVALUE trả về lỗi #VALUE!?
Lỗi này xuất hiện khi chuỗi văn bản đầu vào không khớp với định dạng ngày tháng hệ thống của máy tính (System Region Settings). Ví dụ, máy tính bạn cài đặt định dạng tháng/ngày/năm (MDY) nhưng dữ liệu đầu vào là ngày/tháng/năm (DMY) (ví dụ: 31/01/2023 sẽ lỗi vì không có tháng 31). Hãy kiểm tra lại Control Panel hoặc dùng Text to Columns để khắc phục.

Làm thế nào để thay đổi định dạng ngày tháng mặc định của Excel?
Excel sử dụng định dạng của hệ điều hành Windows. Để thay đổi, bạn cần vào Control Panel > Region > Thẻ Formats > Chọn Short date mong muốn (ví dụ: dd/MM/yyyy). Sau đó khởi động lại Excel.

Tôi có thể dùng Macro/VBA để làm việc này tự động không?
Có. Bạn có thể ghi lại (Record Macro) thao tác Text to Columns hoặc viết một đoạn code VBA sử dụng hàm CDate() để chuyển đổi hàng loạt cho các báo cáo định kỳ.

Sự khác biệt giữa Format Cells và việc chuyển đổi dữ liệu là gì?
Format Cells chỉ thay đổi “lớp vỏ” hiển thị bên ngoài (cách bạn nhìn thấy dữ liệu). Chuyển đổi dữ liệu (như bài viết hướng dẫn) là thay đổi “lõi” dữ liệu từ dạng Text sang dạng Số (Serial Number) để máy tính có thể hiểu và tính toán được.

Nguồn Tham Khảo

Để tìm hiểu sâu hơn về các hàm và kỹ thuật xử lý dữ liệu trong Excel, bạn có thể tham khảo các nguồn uy tín sau:

Bằng cách áp dụng linh hoạt các phương pháp trên, bạn sẽ làm chủ hoàn toàn việc xử lý dữ liệu thời gian, giúp quy trình làm việc trên Excel trở nên chuyên nghiệp và hiệu quả hơn.