Có thể bạn quan tâm: Hàm Vnd Đổi Số Ra Chữ Trong Excel
Giới thiệu
Trong công việc quản lý dữ liệu, đặc biệt là các bảng tính sites liên quan đến lịch sử tài chính, kế hoạch dự án hay lập kế hoạch sản xuất, chúng ta thường xuyên gặp phải tình huống cần chuyển đổi ngược một ngày dương sang ngày âm – hay ngược lại. Tại Excel, ngày dương (positive dates) được lưu trữ dưới dạng số nguyên dương, trong khi ngày âm (negative dates) thường xuất hiện khi ta làm việc với các hệ thống thời gian có thể lùi ngược, tính toán số ngày âm trong lịch sử hoặc đơn giản là muốn hiển thị ngày dưới dạng “-” để dễ nhận biết.
Mặc dù Excel cung cấp rất nhiều hàm liên quan đến ngày tháng, nhưng chưa có hàm tích hợp trực tiếp để “đổi” một ngày dương thành ngày âm một cách nhanh chóng. Do đó, bài viết này sẽ cung cấp cho bạn một số phương pháp hiệu quả nhất, từ công thức đơn giản trong ô tính đến macro VBA chuyên sâu, đểсаты chuyển đổi ngày dương sang âm trong Excel.
Có thể bạn quan tâm: Hàm Value Trong Excel: Cách Dùng, Ứng Dụng Và Những Lưu Ý Quan Trọng
1. Hiểu cách Excel lưu trữ ngày tháng
Trước khicheckout các công thức, chúng ta cần nắm rõ cách Excel xử lý ngày tháng:
| שחסות | Giá trị trong Excel | Ý nghĩa |
|---|---|---|
| 1 | 1 сценарий | 1/1/1900 (hoặc 1904 tùy hệ thống) |
| 2 | 2 | 2/1/1900 |
| ··· | ··· | ··· |
| 40909 | 1/1/2026 | Ngày 1/1/2026 |
| 0 | 0 | 0 ngày kể từ 1/1/1900 (đôi khi xuất hiện khi nhập dữ liệu sai) |
| -1 | -1 | 31/12/1899 (ngày âm 1) |
| -2 | -2 | 30/12/1899 |
- Ngày dương: các giá trị > 0.
- Ngày âm: các giá trị < 0.
Khi ta nhập một ngày trong ô và định dạng ô là “Ngày tháng”, Excel tự động chuyển đổi ngày đó thành số nguyên dương. Khi chuyển đổi sang ngày âm, ta có thể thực hiện bằng cách trừ một giá trị cố định (ví dụ: 1) hoặc sử dụng hàm SUM với -1.
Có thể bạn quan tâm: Hàm Tính Tỷ Lệ Tăng Trưởng Trong Excel: Cách Sử Dụng, Ví Dụ Và Mẹo Thực Tiễn
2. Phương pháp 1: Sử dụng công thức đơn giản
2.1. Công thức trực tiếp
Nếu bạn chỉ cần chuyển đổi một ngày cụ thể, bạn có thể viết công thức như sau:
= -A1
Trong đó A1 là ô chứa ngày dương. Khi áp dụng công thức này, Excel sẽ hiển thị giá trị âm tương ứng với ngày âm. Để hiển thị dưới dạng ngày, bạn cần thay đổi định dạng ô sang “Ngày tháng” và nhấn Ctrl + Shift + 1 (định dạng số). Kết quả sẽ hiển thị ngày âm tương ứng.
2.2. Công thức với hàm DATEVALUE
Nếu ô đầu vào là chuỗi ngày (text), bạn có thể dùng DATEVALUE để chuyển đổi sang số ngày rồi trừ:
=-DATEVALUE("01/01/2026")
Điều này giúp bạn tránh những lỗi khi nhập ngày dưới dạng chuỗi.
2.3. Ví dụ thực tế
| Ô A (Ngày dương) | Công thức | Kết quả (Ngày âm) |
|---|---|---|
1/1/2026 | =-A1 | 31/12/1899 |
15/02/2026 | =-A1 | 14/02/1890? |
Lưu ý: Khi nhập ngày âm, Excel sẽ coi giá trị âm là “ngày âm” và hiển thị theo định dạng ngày tùy vào cài đặt hệ thống. Ví dụ, ̀-1 sẽ hiển thị là
31/12/1899trên hệ thống thời gian 1900.
3. Phương pháp 2: Sử dụng hàm EDATE và DATE
Nếu bạn muốn chuyển đổi một ngày dương sang ngày âm > 1 năm trước, bạn có thể dùng EDATE:
=EDATE(A1, -12)
Điều này sẽ trừ 12 tháng khỏi ngày dương, trở thành ngày âm. Ví dụ:
1/1/2026→1/1/202615/02/2026→15/02 considering?
Bạn cũng có thể dùng DATE để tính toán ngày âm thủ công:
=DATE(YEAR(A1)-1, MONTH(A1), DAY(A1))
Điều này sẽ trừ 1 năm, nhưng giữ nguyên tháng và ngày.
4. Phương pháp 3: Sử dụng hàm TEXT để hiển thị ngày âm dưới dạng chuỗi
Đôi khi bạn chỉ muốn hiển thị ngày âm dưới dạng chuỗi (text) và không cần tính toán tiếp. Bạn có thể dùng:
=TEXT(-A1, "dd/mm/yyyy")
Kết quả sẽ là chuỗi ngày âm, ví dụ 31/12/1899.
5. Phương pháp 4: Macro VBA để chuyển đổi nhanh
Nếu bạn cần chuyển đổi một số lớn các ô, viết macro sẽ tiết kiệm thời gian.
5.1. Macro chuyển đổi toàn bộ cột
Sub ConvertPositiveToNegativeDates() Dim rng As Range Dim cell As Range ' Chọn cột chứa ngày dương (ví dụ cột A) Set rng = Intersect(ActiveSheet.UsedRange, Columns("A")) For Each cell In rng.Cells If IsDate(cell.Value) Then cell.Value = -cell.Value End If Next cell
End Sub
Chạy macro này sẽ lặp qua toàn bộ cột Mohamed và chuyển đổi ngày d Parliamentary.
5.2. Macro chuyển đổi khi nhập dữ liệu
Có thể bạn quan tâm: Hàm Đếm Số Lượng Nhân Viên Trong Excel: Tối Ưu Hóa Quản Lý Dữ Liệu Doanh Nghiệp
Bạn có thể g主页 vào sự kiện Worksheet_Change để tự động chuyển đổi ngay khi nhập ngày dương:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Me.Range("A:A")) ' cột A If Not rng Is Nothing Then Application.EnableEvents = False Dim cell As Range For Each cell In rng If IsDate(cell.Value) Then cell.Value = -cell.Value End If Next cell Application.EnableEvents = True End If
End Sub
6. Cách xử lý lỗi khi chuyển đổi
6.1. Lỗi “#VALUE!”
- Khi ô chứa dữ liệu không phải ngày hoặc chuỗi ngày không đúng định dạng, Excel trả về lỗi
#VALUE!. - Giải pháp: dùng
IFERRORđể kiểm tra:
=IFERROR(-A1, "Không phải ngày")
6.2. Lỗi “#NUM!”
- Khi giá trị số vượt quá giới hạn lưu trữ (ví dụ > 2958465), Excel trả lại
#NUM!. - Giải pháp: kiểm tra giới hạn trước khi chuyển:
=IF(ABS(A1) <= 2958465, -A1, "Giá trị vượt quá")
7. Ứng dụng thực tiễn
7.1. Kế hoạch dự án
Trong một bảng kế hoạch dự án, chúng ta thường cần tính “số ngày trễ” (negative days) khi dự án bị hoãn. Để tính số ngày trễ:
=DATEDIF(ActualEndDate, PlannedEndDate, "d")
Nếu kết quả dương, có thể dùng - để biến thành âm.
7.2. Báo cáo tài chính
ರಿ
Trong báo cáo tài chính, ngày âm có thể biểu thị “ngày chờ” hoặc “ngày chưa chi” khi tính toán các khoản გრ.
7.3. QuảnAudience
Trong bảng quản lý bán hàng, ngày âm có thể biểu thị “ngày hẹn” trước khi giao hàng, giúp đội bán hàng nắm bắt thời gian.
8. Tối ưu hóa công thức
8.1. Sử dụng INDEX và MATCH để tìm kiếm và chuyển đổi
Nếu bạn có danh sách ngày và cần tìm ngày âm tương ứng, có thể kết hợp INDEX và MATCH:
=INDEX(-$A$ economical, MATCH(B1, $A$1:$A$100, 0))
8.2. Sử dụng ARRAYFORMULA (Excel 365)
Nếu bạn dùng Excel 365, có thể dùng LET và MAP:
=MAP(A1:A10, LAMBDA(x, -x))
Điều (!$) cho phép chuyển đổi một dải ô dương thành âm một cách nhanh.
9. So sánh giữa Excel và Google Sheets
- Excel: Hỗ trợ
EDATE,DATE,DATEVALUE,TEXT, tắt macro. - Google Sheets: Hỗ trợ
DATE,DATEVALUE,TEXT,ARRAYFORMULAvàMAP(Google Apps Script). Macro được viết bằng Google Apps Script.
10. Các lưu ý khi làm việc với ngày âm
용
- Định dạng: Đảm bảo ô được định dạng đúng ngày để tránh hiển thị số nguyên dương thay vì ngày âm.
- Cân nhắc hệ thống thời gian: Excel 1900 vs 1904. Nếu bạn chuyển đổi ngày âm, cần kiểm tra hệ thống thời gian của workbook.
- Sử dụng
Round: Khi làm việc với ngày âm có phần thập phân (đơn vị giờ), bạn có thể dùngROUND(A1,-1)để làm tròn. - Bảo vệ dữ liệu: Khi chạy macro, hãy sao lưu dữ liệu trước để tránh mất mát.
11. Tổng kết
Trong Excel, việc chuyển đổi ngày dương sang ngày âm – dù có vẻ đơn giản – thực chất là một thao tác quan trọng trong nhiều trường hợp thực tiễn. Bạn có thể làm điều này bằng các công thức đơn giản, hàm tích hợp, hoặc macro VBA tùy vào quy mô công việc. Mỗi phương pháp đều có ưu điểm riêng:
- Công thức đơn giản: Dễ dàng thực hiện ngay lập tức.
- Hàm
EDATEvàDATE: Thích hợp khi cần tính toán theo tháng hoặc năm. - Macro VBA: Tối ưu khi cần thao tác trên hàng dữ liệu lớn.
- Google Apps Script: Dành cho người dùng Google Sheets.
Hãy lựa chọn phương pháp phù hợp nhất với quy trình làm việc của bạn và luôn kiểm tra kỹ lưỡng kết quả trước khi áp dụng vào báo cáo chính thức. Chúc bạn thành công trong việc quản lý dữ liệu ngày tháng trong Excel!
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ẻ.