Kiểm Tra Định Dạng Ngày Tháng Trong Excel: Hướng Dẫn Chi Tiết, Mẹo Và Công Thức Nâng Cao

Trong công việc quản lý dữ liệu, lập kế hoạch dự án, hoặc phân tích báo cáo tài chính, định dạng ngày tháng luôn là một trong những yếu tố quan trọng nhất. Một dữ liệu ngày tháng sai định dạng không chỉ khiến cho bảng tính trông lộn xộn mà còn có thể gây ra lỗi tính toán, báo cáo sai lệch, thậm chí là mất tin cậy của khách hàng. Với Excel – công cụ bảng tính mạnh mẽ nhất hiện nay – việc kiểm tra, xác nhận và chuyển đổi định dạng ngày tháng có thể thực hiện một cách nhanh chóng nếu biết các công thức, hàm và thủ thuật phù hợp.

Bài viết này sẽ mang đến cho bạn một bộ công cụ toàn diện: từ cách xác định dữ liệu có thực sự là ngày tháng, đến cách chuyển đổi, chuẩn hoá, kiểm tra tính hợp lệ, và thậm chí mô phỏng các trường hợp “định dạng sai” thường gặp. Chúng ta sẽ đi sâu vào từng bước, tương tác với các ví dụ thực tế, và cung cấp các công thức nâng cao giúp tự động hoá quy trình kiểm tra.

Có thể bạn quan tâm: Không Sắp Xếp Được Thứ Tự Trong Excel

1. Tại sao định dạng ngày tháng lại quan trọng?

1. Tính chính xác trong tính toán – Excel tính toán ngày tháng dựa trên số thứ tự ngày (serial number). Nếu dữ liệu nhập vào dưới dạng chuỗi (text), các phép tính như cộng trừ ngày sẽ sai lệch.
2. Định dạng hiển thị – Dùng định dạng đúng giúp người dùng đọc nhanh và tránh nhầm lẫn (ví dụ: 01/02/2026 có thể là 1 tháng 2 hoặc 2 tháng 1 tùy quốc gia).
3. Tích hợp dữ liệu – Khi merge, pivot, hoặc export ra các hệ thống khác, dữ liệu ngày tháng phải chuẩn để tránh lỗi.
4. Tuân thủ quy định – Trong báo cáo tài chính, ngày tháng phải tuân theo tiêu chuẩn ISO 8601 (yyyy-mm-dd).

Có thể bạn quan tâm: Không Dùng Được Phím Mũi Tên Trong Excel: Tìm Hiểu Nguyên Nhân Và Cách Khắc Phục

2. Những vấn đề thường gặp khi làm việc với ngày tháng

Có thể bạn quan tâm: Không Di Chuyển Được Các Ô Trong Excel: Nguyên Nhân Và Cách Khắc Phục

3. Kiểm tra xem ô có phải là ngày tháng hay không

3.1 Sử dụng hàm ISNUMBER và TYPE

Trong Excel, ngày tháng được lưu trữ dưới dạng số nguyên (serial number). Do đó, một cách nhanh nhất để xác định ô có phải ngày tháng hay không là kiểm tra kiểu dữ liệu là số.

=ISNUMBER(A1)

• Nếu trả về TRUE, ô có thể là ngày tháng (hoặc một số khác).
• Nếu trả về FALSE, ô có thể là chuỗi, lỗi, hoặc dữ liệu khác.

3.2 Kiểm tra kiểu dữ liệu bằng hàm CELL

Hàm CELL("format", A1) trả về mã định dạng của ô:

=CELL("format", A1)

• D hoặc DD → ngày
• M hoặc MM → tháng
• Y hoặc YY → năm
• G → số nguyên

Mặc dù không hoàn toàn chắc chắn, nhưng kết hợp với ISNUMBER giúp lọc dữ liệu.

3.3 Kiểm tra chuỗi ngày tháng bằng hàm DATEVALUE

Hàm DATEVALUE chuyển chuỗi ngày thành serial number. Nếu chuỗi không hợp lệ, hàm trả về lỗi.

=IFERROR(DATEVALUE(A1), "Không phải ngày")

Nếu kết quả là một số, ô chứa chuỗi ngày hợp lệ. Nếu lỗi, không phải ngày.

