Cột ngày tháng lẫn định dạng thường là hỗn hợp của ba thứ: ngày thật mà Excel hiểu đúng, ngày thật mà Excel hiểu sai do đảo ngày với tháng, và chuỗi chữ trông như ngày nhưng Excel không coi là ngày. Loại thứ hai nguy hiểm nhất, vì nó trông bình thường, sắp xếp được, tính được, chỉ có điều ngày 5 tháng 3 đã thành ngày 3 tháng 5.
Bài này giúp bạn nhận ra từng loại, sửa bằng công thức và bằng cách nhập lại dữ liệu đúng vùng ngôn ngữ, dùng AI để viết công thức cho các kiểu ngày lạ, rồi kiểm lại trước khi đem cột ngày đi tính tuổi nợ, đếm đơn theo tháng hay ghép với bảng khác. Ngày trong ví dụ đều là ví dụ giả định.
Excel lưu ngày như thế nào và vì sao dễ hiểu sai
Excel không lưu ngày dưới dạng chữ. Mỗi ngày là một con số, đếm từ mốc đầu năm 1900: số 1 là ngày đầu tiên, và mỗi ngày sau cộng thêm một. Thứ bạn thấy trên ô chỉ là cách hiển thị con số đó theo định dạng đã chọn. Nhờ vậy Excel trừ hai ngày ra được số ngày, cộng 30 vào một ngày ra ngày đến hạn.
Khi bạn gõ hoặc dán một chuỗi như "05/03/2025", Excel phải đoán đó là ngày nào. Nó đoán theo thiết lập vùng của máy tính. Máy đặt vùng kiểu Mỹ đọc là tháng 5 ngày 3; máy đặt vùng Việt Nam đọc là ngày 5 tháng 3. Chuỗi "25/03/2025" thì máy kiểu Mỹ không hiểu được vì không có tháng 25, nên giữ nguyên là chữ.
Từ đó sinh ra kiểu lỗi kinh điển: một cột ngày nhập từ tệp CSV trên máy vùng Mỹ, các ngày từ 1 đến 12 bị đảo ngày với tháng thành ngày thật sai, còn các ngày từ 13 trở đi nằm lại dạng chữ. Người nhìn thấy những ô dạng chữ thì tưởng chỉ chúng bị lỗi, sửa xong những ô đó là coi như xong, trong khi một phần lớn cột đã sai mà không báo gì.
Hiểu điều này giúp bạn chọn đúng cách sửa: với ô dạng chữ thì chuyển thành ngày; với ô đã bị đảo thì phải đảo lại, hoặc tốt hơn là quay về dữ liệu gốc và nhập lại cho đúng ngay từ đầu.
Nhận ra ô nào là ngày thật, ô nào là chữ
Nhìn căn lề. Khi ô để căn lề mặc định, số và ngày thật nằm sát lề phải, chữ nằm sát lề trái. Cột ngày mà nửa lệch trái nửa lệch phải là dấu hiệu đầu tiên của hỗn hợp. Cách này không dùng được nếu ai đó đã căn lề cả cột bằng tay.
Dùng ISNUMBER. Ở cột phụ, =ISNUMBER(A2) trả về TRUE nếu ô là ngày thật, FALSE nếu là chữ. Lọc theo cột này để tách hai nhóm.
Đổi định dạng sang số. Chọn cột, đổi định dạng sang General. Ngày thật biến thành số năm chữ số; ô dạng chữ giữ nguyên. Xem xong thì bấm hoàn tác.
Tìm ngày bị đảo. Nếu biết dữ liệu thuộc một khoảng thời gian, ví dụ giả định là đơn hàng tháng 3, hãy lọc các ngày thật có tháng khác 3 bằng hàm MONTH. Ngày 5 tháng 3 bị đảo sẽ hiện thành tháng 5, lộ ra ngay. Với dữ liệu trải nhiều tháng, kiểm bằng các ngày mà ngày lớn hơn 12: chúng không thể bị đảo, nên nếu toàn bộ nhóm đó đang là chữ còn nhóm ngày nhỏ lại là ngày thật, gần như chắc chắn nhóm ngày nhỏ đã bị đảo.
Đối chiếu một vài dòng với nguồn. Mở tệp gốc bằng trình soạn văn bản thuần, hoặc xem chứng từ giấy, so ba đến năm dòng. Đây là cách chắc chắn nhất để biết thứ tự ngày tháng trong nguồn là gì.
Cách sửa tốt nhất: nhập lại dữ liệu đúng vùng
Nếu cột ngày đến từ tệp CSV hay tệp xuất từ phần mềm, đừng vá từng ô. Quay về tệp gốc và nhập lại:
- Mở Excel trống, vào Data, chọn From Text/CSV và chọn tệp gốc thay vì nhấp đúp mở tệp.
- Trong cửa sổ xem trước, chọn Transform Data để mở Power Query.
- Nhấp phải tiêu đề cột ngày, chọn Change Type, rồi Using Locale. Chọn kiểu Date và vùng Vietnamese (Vietnam), hoặc vùng nào có thứ tự ngày trước tháng sau khớp với tệp của bạn.
- Kiểm vài dòng trong khung xem trước, đặc biệt các ngày từ 1 đến 12, rồi bấm Close and Load.
- Với bản Excel không có Power Query, dùng Text to Columns: chọn cột, Data, Text to Columns, đi tới bước 3, chọn Column data format là Date và chọn kiểu DMY. Cách này cũng chuyển được một cột đã dán sẵn dạng chữ.
Cách nhập lại có hai ưu điểm: sửa được cả nhóm ngày đã bị đảo mà công thức không phân biệt nổi, và lặp lại được y hệt mỗi tuần khi có tệp mới. Với Google Sheets, thứ tự ngày tháng khi dán phụ thuộc thiết lập vùng của bảng tính trong File, Settings; đặt vùng Việt Nam trước khi dán dữ liệu.
Công thức cho từng kiểu ngày dạng chữ
Khi không có tệp gốc để nhập lại, chỉ còn cột dạng chữ, dùng công thức dựng ngày bằng hàm DATE(năm, tháng, ngày). Hàm này không phụ thuộc vùng của máy nên cho kết quả ổn định. Bảng dưới giả định chuỗi nằm ở ô A2; công thức dùng dấu phẩy, máy vùng Việt Nam đổi thành chấm phẩy.
| Kiểu chuỗi trong ô | Công thức gợi ý | Ghi chú |
|---|---|---|
| Ngày/tháng/năm đủ hai chữ số, năm bốn số | =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)) | Chỉ đúng khi mọi ô cùng độ dài 10 ký tự |
| Ngày, tháng một hoặc hai chữ số | Excel 365: =LET(p,TEXTSPLIT(A2,"/"),DATE(INDEX(p,3),INDEX(p,2),INDEX(p,1))) | Bản cũ: tách bằng Text to Columns rồi ghép bằng DATE |
| Năm-tháng-ngày theo chuẩn ISO, có thể kèm giờ | =DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2)) | Phần giờ phía sau bị bỏ qua |
| Số giây kể từ đầu năm 1970 (một số hệ thống xuất ra) | =A2/86400+DATE(1970,1,1) | Ra giờ quốc tế; cộng thêm 7/24 để ra giờ Việt Nam |
| Năm chỉ có hai chữ số | Dựng bằng DATE với năm cộng 2000 nếu chắc là thế kỷ này | Excel tự hiểu 00 đến 29 là 2000 đến 2029, 30 đến 99 là 1930 đến 1999 |
Sau khi có cột ngày mới, đặt định dạng hiển thị dd/mm/yyyy để mọi người đọc thống nhất. Với ngày sinh có năm hai chữ số, quy tắc tự hiểu của Excel có thể đẩy người sinh năm 1928 thành năm 2028; luôn kiểm cột năm sinh sau khi chuyển.
Nhờ AI viết công thức cho kiểu ngày lạ
Ngoài các kiểu trên, dữ liệu thật còn những kiểu khó đoán: "ngày 5 tháng 3 năm 2025", "5.3.25", "T3/2025", "05-Mar-2025", hay ô ghi cả ngày lẫn ghi chú "5/3 giao lại". Đây là việc AI làm khá tốt nếu bạn mô tả đủ. Ví dụ câu lệnh: "Cột A trong Excel 365, vùng Việt Nam, chứa ngày dạng chữ theo các kiểu sau (ví dụ giả định): [dán mười chuỗi mẫu]. Thứ tự luôn là ngày trước tháng sau. Viết một công thức trả về ngày thật cho cột B; kiểu nào không nhận ra thì trả về chữ KIỂM TRA. Giải thích từng phần."
Ba điều cần nói rõ trong câu lệnh: phiên bản Excel hoặc Google Sheets, thứ tự ngày tháng trong nguồn, và cách xử lý ô không nhận ra. Thiếu thứ tự ngày tháng, AI có thể mặc định kiểu Mỹ và viết công thức đảo ngược. Thiếu cách xử lý ô lạ, công thức có thể trả về một ngày bịa từ những con số lẫn trong ghi chú.
Ngày tháng đơn hàng hiếm khi là dữ liệu nhạy cảm, nhưng nếu cột ngày nằm cạnh tên khách, số điện thoại, ngày sinh nhân viên, chỉ dán các chuỗi ngày mẫu, không dán cả dòng. Thử công thức trên bộ mẫu gồm đủ kiểu, kể cả các ngày từ 1 đến 12 và ngày cuối tháng 2, trước khi kéo cho cả cột.
Nếu dữ liệu có ngày âm lịch, như ngày giỗ, ngày lễ trong danh sách hội viên, nên để ở cột riêng và ghi rõ là âm lịch. Đừng nhờ chatbot quy đổi hàng loạt từ trí nhớ; dùng bảng lịch đối chiếu đáng tin.
Chặn lỗi ngày tháng ngay từ khâu nhập
Sửa một cột ngày mất cả buổi; chặn lỗi từ đầu chỉ mất vài phút cài đặt. Với bảng nhập tay hằng ngày và các tệp nhận từ nơi khác, áp dụng mấy quy ước sau:
- Kiểm tra dữ liệu kiểu Date: chọn cột ngày, vào Data Validation, chọn Allow là Date và đặt khoảng hợp lý, ví dụ giả định từ đầu năm ngoái đến cuối năm nay. Người nhập gõ chữ hay gõ năm sai sẽ bị chặn ngay.
- Thống nhất một cách hiển thị: đặt định dạng dd/mm/yyyy cho cả cột để ai nhìn cũng đọc cùng một kiểu, kể cả khi máy của họ đặt vùng khác.
- Biểu mẫu trực tuyến dùng ô kiểu ngày: câu hỏi ngày trong biểu mẫu nên là loại chọn ngày, không phải ô trả lời ngắn. Dữ liệu đổ về bảng sẽ là ngày thật.
- Thoả thuận với nơi gửi tệp: nếu nhận tệp định kỳ từ đối tác hoặc phần mềm, đề nghị họ xuất ngày theo dạng năm-tháng-ngày. Dạng này không bị hiểu nhầm ở bất kỳ vùng nào.
- Không gõ ghi chú vào ô ngày: "5/3 giao lại" nên tách thành ô ngày và ô ghi chú riêng. Một ô lẫn chữ là đủ làm hỏng phép tính cả cột.
Với bảng dùng chung nhiều người, ghi quy ước nhập ngày ở dòng đầu hoặc một trang tính hướng dẫn, để người mới vào ca không phải đoán.
Kiểm cột ngày trước khi dùng
Một cột ngày đã sửa cần qua ba phép kiểm. Thứ nhất, ISNUMBER cho toàn bộ cột phải ra TRUE, trừ những ô bạn cố ý để trống hoặc đánh dấu kiểm tra. Đếm bằng COUNT so với COUNTA để biết còn bao nhiêu ô chữ.
Thứ hai, kiểm biên: dùng MIN và MAX xem ngày sớm nhất và muộn nhất. Một bảng đơn hàng của năm nay mà ngày sớm nhất ra năm 1900 nghĩa là có ô số 0 hoặc ô trống bị hiểu là ngày; ngày muộn nhất rơi vào năm sau nghĩa là có ô bị đảo hoặc gõ nhầm năm.
Thứ ba, kiểm phân bố: đếm số dòng theo tháng bằng COUNTIFS với hai mốc đầu tháng và cuối tháng. Ví dụ giả định một cửa hàng chỉ mới bán từ tháng 6 mà bảng lại có đơn ở tháng 2, hoặc một tháng bình thường bỗng có số đơn gấp đôi các tháng khác, đó có thể là dấu vết của ngày bị đảo. Đây chỉ là phép kiểm hợp lý, việc đọc xu hướng doanh số theo tháng thuộc về phân tích, có bài riêng bên AI Phân Tích.
Cuối cùng, giữ cột ngày gốc dạng chữ bên cạnh, ghi vào nhật ký cách đã chuyển. Lần sau nhận tệp từ cùng nguồn, bạn chỉ việc lặp lại đúng các bước, hoặc lưu luôn truy vấn Power Query để làm tự động.
Câu hỏi thường gặp
Vì sao mở tệp CSV thì ngày và tháng bị đảo cho nhau?
Vì Excel đọc chuỗi ngày theo thiết lập vùng của máy. Máy đặt vùng kiểu Mỹ đọc tháng trước ngày sau, nên ngày 5 tháng 3 thành ngày 3 tháng 5, còn các ngày lớn hơn 12 không đọc được và nằm lại dạng chữ.
Làm sao biết ô ngày là ngày thật hay chỉ là chữ?
Dùng hàm ISNUMBER: ngày thật trả về TRUE, chữ trả về FALSE. Hoặc đổi định dạng cột sang General, ngày thật sẽ hiện thành số năm chữ số còn chữ giữ nguyên.
Ngày đã bị đảo thì sửa bằng công thức được không?
Được nếu bạn chắc cả nhóm đó bị đảo, bằng cách dựng lại ngày với DATE lấy tháng làm ngày và ngược lại. Nhưng cách đáng tin hơn là quay về tệp gốc và nhập lại với đúng vùng ngôn ngữ qua Power Query hoặc Text to Columns.
Có nên nhờ chatbot chuyển cả cột ngày giúp không?
Nên nhờ AI viết công thức từ các chuỗi mẫu rồi tự chạy trong tệp, thay vì dán cả cột. Chatbot xử lý danh sách dài dễ bỏ sót dòng hoặc đổi giá trị, và cột ngày thường nằm cạnh dữ liệu cá nhân.
Năm sinh hai chữ số bị Excel hiểu sai thế kỷ thì làm thế nào?
Dựng lại ngày bằng hàm DATE với năm bốn chữ số do bạn tự quyết định, ví dụ cộng 1900 cho các năm lớn hơn năm hiện tại. Sau đó kiểm năm sinh nhỏ nhất và lớn nhất xem có hợp lý không.
Google Sheets có bị lỗi đảo ngày tháng không?
Có, khi vùng của bảng tính khác thứ tự ngày tháng trong dữ liệu. Đặt vùng trong File, Settings trước khi dán hoặc nhập tệp, và kiểm vài ngày từ 1 đến 12 sau khi nhập.