Thời gian đọc ước tính: 7 phút
Những điểm chính
- Hiểu bản chất cách Excel lưu trữ dữ liệu thời gian dưới dạng số sê-ri (serial numbers).
- Thành thạo 3 phương pháp chuyển đổi: Format Cells, Ribbon và hàm TEXT.
- Giải quyết triệt để các lỗi hiển thị ngày tháng phổ biến như
#####hoặc định dạng văn bản. - Tùy chỉnh hiển thị ngày giờ theo chuẩn Việt Nam (dd/mm/yyyy) thay vì mặc định hệ thống.
Bạn mở một tệp báo cáo quan trọng và thay vì nhìn thấy “15/01/2024”, tất cả những gì hiển thị ở cột Ngày tháng là những dãy số vô nghĩa như “45306”. Đừng lo lắng, dữ liệu của bạn không bị hỏng. Đây thực chất là cách Excel “giao tiếp” và lưu trữ thông tin thời gian ở tầng xử lý dữ liệu (back-end).
Excel lưu trữ ngày tháng dưới dạng các con số liên tiếp để phục vụ cho việc tính toán cộng trừ thời gian. Tuy nhiên, việc hiển thị sai định dạng gây khó khăn cho người đọc báo cáo. Bài viết này sẽ hướng dẫn bạn quy trình chuẩn xác để chuyển đổi các dãy số này trở lại định dạng ngày tháng thân thiện, từ các thao tác cơ bản trên thanh Ribbon đến các hàm xử lý dữ liệu nâng cao.
1. Cơ Chế Lưu Trữ “Serial Number” Trong Excel
Trước khi thực hiện bất kỳ thao tác kỹ thuật nào, chúng ta cần hiểu rõ bản chất của dữ liệu. Excel cho Windows mặc định sử dụng hệ thống ngày 1900. Điều này có nghĩa là:
- Ngày 01/01/1900 được quy ước là số 1.
- Ngày 02/01/1900 được lưu là số 2.
- Các ngày sau đó cứ thế tăng dần lên.
Ví dụ: Số 43840 thực chất là ngày 10/01/2020 (tức là 43.840 ngày kể từ mốc 01/01/1900).
Ở hình minh họa dưới đây, cả hai cột A và B đều chứa cùng một giá trị số. Tuy nhiên, cột A hiển thị dạng số nguyên, trong khi cột B hiển thị định dạng ngày tháng tương ứng.
So sánh giá trị số và định dạng ngày tháng trong Excel cột A và B
Hiện tượng bạn thấy số thay vì ngày thường xảy ra khi tải dữ liệu từ các hệ thống ERP, CRM hoặc cơ sở dữ liệu web. Lúc này, định dạng ô (Cell Format) đang được đặt là “General” hoặc “Number” thay vì “Date”.
Lưu ý: Excel trên máy tính Mac có thể sử dụng hệ thống ngày 1904 (mốc bắt đầu là 01/01/1904). Sự khác biệt này có thể gây lệch 4 năm nếu bạn sao chép dữ liệu thô giữa hai hệ điều hành mà không kiểm soát kỹ.
2. Phương Pháp 1: Sử Dụng Công Cụ Number Formatting Trên Ribbon
Đây là cách nhanh nhất để xử lý các dãy số hiển thị sai định dạng. Bạn có thể tìm thấy các tùy chọn này ngay trên thẻ “Home” của thanh công cụ.
Giả sử bạn có một tập dữ liệu tại cột A chứa các dãy số cần chuyển đổi.
Tập dữ liệu mẫu chứa các dãy số sê-ri cần chuyển đổi thành ngày
Quy trình thực hiện:
- Chọn vùng dữ liệu: Bôi đen toàn bộ các ô chứa dãy số bạn muốn chuyển đổi.
Thao tác chọn vùng dữ liệu số cần chuyển đổi - Truy cập thẻ Home: Tại thanh menu chính, nhấn vào thẻ Home.
Vị trí thẻ Home trên thanh công cụ Excel - Mở menu định dạng: Trong nhóm công cụ “Number”, nhấp vào mũi tên xổ xuống tại ô đang hiển thị chữ “General” hoặc “Number”.
Menu thả xuống trong nhóm Number để chọn định dạng - Chọn kiểu hiển thị: Chọn Short Date (Ngày ngắn gọn) hoặc Long Date (Ngày đầy đủ) tùy theo nhu cầu báo cáo của bạn.
Tùy chọn Long Date trong danh sách định dạng số
Kết quả sau khi thực hiện:
Dữ liệu số đã được chuyển thành định dạng ngày tháng chuẩn
Lưu ý quan trọng:
Cách này phụ thuộc hoàn toàn vào thiết lập vùng (Region Settings) của máy tính.
- Nếu máy tính bạn cài đặt theo chuẩn Mỹ (US), Short Date sẽ là MM/DD/YYYY (Tháng trước, Ngày sau).
- Nếu cài đặt theo chuẩn Việt Nam hoặc Anh (UK), định dạng sẽ là DD/MM/YYYY (Ngày trước, Tháng sau).
Phương pháp này tuy nhanh nhưng có hạn chế là thiếu linh hoạt. Bạn không thể tùy chỉnh hiển thị (ví dụ: chỉ hiện tháng/năm) hoặc hiển thị kèm giờ phút một cách chi tiết. Để làm được điều đó, hãy chuyển sang Phương pháp 2.
Bạn có biết? Nếu kết quả hiển thị là các dấu thăng #####, điều này chỉ đơn giản là độ rộng cột không đủ để hiển thị hết nội dung. Bạn chỉ cần click đúp vào biên giới hạn của tiêu đề cột để tự động giãn dòng (AutoFit).
3. Phương Pháp 2: Tùy Biến Nâng Cao Với Hộp Thoại Format Cells
Khi bạn cần báo cáo chuyên nghiệp hơn, ví dụ như hiển thị theo chuẩn Việt Nam “01/01/2024” hoặc kèm theo giờ “01/01/2024 14:30”, hộp thoại Format Cells là công cụ đắc lực nhất.
Giả sử bạn muốn định dạng dãy số thành kiểu 01.01.2020.
Quy trình thực hiện:
- Chọn vùng dữ liệu chứa số cần chuyển đổi.
- Nhấn tổ hợp phím tắt
Ctrl + 1hoặc click vào mũi tên nhỏ ở góc dưới bên phải nhóm Number trên thẻ Home.
Biểu tượng mũi tên mở rộng nhóm Number trên thanh Ribbon - Trong hộp thoại “Format Cells”, đảm bảo bạn đang ở thẻ Number.
Giao diện hộp thoại Format Cells với thẻ Number được chọn - Tại danh sách “Category” bên trái, chọn Date.
Chọn mục Date trong danh sách Category bên trái - Tại vùng “Type” bên phải, chọn định dạng mong muốn và nhấn OK.
Lựa chọn kiểu hiển thị ngày tháng cụ thể trong mục Type
Kết quả hiển thị sẽ chính xác theo mẫu bạn đã chọn:
Kết quả cuối cùng với định dạng ngày tháng tùy chỉnh
Tạo Định Dạng Riêng (Custom Format)
Nếu các mẫu có sẵn không đáp ứng nhu cầu, bạn có thể tự viết mã định dạng.
- Trong hộp thoại Format Cells, chọn Custom ở cột Category.
Chọn mục Custom để tự tạo định dạng ngày tháng - Tại ô Type, nhập mã định dạng. Ví dụ nhập:
dd/mm/yyyy
Nhập mã dd/mm/yyyy vào ô Type để định dạng theo chuẩn Việt Nam - Nhấn OK.
Mọi con số sẽ lập tức tuân theo quy tắc hiển thị này.
Dữ liệu hiển thị đúng theo mã định dạng tùy chỉnh đã nhập
Bảng Mã Ký Hiệu Thời Gian Trong Excel
Để làm chủ việc tùy biến, bạn cần hiểu ý nghĩa các ký tự sau:
| Mã Định Dạng | Ý Nghĩa Hiển Thị | Ví dụ (với ngày 05/01/2024) |
|---|---|---|
| d | Ngày (1-31), không có số 0 ở trước | 5 |
| dd | Ngày (01-31), luôn có 2 chữ số | 05 |
| ddd | Thứ trong tuần (Viết tắt tiếng Anh) | Fri |
| dddd | Thứ trong tuần (Đầy đủ tiếng Anh) | Friday |
| m | Tháng (1-12) | 1 |
| mm | Tháng (01-12), luôn có 2 chữ số | 01 |
| mmm | Tên tháng viết tắt | Jan |
| mmmm | Tên tháng đầy đủ | January |
| yy | 2 số cuối của năm | 24 |
| yyyy | Năm đầy đủ 4 số | 2024 |
Bạn có thể kết hợp ngày và giờ bằng cách dùng khoảng trắng. Ví dụ: dd/mm/yyyy hh:mm AM/PM.
4. Phương Pháp 3: Sử Dụng Hàm TEXT (Xử Lý Dữ Liệu Dạng Văn Bản)
Hai phương pháp trên chỉ thay đổi “lớp vỏ” hiển thị, giá trị gốc của ô vẫn là số. Tuy nhiên, có những trường hợp bạn cần chuyển đổi hẳn giá trị đó thành văn bản (Text) để nối chuỗi hoặc khi dữ liệu gốc bị dính ký tự lạ (như dấu nháy đơn ' ở đầu).
Ví dụ: Dữ liệu có dấu nháy đơn '43840, Excel sẽ hiểu đây là văn bản chứ không phải số, khiến bạn không thể dùng Format Cells.
Số bị định dạng text do có dấu nháy đơn ở đầu
Trong trường hợp này, hãy sử dụng hàm TEXT.
Cú pháp:=TEXT(Giá_trị, "Mã_định_dạng")
Giả sử dãy số nằm ở ô A2, bạn muốn chuyển thành ngày tháng chuẩn Việt Nam, công thức sẽ là:
=TEXT(A2, "dd/mm/yyyy")
Kết quả sử dụng hàm TEXT để chuyển đổi số thành ngày
Nếu bạn muốn hiển thị cả giờ phút:
=TEXT(A2, "dd/mm/yyyy hh:mm AM/PM")
Kết quả hàm TEXT hiển thị cả ngày và giờ chi tiết
Ứng dụng thực tế của hàm TEXT:
Hàm này cực kỳ hữu ích khi bạn muốn lồng ngày tháng vào một câu thông báo.
Ví dụ: ="Hạn thanh toán là ngày " & TEXT(A2, "dd/mm/yyyy")
Kết quả sẽ là: Hạn thanh toán là ngày 10/01/2020.
Nếu không dùng hàm TEXT mà chỉ dùng & A2, kết quả sẽ bị lỗi thành: Hạn thanh toán là ngày 43840.
Sau khi dùng hàm xong, nếu bạn muốn loại bỏ công thức và chỉ giữ lại giá trị ngày tháng tĩnh, hãy sử dụng tính năng Paste Special (Dán giá trị) để chuyển đổi kết quả.
Câu Hỏi Thường Gặp (FAQ)
Tại sao tôi nhập ngày tháng nhưng Excel lại tự động chuyển thành số?
Excel được lập trình để ưu tiên tính toán. Nếu ô đó đang có định dạng là “General” hoặc “Number”, Excel sẽ hiển thị giá trị thực (serial number) của ngày đó. Bạn chỉ cần đổi định dạng ô về “Date”.
Làm thế nào để sửa lỗi dãy số ###### trong ô ngày tháng?
Lỗi này xuất hiện khi cột quá hẹp để hiển thị toàn bộ ngày tháng. Hãy di chuột vào vạch ngăn cách giữa các tiêu đề cột (ví dụ giữa cột A và B) và click đúp chuột để cột tự động mở rộng.
Tôi có thể chuyển đổi số âm thành ngày tháng không?
Trong hệ thống ngày 1900 mặc định của Windows, Excel không hỗ trợ ngày tháng cho số âm (các ngày trước 01/01/1900). Nếu bạn nhập số âm, Excel sẽ báo lỗi ####################.
Sự khác nhau giữa hàm DATE và hàm TEXT là gì?
Hàm DATE trả về một giá trị số sê-ri (serial number) thực thụ để tính toán tiếp. Hàm TEXT trả về một chuỗi văn bản (String) dùng để hiển thị hoặc nối chuỗi, không dùng để cộng trừ ngày tháng trực tiếp được.
Nguồn tham khảo
- Microsoft Support: Format a date the way you want – Hướng dẫn chính thức từ Microsoft về định dạng ngày tháng.
- Excel Jet: Excel Date Formats – Chi tiết về các mã tùy chỉnh (Custom Format) trong Excel.
- GCF Global: Excel: Working with Dates – Các bài học cơ bản về xử lý dữ liệu thời gian.
Việc hiểu rõ cách Excel xử lý ngày tháng dưới dạng số sê-ri sẽ giúp bạn làm chủ dữ liệu và tránh được những sai sót không đáng có trong quá trình làm báo cáo. Hãy áp dụng linh hoạt giữa Format Cells và hàm TEXT tùy theo tình huống cụ thể của bạn.
