Có thể bạn quan tâm: Cách Chuyển Đổi File Excel 2003 Sang Excel 2026 Một Cách Nhanh Gọn Và Đảm Bảo An Toàn Dữ Liệu
Mở đầu
Trong công việc văn phòng, đặc biệt là khi xử lý dữ liệu tài chính, bán hàng hay quản lý dự án, chúng ta thường gặp phải các file Excel chứa chuỗi văn bản (string) đại diện cho ngày tháng. Những chuỗi này có thể xuất hiện dưới nhiều định dạng khác nhau: "2026-04-15", "15/04/2026", "April 15, 2026" hay thậm chí "15th April 2026". Khi dữ liệu được nhập dưới dạng chuỗi, Excel sẽ không nhận dạng chúng là ngày và do đó không thể thực hiện các phép tính thời gian, sắp xếp, lọc, hoặc sử dụng các hàm ngày tháng như DATEDIF, NETWORKDAYS, EOMONTH, v.v.
Bài viết này sẽ cung cấp hướng dẫn chi tiết, từng bước một, giúp bạn chuyển đổi chuỗi thành ngày trong Excel, đồng thời giải thích nguyên lý hoạt động, các hàm liên quan, và các mẹo tối ưu cho các trường hợp phức tạp. Nội dung sẽ bao gồm:
- Kiến thức nền tảng: Excel hiểu ngày tháng như thế nào.
- Các hàm chuẩn:
DATE,DATEVALUE,TEXT,VALUE,LEFT,MID,RIGHT,FIND,SUBSTITUTE,TEXTJOIN. - Công cụ chuyển đổi nhanh: tính năng Text to Columns, Flash Fill, Power Query.
- Công thức nâng cao: xử lý các định dạng không đồng nhất, chuỗi có tiếng Anh, tiếng Việt, ký tự đặc biệt.
- Sử dụng VBA: macro tự động chuyển đổi hàng loạt.
- Thực hành thực tế: 10 ví dụ thực tế với dữ liệu mẫu.
- Mẹo tối ưu: định dạng ô, kiểm tra lỗi, bảo trì dữ liệu.
Sau khi đọc xong, bạn sẽ có thể tự tin chuyển đổi bất kỳ chuỗi ngày nào sang dạng ngày chuẩn trong Excel, giảm thiểu lỗi dữ liệu và tăng hiệu suất làm việc.
Có thể bạn quan tâm: Cách Concatenate Trong Excel Với Dấu Phân Cám: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
1. Excel và cách lưu trữ ngày tháng
1.1. Số serial (serial number) của ngày
Excel không lưu trữ ngày dưới dạng chuỗi ký tự mà lưu trữ dưới dạng một số nguyên dương (đối với ngày) hoặc số thập phân (đối với ngày kèm thời gian). Số này được gọi là serial number. Ví dụ:
| Ngày | Serial |
|---|---|
| 1/1/1900 | 1 |
| 31/12/1899 | 0 (không hợp lệ trong Excel) |
| 15/04/2026 | 44932 |
| 15/04/2026 12:30 | 44932.5208333 |
Serial number đếm số ngày tính từ ngày 1/1/1900 (đối với hệ thống Windows) hoặc 1/1/1904 (đối với Mac). Khi một ô được định dạng là Date, Excel sẽ hiển thị giá trị serial dưới dạng ngày tháng dựa trên định dạng hiển thị.
1.2. Tại sao chuỗi không được công nhận?
Khi nhập "15/04/2026" vào một ô, Excel sẽ tự động nhận dạng nếu cài đặt ngôn ngữ/định dạng ngày của hệ thống phù hợp. Tuy nhiên, nếu chuỗi có:
- Định dạng không chuẩn (ví dụ:
"2026/04/15"vs"15-04-2026"). - Ký tự thừa (ví dụ:
"15/04/2026 "hoặc"Ngày 15/04/2026"). - Tên tháng bằng tiếng Anh/tiếng Việt không đồng nhất (
"Apr 15, 2026"vs"15 Tháng 4 2026").
thì Excel sẽ đánh dấu là văn bản và không cho phép thực hiện các phép tính ngày tháng.
Giải pháp: Chuyển đổi chuỗi thành serial number bằng các hàm hoặc công cụ chuyển đổi.
Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Tạo Mẫu Hóa Đơn Thương Mại (commercial Invoice) Chuẩn Trong Excel
2. Các hàm chuẩn để chuyển đổi chuỗi thành ngày
2.1. Hàm DATEVALUE
Cú pháp: =DATEVALUE(text)
textlà chuỗi ngày hợp lệ (theo định dạng ngày hệ thống).- Trả về serial number của ngày.
Ví dụ:
| A | Công thức | Kết quả |
|---|---|---|
| 15/04/2026 | =DATEVALUE(A1) | 44932 |
| 2026-04-15 | =DATEVALUE(A2) | 44932 |
Lưu ý: DATEVALUE không chấp nhận thời gian, chỉ nhận ngày. Nếu chuỗi chứa thời gian, sử dụng VALUE hoặc tách riêng.
2.2. Hàm VALUE
Cú pháp: =VALUE(text)
- Chuyển đổi chuỗi biểu diễn số (cũng áp dụng cho ngày) thành số.
- Hỗ trợ cả ngày và thời gian nếu định dạng hợp lệ.
Ví dụ:
| A | Công thức | Kết quả |
|---|---|---|
| 15/04/2026 08:30 | =VALUE(A1) | 44932.3541667 |
| 2026/04/15 23:45 | =VALUE(A2) | 44932.9895833 |
2.3. Hàm DATE + LEFT, MID, RIGHT
Khi định dạng chuỗi không được Excel nhận diện trực tiếp, chúng ta phải tách các phần ngày, tháng, năm ra và dùng hàm DATE.
Cú pháp DATE(year, month, day). Ví dụ, chuỗi "20230415" (YYYYMMDD):
=DATE(LEFT(A1,4), MID(A1,5,2), RIGHT(A1,2))
2.4. Hàm TEXT để chuẩn hoá định dạng
Nếu chúng ta muốn đổi định dạng chuỗi ngày sang định dạng chuẩn (ví dụ: "15/04/2026"), có thể dùng:
=TEXT(DATEVALUE(A1),"dd/mm/yyyy")
2.5. Hàm SUBSTITUTE và TRIM
Loại bỏ ký tự thừa hoặc thay thế dấu phân cách:
=DATEVALUE(SUBSTITUTE(TRIM(A1),".","/"))
2.6. Kết hợp IFERROR để xử lý lỗi
Khi dữ liệu không đồng nhất, dùng IFERROR để bảo vệ công thức:
=IFERROR(DATEVALUE(A1), IFERROR(DATEVALUE(SUBSTITUTE(A1,"-","/")), "Invalid"))
3. Công cụ chuyển đổi nhanh trong Excel
3.1. Text to Columns (Chia cột)
- Chọn cột chứa chuỗi ngày.
- Vào Data → Text to Columns.
- Chọn Delimited → Next.
- Bỏ chọn tất cả delimiters → Next.
- Ở mục Column data format, chọn Date và chọn DMY, MDY, YMD tùy theo định dạng.
- Nhấn Finish.
Excel sẽ tự động chuyển chuỗi thành ngày (serial number). Phương pháp này nhanh cho một cột lớn, nhưng không linh hoạt khi có nhiều định dạng trong cùng một cột.
3.2. Flash Fill (Điền nhanh)
Từ Excel 2026, Flash Fill có thể nhận diện mẫu:
- Trong ô kế bên, nhập ngày mẫu đã chuyển đổi (ví dụ:
15/04/2026). - Bắt đầu nhập ngày tiếp theo cho ô dưới, Excel sẽ đề xuất tự động điền.
- Nhấn Enter để chấp nhận.
Flash Fill rất hữu ích khi chuỗi ngày có định dạng đồng nhất và không quá phức tạp.
3.3. Power Query (Get & Transform)
Có thể bạn quan tâm: Chuyển Pdf Sang Excel Online Free: Hướng Dẫn Chi Tiết, Công Cụ Tốt Nhất Và Mẹo Tối Ưu Hóa Dữ Liệu
Power Query cung cấp công cụ Data Type → Date và Transform → Using Locale để xử lý định dạng đa ngôn ngữ.
Bước thực hiện:
- Chọn Data → Get Data → From Table/Range.
- Trong Power Query Editor, chọn cột chứa chuỗi.
- Vào Transform → Data Type → Using Locale.
- Chọn Date và Locale phù hợp (ví dụ: Vietnamese (Vietnam) hoặc English (United States)).
- Nhấn OK, sau đó Close & Load.
Power Query sẽ tự động chuyển các chuỗi thành ngày, hỗ trợ các định dạng phức tạp và cho phép lưu lại bước chuyển đổi để tái sử dụng.
4. Công thức nâng cao cho các định dạng phức tạp
4.1. Xử lý định dạng “ddth Month yyyy” (ví dụ: “15th April 2026”)
Chuỗi có hậu tố “th”, “st”, “nd”, “rd” và tên tháng tiếng Anh.
=DATE( RIGHT(A1,4), MATCH(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1), {"January","February","March","April","May","June","July","August","September","October","November","December"},0), VALUE(LEFT(A1,FIND(" ",A1)-1))
)
Giải thích:
– RIGHT(A1,4) lấy năm.
– MID + FIND tách tên tháng, dùng MATCH để chuyển thành số tháng.
– LEFT lấy ngày (bỏ hậu tố).
4.2. Định dạng “yyyy.mm.dd” (ví dụ: “2026.04.15”)
=DATE(LEFT(A1,4), MID(A1,6,2), RIGHT(A1,2))
4.3. Định dạng “dd/mm/yyyy hh:mm:ss”
=VALUE(SUBSTITUTE(A1,"/","-"))
Hoặc tách ngày và thời gian:
=DATE(LEFT(A1,4), MID(A1,6,2), MID(A1,9,2)) + TIME(MID(A1,12,2), MID(A1,15,2), MID(A1,18,2))
4.4. Định dạng hỗn hợp: “15/04/2026; 2026-04-15; Apr 15, 2026”
Sử dụng công thức lồng IFERROR:
=IFERROR(DATEVALUE(A1), IFERROR(DATEVALUE(SUBSTITUTE(A1,"-","/")), IFERROR(DATEVALUE(TEXT(A1,"dd/mm/yyyy")), "Invalid")))
4.5. Xử lý chuỗi có ký tự “Ngày” hoặc “Tháng”
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A1,"Ngày ",""),"Tháng ",""))
Nếu chuỗi là "Ngày 15 Tháng 04 Năm 2026":
=DATE( MID(A1,FIND("Năm ",A1)+4,4), MID(A1,FIND("Tháng ",A1)+6,2), MID(A1,FIND("Ngày ",A1)+5,2)
)
5. Sử dụng VBA để tự động chuyển đổi hàng loạt
Khi dữ liệu có hàng nghìn dòng và định dạng không đồng nhất, viết macro VBA là cách hiệu quả.
5.1. Macro cơ bản
Sub ConvertStringToDate() Dim rng As Range, cell As Range Set rng = Selection ' Chọn vùng dữ liệu cần chuyển For Each cell In rng If Not IsEmpty(cell) Then On Error Resume Next cell.Value = DateValue(cell.Value) If Err.Number <> 0 Then ' Thử các định dạng khác cell.Value = DateValue(Replace(cell.Value, "-", "/")) If Err.Number <> 0 Then cell.Value = "Invalid" End If End If On Error GoTo 0 End If Next cell
End Sub
- Cách dùng: Chọn cột chứa chuỗi, chạy macro. Các giá trị không chuyển đổi sẽ được gán
"Invalid".
5.2. Macro nâng cao với regex (Regular Expressions)
Sub ConvertWithRegex() Dim regEx As Object, matches As Object Dim txt As String, y As Long, m As Long, d As Long Dim rng As Range, cell As Range Set regEx = CreateObject("VBScript.RegExp") regEx.Pattern = "(\d{1,2})\/\.\-(\d{1,2})\/\.\-(\d{2,4})" regEx.IgnoreCase = True regEx.Global = False Set rng = Selection For Each cell In rng txt = Trim(cell.Value) If regEx.Test(txt) Then Set matches = regEx.Execute(txt) d = matches(0).SubMatches(0) m = matches(0).SubMatches(1) y = matches(0).SubMatches(2) If y < 100 Then y = y + 2000 ' xử lý 2 chữ số năm cell.Value = DateSerial(y, m, d) End If Next cell
End Sub
- Ưu điểm: Phát hiện và chuyển đổi mọi chuỗi có dạng
dd/mm/yyyy,dd-mm-yyyy,dd.mm.yymột cách tự động.
6. 10 Ví dụ thực tế và cách giải quyết
| STT | Dữ liệu gốc | Định dạng | Công thức / Công cụ | Kết quả |
|---|---|---|---|---|
| 1 | 2026-04-15 | ISO (yyyy-mm-dd) | =DATEVALUE(A2) | 15/04/2026 |
| 2 | 15/04/2026 | DMY | =DATEVALUE(A3) | 15/04/2026 |
| 3 | 15.04.2026 | DMY với dấu chấm | =DATEVALUE(SUBSTITUTE(A4,".","/")) | 15/04/2026 |
| 4 | 20230415 | YYYYMMDD | =DATE(LEFT(A5,4),MID(A5,5,2),RIGHT(A5,2)) | 15/04/2026 |
| 5 | Apr 15, 2026 | English month | =DATEVALUE(A6) (đặt locale EN) | 15/04/2026 |
| 6 | 15th April 2026 | English + suffix | Công thức nâng cao (phần 4.1) | 15/04/2026 |
| 7 | Ngày 15 Tháng 04 Năm 2026 | Tiếng Việt | =DATE(MID(A7,23,4), MID(A7,15,2), MID(A7,7,2)) | 15/04/2026 |
| 8 | 2026/04/15 08:30 | Date + Time | =VALUE(A8) | 15/04/2026 08:30 |
| 9 | 15/04/23 | 2-digit year | =DATEVALUE(A9) (Excel tự động hiểu) | 15/04/2026 |
| 10 | 15/04/2026; 2026-04-15; Apr 15, 2026 (cột) | Đa định dạng | =IFERROR(DATEVALUE(A10), IFERROR(DATEVALUE(SUBSTITUTE(A10,"-","/")), DATEVALUE(TEXT(A10,"dd/mm/yyyy")))) | 15/04/2026 (đối với mỗi ô) |
7. Mẹo tối ưu và kiểm tra lỗi
7.1. Định dạng ô luôn là Date
Sau khi chuyển đổi, định dạng ô vẫn có thể hiển thị dưới dạng số. Đặt định dạng Short Date hoặc Custom (dd/mm/yyyy) để hiển thị đúng.
7.2. Kiểm tra giá trị không chuyển đổi
Sử dụng công thức:
=IF(ISNUMBER(A1), "OK", "Check")
Hoặc dùng Conditional Formatting để tô màu các ô không phải số (là chuỗi).
7.3. Sử dụng ISERROR hoặc IFNA
=IFERROR(DATEVALUE(A1), "Invalid Date")
7.4. Bảo trì dữ liệu
- Tránh nhập thủ công: Sử dụng Data Validation để giới hạn nhập ngày.
- Chuẩn hoá nguồn dữ liệu: Khi nhận file CSV, xác định định dạng ngày trước khi import.
- Lưu bản sao: Trước khi chạy macro, luôn sao lưu dữ liệu.
8. Kết luận
Việc chuyển đổi chuỗi thành ngày trong Excel không chỉ là một thao tác kỹ thuật mà còn là nền tảng để khai thác dữ liệu hiệu quả. Bằng cách nắm vững:
- Nguyên lý serial number của Excel.
- Các hàm
DATEVALUE,VALUE,DATE, và các hàm xử lý chuỗi. - Công cụ nhanh như Text to Columns, Flash Fill, Power Query.
- Các công thức nâng cao cho định dạng phức tạp.
- Macro VBA để tự động hoá quy trình.
Bạn sẽ giải quyết được hầu hết các trường hợp gặp trong thực tế, từ dữ liệu nhập tay đến file CSV từ hệ thống ERP. Hãy áp dụng những kiến thức này ngay hôm nay để giảm thiểu lỗi dữ liệu, tăng tốc độ xử lý và nâng cao chất lượng báo cáo của bạn.
Tip cuối cùng: Khi làm việc với dữ liệu đa ngôn ngữ, luôn đặt locale phù hợp trong Power Query hoặc trong hàm
DATEVALUE(sử dụngDATEVALUE(TEXT(A1,"dd/mm/yyyy"))), vì Excel sẽ dựa vào cài đặt ngôn ngữ hệ thống để giải mã ngày tháng.
Chúc bạn thành công trong việc biến chuỗi thành ngày và khai thác dữ liệu ngày tháng một cách mạnh mẽ!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.