4. Kiểm tra tính hợp lệ của ngày tháng

4.1 Kiểm tra ngày tồn tại

Khi người dùng nhập ngày sai (ví dụ 31/02/2026), DATEVALUE sẽ trả về lỗi. Bạn có thể dùng IFERROR để kiểm tra:

=IFERROR(DATEVALUE(A1), "Ngày không hợp lệ")

4.2 Kiểm tra phạm vi ngày

Để giới hạn ngày trong khoảng cụ thể (ví dụ từ 01/01/2026 đến 31/12/2026):

=AND(DATEVALUE(A1)>=DATE(2026,1,1), DATEVALUE(A1)<=DATE(2026,12,31))

Nếu trả về TRUE, ngày hợp lệ trong phạm vi; ngược lại, FALSE.

4.3 Kiểm tra định dạng (ngày/ tháng/ năm)

Để xác định định dạng ngày tháng (dd/mm/yyy hay mm/dd/yyy), bạn có thể so sánh kết quả của DAY, MONTH, YEAR.

Ví dụ, nếu nhập 12/01/2026:

• Nếu DAY(A1) = 12, MONTH(A1) = 1 → dd/mm/yyy
• Nếu DAY(A1) = 1, MONTH(A1) = 12 → mm/dd/yyy

Sử dụng hàm TEXT để kiểm tra:

=IF(TEXT(A1,"dd/mm/yyyy")=TEXT(A1,"mm/dd/yyyy"), "Định dạng dd/mm/yyyy", "Định dạng mm/dd/yyyy")

5. Chuẩn hoá định dạng ngày tháng

5.1 Chuyển đổi chuỗi thành ngày

Sử dụng DATEVALUE hoặc DATE kết hợp LEFT, MID, RIGHT để tạo ngày chuẩn.

Ví dụ 1: Chuỗi dd/mm/yyyy

=DATE(RIGHT(A1,4), MID(A1,4,2), LEFT(A1,2))

Ví dụ 2: Chuỗi mm-dd-yyyy

=DATE(RIGHT(A1,4), LEFT(A1,2), MID(A1,4,2))

5.2 Định dạng ngày chuẩn ISO

Sau khi chuyển đổi thành serial number, có thể hiển thị ngày theo chuẩn ISO bằng công thức:

=TEXT(A1, "yyyy-mm-dd")

Hoặc sử dụng hàm TEXT trong IFERROR để tránh lỗi:

=IFERROR(TEXT(DATEVALUE(A1),"yyyy-mm-dd"), "Sai định dạng")

5.3 Định dạng ngày truyền thống

Nếu muốn hiển thị ngày theo định dạng dd/mm/yyyy:

=TEXT(A1, "dd/mm/yyyy")

6. Kiểm tra dữ liệu ngày tháng trong dải ô

6.1 Kiểm tra từng ô và trả về kết quả

Có thể bạn quan tâm: Khắc Phục Lỗi Mở File Excel Rất Chậm: Hướng Dẫn Chi Tiết Cho Người Dùng

=IF(ISNUMBER(A1),"Ngày","Không phải ngày")

6.2 Kiểm tra toàn bộ cột và đưa ra thống kê

Sử dụng COUNTIF để đếm số ô hợp lệ:

=COUNTIF(A:A,">0") // Đếm số ô có giá trị > 0 (số serial)

Hoặc dùng SUMPRODUCT để tính tỉ lệ hợp lệ:

=SUMPRODUCT(--ISNUMBER(A:A))/COUNTA(A:A)

6.3 Kiểm tra ngày trùng lặp

Sử dụng hàm COUNTIF:

=IF(COUNTIF($A$1:$A$100, A1)>1, "Trùng lặp", "Độc nhất")

7. Thực hành: Kiểm tra dữ liệu thực tế

Giả sử bạn có bảng dữ liệu trong cột A (từ A2 đến A1000) chứa các giá trị ngày tháng tương tự:

Bạn có thể dùng công thức kiểm tra hợp lệ như sau (ngồi trong ô B2 và sao chép xuống):

=IFERROR( IF( AND( ISNUMBER(DATEVALUE(A2)), DATEVALUE(A2)>=DATE(2026,1,1), DATEVALUE(A2)<=DATE(2026,12,31) ), "Hợp lệ", "Không hợp lệ" ), "Không phải ngày"
)

