Thời gian đọc ước tính: 12 phút
Những điểm chính
- Cơ chế lưu trữ: Excel lưu trữ ngày tháng dưới dạng số serial (bắt đầu từ số 1 tương ứng với ngày 01/01/1900).
- Công cụ định dạng: Sử dụng General Format hoặc hộp thoại Format Cells (Ctrl + 1) để hiển thị giá trị số thực của ngày tháng.
- Xử lý dữ liệu văn bản: Hàm DATEVALUE giúp chuyển đổi chuỗi văn bản dạng ngày thành số serial hợp lệ.
- Tùy chỉnh nâng cao: Tạo chuỗi số 8 chữ số (như 31122022) bằng tính năng Custom Format.
- Xử lý lỗi: Cách khắc phục khi Excel không nhận diện đúng định dạng ngày do khác biệt vùng miền (Regional Settings).
Trong quá trình làm việc với dữ liệu tài chính hoặc kế toán trên Excel, bạn sẽ thường xuyên bắt gặp các ô hiển thị ngày tháng như “31/12/2022”. Tuy nhiên, về bản chất kỹ thuật, Excel không lưu trữ các ký tự này dưới dạng văn bản hay ngày tháng như mắt thường nhìn thấy. Hệ thống bảng tính này lưu trữ mọi giá trị thời gian dưới dạng một chuỗi số liên tiếp (Serial Number).
Ví dụ, ngày 31 tháng 12 năm 2022 thực chất là con số 44926.
Việc hiểu và chuyển đổi giữa định dạng ngày và số serial là kỹ năng nền tảng giúp bạn thực hiện các phép tính phức tạp, xử lý dữ liệu thô từ phần mềm khác hoặc sửa lỗi định dạng. Bài viết này sẽ hướng dẫn bạn chi tiết từng bước để thực hiện việc này, từ các thao tác cơ bản trên thanh Ribbon đến các hàm xử lý dữ liệu văn bản nâng cao.
Cơ Chế Hoạt Động Của Ngày Tháng Trong Excel
Trước khi đi vào các phương pháp chuyển đổi, bạn cần hiểu rõ cách Excel “tư duy” về thời gian. Theo mặc định trên hệ điều hành Windows, Excel sử dụng hệ thống ngày 1900 (1900 Date System).
Điều này có nghĩa là ngày bắt đầu được tính là 01/01/1900, tương ứng với số serial là 1. Mỗi ngày trôi qua sẽ cộng thêm 1 đơn vị vào số này. Do đó, ngày 02/01/1900 sẽ là 2, và cứ thế tiếp tục cho đến hiện tại. Đây là lý do tại sao khi bạn xóa định dạng của một ô chứa ngày, nó sẽ biến thành một con số lạ lẫm.
Dưới đây là các phương pháp cụ thể để bạn kiểm soát việc hiển thị này.
Phương Pháp 1: Sử Dụng Menu Format Trên Thanh Ribbon (Cách Nhanh Nhất)
Đây là cách nhanh và đơn giản nhất để chuyển đổi một vùng dữ liệu ngày tháng chuẩn sang dạng số serial. Phương pháp này phù hợp khi bạn cần kiểm tra nhanh giá trị thực của các ô dữ liệu.
Giả sử bạn có một danh sách ngày tháng trong cột A và muốn xem giá trị số của chúng.
Dữ liệu ngày tháng cần chuyển đổi trong cột A Excel
Quy trình thực hiện:
- Chọn vùng dữ liệu: Bôi đen tất cả các ô chứa ngày tháng trong cột A mà bạn muốn chuyển đổi.
- Truy cập tab Home: Trên thanh công cụ phía trên cùng, hãy nhấn vào tab Home.
Chọn tab Home trên thanh công cụ Excel
- Mở menu định dạng: Tại nhóm công cụ Number, bạn sẽ thấy một danh sách thả xuống (thường hiển thị chữ “Date” hoặc “Custom”).
- Chọn General: Nhấn vào danh sách và chọn tùy chọn General.
Chọn tùy chọn General trong danh sách định dạng thả xuống
Ngay lập tức, các giá trị ngày tháng sẽ được chuyển đổi thành các con số nguyên (số serial).
Kết quả sau khi chuyển đổi ngày tháng thành số serial
Lưu ý quan trọng:
Định dạng General (Chung) là trạng thái nguyên bản của dữ liệu, không áp dụng bất kỳ quy tắc hiển thị đặc biệt nào. Do đó, Excel sẽ hiển thị chính xác con số đang được lưu trữ trong bộ nhớ.
Tuy nhiên, phương pháp này chỉ hoạt động nếu dữ liệu ban đầu đã là ngày tháng hợp lệ (Excel nhận diện được). Nếu bạn nhập “31.12.2022” nhưng máy tính của bạn đang thiết lập định dạng “Tháng/Ngày/Năm”, Excel có thể coi đó là văn bản (Text) và sẽ không chuyển đổi được.
Bảng: Các định dạng ngày Excel thường nhận diện tự động
| Định dạng nhập liệu | Ví dụ | Ghi chú |
|---|---|---|
| Gạch ngang | 31-12-2022 | Phổ biến nhất |
| Dấu gạch chéo | 31/12/2022 | Phụ thuộc Regional Settings |
| Tên tháng viết tắt | 31 Dec 2022 | Tự động nhận diện |
| Dấu chấm | 31.12.2022 | Thường dùng ở châu Âu/Việt Nam |
Phương Pháp 2: Sử Dụng Hộp Thoại Format Cells (Kiểm Soát Chi Tiết)
Nếu bạn cần nhiều tùy chọn hơn, chẳng hạn như thêm dấu phân cách hàng nghìn hoặc hiển thị số thập phân (đối với các mốc thời gian có chứa giờ/phút), hộp thoại Format Cells là công cụ tối ưu.
So với cách dùng Ribbon, phương pháp này giúp bạn định dạng số liệu chuyên nghiệp hơn, tránh việc hiển thị các con số thô khó đọc.
Quy trình thực hiện:
- Chọn dữ liệu: Bôi đen các ô ngày tháng trong cột A.
- Mở Format Cells: Nhấn tổ hợp phím tắt
Ctrl + 1(hoặc chuột phải chọn Format Cells).- Ngoài ra, bạn có thể nhấn vào biểu tượng mũi tên nhỏ ở góc dưới bên phải nhóm Number trên tab Home.
Nhấn vào biểu tượng mở rộng hộp thoại Number
- Chọn thẻ Number: Đảm bảo bạn đang ở tab Number trong hộp thoại vừa hiện ra.
Đảm bảo thẻ Number được chọn trong hộp thoại
- Thiết lập Category: Trong cột bên trái (Category), chọn mục Number.
Chọn danh mục Number trong danh sách Category
- Điều chỉnh số thập phân: Tại ô Decimal places, bạn hãy nhập số
0. Vì số serial của ngày là số nguyên, chúng ta không cần phần thập phân (trừ khi ngày đó có chứa thông tin giờ giấc cụ thể).
Thiết lập số chữ số thập phân là 0
- Dấu phân cách hàng nghìn (Tùy chọn): Tích vào ô Use 1000 Separator (,) nếu bạn muốn số dễ đọc hơn (ví dụ: 44,926 thay vì 44926).
Tùy chọn sử dụng dấu phân cách hàng nghìn
- Hoàn tất: Nhấn OK.
Kết quả trả về sẽ là các số serial được định dạng gọn gàng theo ý muốn của bạn.
Kết quả hiển thị số serial sau khi dùng Format Cells
Phương pháp này cực kỳ hữu ích khi bạn làm việc với các bảng tính lớn và cần đồng bộ hóa định dạng hiển thị cho báo cáo. Nó cũng cung cấp tùy chọn hiển thị số âm (mặc dù ngày tháng trong Excel hiếm khi âm, trừ khi do lỗi tính toán).
Phương Pháp 3: Xử Lý Ngày Tháng Dạng Văn Bản (Hàm DATEVALUE)
Đây là trường hợp gây đau đầu nhất cho người dùng Excel: Bạn nhìn thấy ngày tháng, nhưng Excel lại coi đó là văn bản (Text). Điều này thường xảy ra khi:
- Dữ liệu được xuất từ phần mềm ERP, web, hoặc cơ sở dữ liệu nội bộ.
- Dữ liệu có dấu nháy đơn
'ở phía trước (ví dụ:'31/12/2022). - Định dạng ngày của dữ liệu nguồn không khớp với cài đặt máy tính (ví dụ: máy tính cài đặt ngày/tháng/năm nhưng dữ liệu lại là tháng/ngày/năm).
Trong tình huống này, việc đổi định dạng sang Number hay General như hai cách trên sẽ không có tác dụng. Ô dữ liệu vẫn sẽ hiển thị y nguyên chuỗi văn bản đó.
Giải pháp ở đây là sử dụng hàm DATEVALUE. Hàm này được thiết kế chuyên biệt để “dịch” một chuỗi văn bản trông giống ngày tháng thành số serial chuẩn của Excel.
Cú pháp hàm:
=DATEVALUE(date_text)Giả sử bạn có một danh sách ngày dạng text trong cột A như hình dưới:
Dữ liệu ngày tháng đang ở định dạng văn bản Text
Quy trình thực hiện:
- Tại một ô trống bên cạnh (ví dụ B2), nhập công thức:
=DATEVALUE(A2) - Nhấn Enter.
- Sao chép công thức xuống các ô còn lại.
Sử dụng hàm DATEVALUE để chuyển đổi văn bản thành số
Hàm sẽ trả về số serial (ví dụ: 44926). Sau khi có được số này, bạn có thể định dạng lại nó thành kiểu ngày tháng (Date) nếu muốn, hoặc giữ nguyên dạng số để tính toán.
Lưu ý quan trọng về lỗi #VALUE!:
Hàm DATEVALUE chỉ hoạt động nếu chuỗi văn bản đó “có vẻ” hợp lý với cài đặt ngày giờ của hệ thống máy tính (System Date Format).
- Ví dụ: Nếu máy tính bạn đang cài đặt kiểu dd/mm/yyyy (Ngày/Tháng/Năm), và chuỗi văn bản là “12/31/2022” (Tháng 12, ngày 31), hàm có thể trả về lỗi #VALUE! vì nó không hiểu “tháng 31” là gì.
- Để khắc phục, bạn cần kiểm tra lại Regional Settings trong Control Panel của Windows hoặc sử dụng các hàm xử lý chuỗi như
LEFT,MID,RIGHTđể sắp xếp lại trật tự ngày tháng trước khi đưa vào DATEVALUE.
Phương Pháp 4: Chuyển Đổi Ngày Thành Số 8 Chữ Số (Định dạng MMDDYYYY hoặc DDMMYYYY)
Trong một số hệ thống ngân hàng hoặc xuất nhập khẩu dữ liệu cũ, yêu cầu đặt ra không phải là số serial của Excel (như 44926), mà là một chuỗi số đại diện cho ngày tháng, ví dụ: 31122022.
Đây thực chất là một kỹ thuật “đánh lừa thị giác” bằng Custom Formatting. Giá trị thực của ô vẫn là số serial của ngày, nhưng cách hiển thị sẽ là chuỗi số liền mạch.
Giả sử bạn có dữ liệu ngày trong cột E:
Dữ liệu ngày tháng cần định dạng thành chuỗi 8 số
Quy trình thực hiện:
- Chọn vùng dữ liệu: Bôi đen các ô ngày tháng.
- Mở Format Cells: Nhấn
Ctrl + 1. - Chọn Custom: Tại thẻ Number, chọn mục Custom ở cột Category.
Chọn tùy chọn Custom trong hộp thoại Format Cells
- Thiết lập mã định dạng: Tại ô Type, bạn hãy nhập mã định dạng mong muốn:
- Nhập
ddmmyyyynếu muốn hiển thị ngày trước tháng (Ví dụ: 31122022). - Nhập
mmddyyyynếu muốn hiển thị tháng trước ngày (Ví dụ: 12312022). - Nhập
yyyymmddnếu muốn hiển thị năm trước (Ví dụ: 20221231).
- Nhập
Nhập mã mmddyyyy vào ô Type
- Hoàn tất: Nhấn OK.
Kết quả là các ngày tháng đã được hiển thị dưới dạng chuỗi số liền mạch mà không có dấu gạch chéo hay gạch ngang.
Ngày tháng hiển thị dưới dạng chuỗi số 8 chữ số
Lợi ích: Cách này giữ nguyên bản chất dữ liệu là ngày tháng (Serial Number), cho phép bạn vẫn thực hiện được các phép cộng trừ ngày tháng bình thường, trong khi vẫn đáp ứng được yêu cầu hiển thị của báo cáo.
Câu Hỏi Thường Gặp (FAQ)
Tại sao ngày tháng của tôi lại biến thành số như 44xxx?
Đây không phải là lỗi. Excel lưu trữ ngày tháng dưới dạng số đếm (số serial) bắt đầu từ ngày 01/01/1900. Khi ô bị mất định dạng “Date” và chuyển về “General”, bạn sẽ thấy con số cốt lõi này. Bạn chỉ cần định dạng lại ô thành “Short Date” là sẽ hiển thị lại bình thường.
Làm thế nào để biết một ô là ngày tháng thật hay văn bản (Text)?
Bạn có thể dùng hàm =ISNUMBER(A1). Nếu kết quả là TRUE, đó là ngày tháng chuẩn (số). Nếu là FALSE, đó là văn bản. Ngoài ra, theo mặc định, số sẽ được căn lề phải, còn văn bản sẽ căn lề trái trong ô.
Hệ thống ngày 1904 là gì?
Excel có hai hệ thống ngày: 1900 (mặc định cho Windows) và 1904 (mặc định cũ cho Mac). Hệ thống 1904 bắt đầu tính từ 01/01/1904. Sự khác biệt này đôi khi gây ra lỗi lệch 4 năm khi sao chép dữ liệu giữa các file Excel khác hệ điều hành. Bạn có thể chỉnh lại trong File > Options > Advanced.
Tôi dùng DATEVALUE nhưng bị lỗi #VALUE!, phải làm sao?
Lỗi này thường do định dạng ngày trong văn bản không khớp với cài đặt vùng (Region) của máy tính. Ví dụ: Máy tính hiểu dd/mm/yyyy nhưng văn bản lại là mm/dd/yyyy. Hãy thử dùng công cụ Text to Columns để chia nhỏ và sắp xếp lại, hoặc kiểm tra cài đặt trong Control Panel.
Nguồn Tham Khảo
Để hiểu sâu hơn về cách Excel xử lý dữ liệu thời gian và các hàm liên quan, bạn có thể tham khảo các tài liệu chính thống sau:
- Microsoft Support: Hàm DATEVALUE – Tài liệu chính thức từ Microsoft giải thích chi tiết về cú pháp và cách khắc phục lỗi.
- Microsoft Support: Hệ thống ngày tháng trong Excel – Giải thích sự khác biệt giữa hệ thống 1900 và 1904.
- Excel Functions: Các định dạng số tùy chỉnh – Hướng dẫn sâu về Custom Formatting.
Việc nắm vững cách chuyển đổi và xử lý số serial của ngày tháng không chỉ giúp bạn sửa lỗi hiển thị mà còn là bước đệm quan trọng để thành thạo các kỹ năng phân tích dữ liệu nâng cao trong Excel. Hãy thực hành ngay trên dữ liệu của bạn để ghi nhớ các thao tác này.
