Có thể bạn quan tâm: Cash Flow Forecast Template Excel: Hướng Dẫn Chi Tiết Từ A Đến Z Để Quản Lý Dòng Tiền Hiệu Quả
Mở đầu
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, việc hiển thị ngày tháng theo định dạng phù hợp với nhu cầu của người dùng đôi khi lại gây ra không ít khó khăn, đặc biệt khi làm việc với dữ liệu đa ngôn ngữ, đa khu vực hoặc khi cần chuẩn hoá dữ liệu cho các báo cáo. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, toàn diện và thực tiễn về cách thay đổi định dạng ngày trong Excel, bao gồm:
- Giải thích cơ bản về cách Excel lưu trữ ngày tháng.
- Các cách thay đổi định dạng ngày bằng công cụ Format Cells, Custom Format, TEXT, DATEVALUE, TEXT TO COLUMNS, và Power Query.
- Cách áp dụng định dạng ngày cho một ô, một vùng dữ liệu, hoặc toàn bộ cột.
- Xử lý các vấn đề thường gặp: ngày tháng bị nhận dạng sai, ngày tháng dưới dạng văn bản, ngày tháng quốc tế (dd/mm/yyyy vs mm/dd/yyyy), và ngày tháng 1900/1904.
- Các mẹo nâng cao: sử dụng hàm IF, VLOOKUP, XLOOKUP, PivotTable, và Conditional Formatting để làm việc hiệu quả hơn với ngày tháng.
- Thực hành thực tế qua các ví dụ thực tế trong kinh doanh, kế toán, quản lý dự án và phân tích dữ liệu.
Với nội dung trên hơn 1500 từ, bạn sẽ nắm vững mọi khía cạnh cần thiết để thao tác và tùy chỉnh ngày tháng trong Excel một cách chuyên nghiệp.
Có thể bạn quan tâm: Cách Canh Đều Chữ Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
1. Excel Lưu Trữ Ngày Tháng Như Thế Nào?
Trước khi bắt đầu thay đổi định dạng, bạn cần hiểu cách Excel lưu trữ ngày tháng:
| Thuật ngữ | Mô tả |
|---|---|
| Serial Number | Excel lưu ngày dưới dạng một số nguyên (số ngày tính từ ngày 1/1/1900 hoặc 1/1/1904 tùy tùy chọn). |
| Phần thập phân | Phần thập phân của số biểu thị thời gian trong ngày (ví dụ: 0.5 = 12:00 PM). |
| Ngày 0 | Trong hệ thống 1900, ngày 0 tương đương 0/1/1900 (Excel hiển thị là 0/1/1900). |
| Ngày 60 | Excel có lỗi “leap year” 1900, ngày 60 được tính là 29/2/1900 (không tồn tại thực tế). |
Lưu ý: Khi bạn nhập một ngày, Excel tự động chuyển nó thành serial number. Định dạng ngày chỉ là cách hiển thị giá trị này.
1.1. Kiểm tra Serial Number
- Nhập một ngày bất kỳ, ví dụ 15/08/2026 vào ô A1.
- Đổi định dạng ô sang Number (General) → bạn sẽ thấy số 45205. Đây là serial number của ngày đó.
1.2. 1900 vs 1904 Date System
- 1900 System: Mặc định cho Windows. Ngày 1 = 1/1/1900.
- 1904 System: Dành cho Mac (cũng có thể dùng trên Windows). Ngày 0 = 1/1/1904.
Bạn có thể chuyển đổi:
– File → Options → Advanced → When calculating this workbook → chọn 1904 date system.
Có thể bạn quan tâm: Không Mở Được File Excel: Nguyên Nhân, Giải Pháp Và Các Bước Khắc Phục Chi Tiết
2. Thay Đổi Định Dạng Ngày Bằng “Format Cells”
2.1. Sử Dụng Menu Chuột Phải
- Chọn ô hoặc vùng dữ liệu cần thay đổi.
- Click chuột phải → Format Cells (hoặc nhấn
Ctrl + 1). - Tab Number → chọn Date.
- Ở Locale (location), chọn Vietnam (Vietnam) hoặc English (United States) tùy nhu cầu.
- Chọn mẫu ngày mong muốn (vd:
dd/mm/yyyy,mm-dd-yy,dddd, mmmm dd, yyyy). - Click OK.
2.2. Sử Dụng Ribbon
- Home → Number group → Dropdown “Number Format” → chọn Short Date hoặc Long Date.
- Để mở More Number Formats, click vào biểu tượng mũi tên nhỏ ở góc dưới bên phải nhóm Number.
2.3. Định Dạng Nhanh Với Các Kiểu Mặc Định
| Kiểu | Mô tả |
|---|---|
| Short Date | dd/mm/yyyy (đối với locale VN) hoặc mm/dd/yyyy (US). |
| Long Date | dddd, mmmm dd, yyyy (ví dụ: Thứ Hai, Tháng Giêng 01, 2026). |
| Custom | Tự tạo mẫu (xem phần 3). |
3. Định Dạng Tùy Chỉnh (Custom Date Format)
Khi các mẫu có sẵn không đáp ứng nhu cầu, bạn có thể tạo định dạng riêng:
3.1. Các ký tự định dạng ngày
| Ký tự | Ý nghĩa |
|---|---|
d | Ngày không có số 0 đầu (1‑31). |
dd | Ngày có số 0 đầu (01‑31). |
ddd | Tên ngày viết tắt (Mon, Tue). |
dddd | Tên ngày đầy đủ (Monday, Tuesday). |
m | Tháng không có số 0 (1‑12). |
mm | Tháng có số 0 đầu (01‑12). |
mmm | Tên tháng viết tắt (Jan, Feb). |
mmmm | Tên tháng đầy đủ (January, February). |
yy | Năm 2 chữ số (23). |
yyyy | Năm 4 chữ số (2026). |
h/hh | Giờ (12‑giờ). |
H/HH | Giờ (24‑giờ). |
m/mm (sau giờ) | Phút. |
s/ss | Giây. |
AM/PM | Định dạng buổi sáng/chiều. |
Lưu ý: Khi sử dụng
mcho tháng và phút trong cùng một định dạng, Excel sẽ phân biệt dựa vào vị trí (trước hoặc sau giờ).
3.2. Ví dụ thực tế
| Mẫu Custom | Kết quả (với ngày 15/08/2026 14:35) |
|---|---|
dd-mmm-yyyy | 15-Aug-2026 |
dddd, dd mmmm yyyy | Tuesday, 15 August 2026 |
yyyy/mm/dd | 2026/08/15 |
dd/mm/yyyy hh:mm AM/PM | 15/08/2026 02:35 PM |
mm-dd-yy | 08-15-23 |
dd/mm/yyyy;@ (đảm bảo văn bản) | 15/08/2026 |
3.3. Cách tạo Custom Format
- Format Cells → Number → Custom.
- Nhập mẫu vào ô Type.
- Click OK.
4. Chuyển Đổi Ngày Thành Văn Bản Bằng Hàm TEXT
Đôi khi bạn cần xuất ngày dưới dạng chuỗi để ghép vào công thức hoặc báo cáo.
=TEXT(A1, "dd/mm/yyyy")
- A1: ô chứa ngày.
- “dd/mm/yyyy”: mẫu định dạng.
4.1. Khi Dùng TEXT Trong CONCATENATE
="Ngày báo cáo: " & TEXT(A1, "dddd, dd mmmm yyyy")
Kết quả: Ngày báo cáo: Tuesday, 15 August 2026
4.2. Lưu Ý Khi Dùng TEXT
- Kết quả trả về là văn bản, không còn là ngày thực.
- Không thể thực hiện các phép tính ngày (trừ, cộng) trên kết quả TEXT trừ khi chuyển lại sang ngày bằng
DATEVALUE.
5. Chuyển Đổi Văn Bản Thành Ngày: DATEVALUE và Các Phương Pháp Khác
5.1. Hàm DATEVALUE
=DATEVALUE("15/08/2026")
- Trả về serial number 45205.
- Khi định dạng ô thành ngày, sẽ hiển thị đúng.
5.2. Khi Văn Bản Không Được Nhận Diện
Nếu Excel không nhận dạng đúng định dạng (ví dụ: 2026-15-08), bạn có thể:
- Sử dụng Text to Columns (xem phần 6).
- Kết hợp hàm MID, LEFT, RIGHT để tách và tạo ngày:
=DATE(RIGHT(A1,4), MID(A1,4,2), LEFT(A1,2))
Giả sử A1 chứa "15-08-2026".
5.3. Hàm DATE
=DATE(year, month, day)
Bạn có thể lấy các thành phần từ chuỗi bằng hàm LEFT, MID, RIGHT hoặc TEXTSPLIT (Excel 365).
=DATE(VALUE(RIGHT(A1,4)), VALUE(MID(A1,4,2)), VALUE(LEFT(A1,2)))
6. Xử Lý Dữ Liệu Đầu Vào Bằng “Text to Columns”
Khi nhập dữ liệu ngày từ file CSV hoặc nguồn khác, Excel có thể để chúng dưới dạng text. Text to Columns giúp chuyển nhanh:
- Chọn cột chứa ngày dạng text.
- Data → Text to Columns.
- Chọn Delimited → Next.
- Bỏ chọn mọi delimiter → Next.
- Ở Column data format, chọn Date và chọn DMY (hoặc MDY, tùy dữ liệu).
- Click Finish.
Sau bước này, các ô sẽ được chuyển thành ngày thực và có thể định dạng lại.
6.1. Khi Dữ Liệu Có Nhiều Định Dạng
Nếu một cột có ngày ở cả dạng dd/mm/yyyy và yyyy-mm-dd, bạn cần:
- Sao chép cột sang một cột tạm.
- Sử dụng IFERROR kết hợp DATEVALUE:
=IFERROR(DATEVALUE(A2), DATEVALUE(SUBSTITUTE(A2,"-","/")))
Sau đó sao chép giá trị và Paste Special → Values để thay thế cột gốc.
7. Sử Dụng Power Query Để Chuẩn Hóa Định Dạng Ngày
Có thể bạn quan tâm: Certificate Of Excellence Là Gì? – Khám Phá Ý Nghĩa, Loại Hình Và Cách Đạt Được Danh Hiệu Danh Giá
Power Query (Get & Transform) là công cụ mạnh mẽ cho việc làm sạch dữ liệu.
7.1. Nhập Dữ Liệu
- Data → Get Data → From File → From Workbook/CSV.
- Chọn file và nhấn Import.
- Trong Power Query Editor, chọn cột ngày.
7.2. Chuyển Đổi Kiểu Dữ Liệu
- Click chuột phải trên tiêu đề cột → Change Type → Using Locale….
- Chọn Date và Locale (ví dụ: Vietnamese (Vietnam)).
- Nhấn OK.
Power Query sẽ tự động nhận diện và chuẩn hoá các định dạng khác nhau thành ngày chuẩn.
7.3. Thêm Cột Tùy Chỉnh
Nếu cần chuyển đổi định dạng thành chuỗi:
= Table.AddColumn(#"Previous Step", "Ngày Văn Bản", each Date.ToText(Ngày, "dd/MM/yyyy"), type text)
Sau khi hoàn thành, Close & Load để đưa dữ liệu vào Excel.
8. Áp Dụng Định Dạng Ngày Cho Toàn Bộ Cột Hoặc Bảng
8.1. Định Dạng Toàn Cột
- Click vào tiêu đề cột (ví dụ: C).
- Ctrl + 1 → Number → Date → chọn mẫu → OK.
8.2. Sử Dụng Table Styles
Nếu dữ liệu ở dạng Table (Insert → Table), khi bạn thay đổi định dạng một ô trong cột, toàn bộ cột sẽ tự động áp dụng cùng định dạng.
8.3. Định Dạng Động Với Conditional Formatting
Bạn có thể làm nổi bật ngày trong một khoảng thời gian:
- Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Công thức ví dụ:
=AND($A2>=TODAY()-30, $A2<=TODAY()). - Chọn định dạng (màu nền, font) → OK.
Điều này giúp nhanh chóng xem các ngày gần nhất.
9. Các Vấn Đề Thường Gặp Khi Làm Việc Với Định Dạng Ngày
| Vấn đề | Nguyên nhân | Giải pháp |
|---|---|---|
| Ngày hiển thị dưới dạng số (44723) | Định dạng ô là Number/General | Chọn ô → Format Cells → Date hoặc Custom. |
| Excel nhận dạng sai ngày (15/08/2026 → 08/15/2026) | Thiết lập Locale không phù hợp | Đổi Locale trong Format Cells hoặc Data → Text to Columns → chọn đúng Date format. |
| Ngày 29/02/2026 không tồn tại | Nhập ngày không hợp lệ | Kiểm tra dữ liệu nguồn, sử dụng Data Validation để ngăn nhập sai. |
| Ngày hiển thị #VALUE! | Công thức DATEVALUE nhận dạng không đúng | Đảm bảo chuỗi ngày đúng định dạng, hoặc dùng SUBSTITUTE để thay đổi dấu phân cách. |
| Ngày bị lệch 1 ngày khi chuyển từ Mac (1904) sang Windows (1900) | Hệ thống ngày khác nhau | Thiết lập cùng Date System cho cả hai workbook (File → Options → Advanced). |
10. Mẹo Nâng Cao Khi Xử Lý Ngày Trong Excel
10.1. Tính Khoảng Cách Ngày
=DATEDIF(A2, B2, "d") // số ngày
=DATEDIF(A2, B2, "m") // số tháng
=DATEDIF(A2, B2, "y") // số năm
10.2. Tự Động Tạo Ngày Bắt Đầu/ Kết Thúc Tháng
=EOMONTH(A2, 0) // ngày cuối tháng của A2
=DATE(YEAR(A2), MONTH(A2), 1) // ngày đầu tháng
10.3. Sắp Xếp và Lọc Dữ Liệu Theo Tháng/Năm
- Thêm cột phụ:
=TEXT(A2,"yyyy-mm"). - Dùng Sort & Filter hoặc PivotTable để nhóm.
10.4. Sử Dụng Hàm XLOOKUP Với Ngày
=XLOOKUP(DATE(2026,8,15), A:A, B:B, "Không tìm thấy")
10.5. Định Dạng Ngày Trong PivotTable
- Kéo trường ngày vào Rows.
- Click chuột phải → Group → chọn Months, Years.
- Định dạng hiển thị bằng Field Settings → Number Format.
10.6. Đánh Dấu Ngày Quan Trọng Bằng Icon Set
- Conditional Formatting → Icon Sets.
- Thiết lập công thức:
=A2=TODAY()→ hiển thị biểu tượng đặc biệt.
11. Thực Hành: 5 Bài Tập Thực Tế
Bài 1: Chuẩn Hoá Ngày Từ CSV
Bạn nhận được file sales.csv với cột OrderDate dạng dd-mm-yyyy. Hãy:
- Nhập file vào Excel.
- Dùng Text to Columns để chuyển cột thành ngày.
- Định dạng thành
dd/mm/yyyy.
Bài 2: Tạo Báo Cáo Tháng
Có bảng Sales với cột Date và Revenue. Yêu cầu:
- Thêm cột MonthYear =
TEXT(Date,"yyyy-mm"). - Dùng PivotTable để tổng doanh thu theo MonthYear.
- Định dạng MonthYear trong PivotTable thành
mmm yyyy(Jan 2026).
Bài 3: Kiểm Tra Ngày Hết Hạn
Trong danh sách Contracts có cột ExpirationDate. Hãy:
- Đánh dấu các hợp đồng hết hạn trong 30 ngày tới bằng màu nền Red.
- Sử dụng Conditional Formatting với công thức
=AND($B2>=TODAY(), $B2<=TODAY()+30).
Bài 4: Tính Tuổi Nhân Viên
Cột BirthDate có ngày sinh. Thêm cột Age:
=INT((TODAY()-BirthDate)/365.25)
Bài 5: Đổi Định Dạng Ngày Cho Báo Cáo PDF
Bạn cần xuất ngày dưới dạng chữ: “Ngày 15 tháng 8 năm 2026”. Công thức:
="Ngày " & DAY(A2) & " tháng " & MONTH(A2) & " năm " & YEAR(A2)
Hoặc dùng hàm TEXT:
="Ngày " & TEXT(A2,"d") & " tháng " & TEXT(A2,"m") & " năm " & TEXT(A2,"yyyy")
12. Tổng Kết
Việc thay đổi định dạng ngày trong Excel không chỉ là thay đổi hình thức hiển thị mà còn liên quan tới:
- Hiểu cách Excel lưu trữ ngày (serial number).
- Biết cách sử dụng công cụ Format Cells, Custom Format, TEXT, DATEVALUE, Text to Columns, Power Query.
- Xử lý các lỗi thường gặp như ngày không nhận dạng, hệ thống ngày 1900/1904, và các định dạng quốc tế.
- Áp dụng các kỹ thuật nâng cao như Conditional Formatting, PivotTable, và hàm tính ngày để tối ưu hoá quy trình làm việc.
Bằng cách nắm vững các kiến thức trên, bạn sẽ tự tin thao tác với bất kỳ dữ liệu ngày nào, từ việc nhập dữ liệu hàng ngày, chuẩn hoá báo cáo tài chính, đến việc phân tích dữ liệu thời gian trong các dự án lớn. Hãy thực hành thường xuyên với các ví dụ thực tế, và bạn sẽ thấy Excel trở thành công cụ mạnh mẽ, linh hoạt cho mọi nhu cầu quản lý ngày tháng.
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ẻ.