Kết quả sẽ hiển thị Hợp lệ cho các ô hợp lệ, Không hợp lệ cho ngày sai, và Không phải ngày cho chuỗi không phải ngày.

8. Kiểm tra ngày theo chuẩn ISO 8601

Nếu bạn làm việc với dữ liệu ISO (ví dụ 2026-02-29T00:00:00Z), cần tách phần ngày:

=IFERROR( DATEVALUE(LEFT(A2,10)), "Sai định dạng"
)

Sau khi có serial number, bạn có thể kiểm tra tính hợp lệ:

=IF( AND( DATEVALUE(LEFT(A2,10))>=DATE(2026,1,1), DATEVALUE(LEFT(A2,10))<=DATE(2026,12,31) ), "Hợp lệ", "Không hợp lệ"
)

9. Kiểm tra thời gian trong ngày

Nếu dữ liệu chứa thời gian (ví dụ 2026-02-01 14:30:00), bạn có thể tách phần thời gian:

=MOD(A2,1) // Lấy phần thập phân của serial number => thời gian

Kiểm tra thời gian hợp lệ:

=IF(AND(MOD(A2,1)>=0, MOD(A2,1)<=1), "Thời gian hợp lệ", "Sai thời gian")

10. Tự động chuyển đổi định dạng khi nhập

Để giảm thiểu lỗi nhập dữ liệu, bạn có thể tạo một macro VBA đơn giản:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then On Error Resume Next Target.NumberFormat = "yyyy-mm-dd" Target.Value = DateValue(Target.Value) On Error GoTo 0 End If
End Sub

Macro này sẽ tự động chuyển đổi bất kỳ dữ liệu nhập vào cột A thành ngày theo chuẩn ISO ngay khi người dùng gõ.

11. Kiểm tra dữ liệu ngày tháng trong CSV

Khi nhập dữ liệu từ file CSV, Excel có thể không nhận dạng đúng định dạng ngày. Bạn có thể:

1. Sử dụng Text to Columns:
2. Chọn cột chứa ngày.
3. Chọn Data > Text to Columns.
4. Chọn Delimited > Next > Next.
5. Trong Column data format, chọn Date và chọn định dạng phù hợp (DMY, MDY, …).

6. Hoàn thành.

7. Sử dụng công thức:

8. Giả sử dữ liệu ngày ở dạng chuỗi trong cột A:
   excel =DATEVALUE(A2)

9. Kiểm tra lỗi:
   excel =IFERROR(DATEVALUE(A2), "Sai định dạng")

12. Bảo vệ dữ liệu ngày tháng

12.1 Sử dụng dữ liệu validation

1. Chọn cột dữ liệu ngày.
2. Data > Data Validation.
3. Trong Allow, chọn Date.
4. Thiết lập Data > between, nhập 01/01/2026 và 31/12/2026.
5. Đặt thông báo lỗi tùy ý.

12.2 Định dạng Conditional Formatting

• Chọn cột dữ liệu.
• Home > Conditional Formatting > New Rule.
• Chọn Use a formula to determine which cells to format.
• Nhập công thức:
  excel =NOT(ISNUMBER(A2))
• Đặt màu đỏ để làm nổi bật ô không hợp lệ.

13. Mẹo nâng cao khi làm việc với ngày tháng

14. Tổng kết

• Xác định: Sử dụng ISNUMBER, DATEVALUE, CELL để xác định ô là ngày tháng.
• Kiểm tra hợp lệ: Dùng IFERROR, AND, COUNTIF, DATEDIF.
• Chuẩn hoá: TEXT, DATEVALUE, DATE, TEXTJOIN.
• Bảo vệ dữ liệu: Data Validation, Conditional Formatting.
• Tự động hoá: Macro VBA, công thức động.

Bằng cách áp dụng các công thức và thủ thuật trên, bạn sẽ giảm thiểu lỗi nhập dữ liệu, tăng tính chính xác của báo cáo, và tiết kiệm thời gian khi làm việc với ngày tháng trong Excel. Hãy thử áp dụng ngay vào bảng tính hiện tại của bạn để trải nghiệm sự khác biệt!

Tiến sĩ Lê Thống Nhất

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ẻ.