Ngày tháng là một trong những dữ liệu quan trọng nhất trong các bảng tính Excel. Tuy nhiên, khi nhập liệu, nhập khẩu dữ liệu từ nguồn khác hoặc chuyển đổi vùng địa phương, chúng ta thường gặp phải các lỗi định dạng ngày tháng gây nhầm lẫn và làm giảm tính chính xác của báo cáo. Bài viết này sẽ hướng dẫn bạn cách phát hiện, khắc phục và ngăn ngừa các lỗi định dạng ngày tháng trong Excel, bao gồm các bước chi tiết, công thức, công cụ và mẹo thực hành hiệu quả.
Excel lưu trữ ngày tháng dưới dạng số nguyên (số ngày kể từ ngày 1/1/1900) và hiển thị dựa trên cài đặt vùng địa phương. Nếu cài đặt vùng địa phương của máy tính hoặc Excel không khớp với định dạng dữ liệu nhập vào, Excel sẽ không nhận diện đúng ngày.
Ví dụ: Nếu máy tính đang ở vùng địa phương “Viet Nam” (dd/mm/yyyy) và bạn nhập “12/01/2026”, Excel sẽ hiểu là ngày 12 tháng 01 năm 2026. Nhưng khi nhập “01/12/2026” thì Excel sẽ جودة hiểu là ngày 1 tháng 12.
1.2 Dữ liệu nhập từ file CSV hoặc nguồn bên ngoài
Khi mở file CSV hoặc nhập dữ liệu từ các nguồn bên ngoài (địa chỉ web, cơ sở dữ liệu, API), Excel thường nhận giá trị như chuỗi văn bản, vì vậy ngày tháng không được chuyển đổi tự động. Nếu chuỗi ngày có định dạng không chuẩn, Excel sẽ không chuyển thành kiểu ngày.
1.3 Dấu cách và ký tự ẩn
Các dấu cách (trước, sau, hoặc giữa) và ký tự không nhìn thấy (như dấu tab, ký tự điều khiển) có thể vergessen làm Excel không nhận dạng ngày. Ví dụ: “ 12/01/2026 ”, “12/01/2026\n”.
1.4 Định dạng ngày “nghiện” (mixed date format)
Trong một cột, người dùng có thể nhập ngày theo hai định dạng khác nhau (dd/mm/yyyy và mm/dd/yyyy) trong cùng một cột. Khi cố gắng tính toán, Excel sẽ trả về lỗi hoặc giá trị sai.
Nhìn vào thanh công cụ “Formula” để xem giá trị thực tế. Nếu giá trị hiển thị là chuỗi, bạn có thể thấy dấu ‘`’ (backtick) ở đầu chuỗi, hoặc ô hiển thị dưới dạng văn bản.
2.2 Đánh dấu ô có lỗi
Sử dụng Conditional Formatting:
Chọn cột ngày.
Chọn Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Nhập công thức: =NOT(ISNUMBER(A1)) (giả sử ô đầu tiên là A1).
Đặt màu nền đỏ hoặc màu chữ khác để dễ nhìn.
2.3 Kiểm tra hàm DATEVALUE
Nếu hàm =DATEVALUE(A1) trả về lỗi #VALUE! hoặc giá trị không hợp lệ, có thể ô A1 chứa chuỗi không chuyển đổi sang ngày.
Nhấn Next, chọn dấu “/” hoặc “-” làm dấu phân cách.
Nhấn Next, chọn Date và chọn thứ tự ngày, tháng, năm (DMY hoặc MDY).
Nhấn Finish.
Excel sẽ tự chuyển chuỗi thành số ngày.
3.4 Định dạng ô (Format Cells)
Chọn cột.
Nhấn chuột phải > Format Cells > Number > Date > chọn kiểu ngày mong muốn (ví dụ: dd/mm/yyyy).
Nếu vẫn chưa chuyển, dùng Text → General → Date.
3.5 Thay đổi cài đặt vùng địa phương của Excel
Truy cập File > Options > Advanced > When calculating this workbook > Use system separators.
Đảm bảo dấu “/” là dấu phân cách ngày/tháng.
Nếu cần, chuyển sang Regional Settings (Windows Control Panel) → Region → Formats → Custom Format.
3.6 Sử dụng Power Query để chuyển đổi
Chọn cột dữ liệu.
Chọn Data > From Table/Range (đảm bảo dữ liệu được chuyển thành bảng).
Trong Power Query, chọn cột, click Transform > Date > Date.
Nếu dữ liệu không chuyển, chọn Transform > Split Column > By Delimiter > “/” hoặc “-”.
Sắp xếp lại thứ tự thành Năm, Tháng, Ngày, rồi click Close & Load.
3.7 Viết macro VBA
Nếu bạn cần chuyển đổi hàng nghìn dòng, macro có thể giúp tự động:
Sub ConvertDateText() Dim rng As Range Dim cell As Range Set rng = Selection For Each cell In rng If IsDate(cell.Value) Then cell.Value = DateValue(cell.Value) cell.NumberFormat = "dd/mm/yyyy" Else ' Nếu chuỗi không nhận diện, thử chuẩn hóa cell.Value = DateSerial(Right(cell.Value, 4), Mid(cell.Value, 4, 2), Left(cell.Value, 2)) cell.NumberFormat = "dd/mm/yyyy" End If Next cell
End Sub
Chạy macro này trên vùng dữ liệu đã chọn sẽ tự chuyển các chuỗi ngày.
4. Ngăn ngừa lỗi định dạng ngày tháng trong tương lai
4.1 Kiểm tra dữ liệu trước khi nhập
Khi nhập dữ liệu từ nguồn ngoài, hãy chuyển thành CSV với định dạng ngày chuẩn (yyyy-mm-dd).
Sử dụng công cụ kiểm tra dữ liệu (Data Validation) trong Excel để giới hạn nhập ngày hợp lệ.
4.2 Dùng Data Validation
Chọn cột.
Data > Data Validation > Settings > Date > between.
Đặt giới hạn ngày (ví dụ: từ 01/01/1900 đến 31/12/2099).
Click OK.
Excel sẽ hiện cảnh báo nếu người dùng nhập ngày không hợp lệ.
4.3 Định dạng cột ngay khi tạo bảng
Khi tạo bảng (Insert > Table), chọn kiểu Date Hijri hay Date ngay.
Khi nhập dữ liệu, Excel sẽ tự chuyển sang kiểu ngày.
4.4 Thực hiện kiểm tra định kỳ
Thiết lập macro hoặc công thức kiểm tra định kỳ (ví dụ: =IF(ISNUMBER(A1), "", "Lỗi ngày")) để phát hiện nhanh các ô sai.
4.5 Đào tạo người dùng
Hướng dẫn người dùng cách nhập ngày đúng định dạng.
Sử dụng công cụ AutoCorrect để tự động thay đổi “01/12/2026” thành “01/12/2026”.
5. Các tình huống đặc biệt và giải pháp
Tình huống
Mô tả
Giải pháp
Ngày tháng trong văn bản
Chuỗi “Ngày 12/01/2026”
Sử dụng MID, LEFT, RIGHT để tách, sau đó DATE
Dữ liệu lặp lại ngày tháng
Thay vì nhập lại, dùng Fill Handle
Chọn ô đầu tiên, kéo chuột xuống
Dữ liệu ngày tháng trong cột bán sắp xếp
Sử dụng Sort
Data > Sort > chọn cột ngày
Dữ liệu ngày tháng trong định dạng Excel nhưng hiển thị sai
Kiểm tra NumberFormat
Đặt Custom = dd/mm/yyyy
Dữ liệu ngày tháng bị lỗi do nhập từ Google Sheets
Export CSV với “yyyy-mm-dd مهم”
Import lại, dùng Text to Columns
6. Công cụ hỗ trợ của Excel
6.1 Advanced Find
Home > Find & Select > Replace.
Tìm govern \d{2}/\d{2}/\d{4} (regex) và thay thế bằng dd/mm/yyyy.
6.2 Power Pivot
Khi làm việc với dữ liệu lớn, Power Pivot có thể xử lý ngày tháng ngăn ngừa lỗi khi tạo pivot table.
6.3 Add-ins
Add-in “Power Query” (đã tích hợp sẵn từ Excel 2026).
Add-in “Kutools” cung cấp công cụ chuyển đổi ngày nhanh.
7. Tóm tắt các bước khắc phục nhanh
Xác định nguồn lỗi: Định dạng vùng địa phương, dữ liệu văn bản, dấu cách, định dạng lẫn nhau.
Chuyển đổi dữ liệu: Sử dụng DATEVALUE, Text to Columns, Power Query, hoặc macro.
Định dạng ô: result dd/mm/yyyy.
Kiểm tra: Data Validation, Conditional Formatting.
Ngăn ngừa: Kiểm tra dữ liệu, đào tạo người dùng, sử dụng bảng và cột định dạng.
8. Kết luận
Lỗi định dạng ngày tháng trong Excel là một vấn đề phổ biến nhưng có thể được khắc phục nhanh chóng nếu bạn biết cách phát hiện và áp dụng các công cụ phù hợp. Bằng cách chuẩn hóa dữ liệu đầu vào, sử dụng công thức chuyển đổi, áp dụng Power Query hoặc macro, và thiết lập các quy tắc kiểm tra dữ liệu, bạn có thể giảm thiểu rủi ro sai sót, nâng cao độ chính xác của báo cáo và tối ưu quy trình làm việc. Hãy thử áp dụng các phương pháp trên vào bảng tính của bạn và cảm nhận sự khác biệt trong tính chính xác và hiệu quả công việc.
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ẻ.
⚠️
LƯU Ý QUAN TRỌNG:Miễn trừ trách nhiệm: Mặc dù chúng tôi luôn nỗ lực để đảm bảo tính chính xác và cập nhật của thông tin, nhưng các quy định về giáo dục và tuyển sinh có thể thay đổi theo thời gian. BigSchool không chịu trách nhiệm về bất kỳ sai sót nào hoặc kết quả phát sinh từ việc sử dụng thông tin trên trang này. Độc giả nên liên hệ trực tiếp với nhà trường hoặc cơ quan chức năng để có thông tin mới nhất. Ngoài ra, BigSchool tuyệt đối không bán sản phẩm, khóa học hay cung cấp dịch vụ được nhắc đến trong bài.
