Thời gian đọc ước tính: 12 phút
Những điểm chính
- Hàm EDATE là giải pháp tối ưu để cộng hoặc trừ số tháng chính xác từ một ngày cụ thể, tự động xử lý các tháng có số ngày khác nhau (28, 30, 31 ngày).
- Cú pháp:
=EDATE(start_date, months). Sử dụng số dương để cộng tháng và số âm để trừ tháng. - Tính toán năm: Có thể dùng EDATE để cộng/trừ năm bằng cách nhân số năm với 12 (Ví dụ: 2 năm = 24 tháng).
- Xử lý lỗi: Hướng dẫn khắc phục các lỗi thường gặp như
#VALUE!hoặc định dạng ngày tháng không hiển thị đúng. - Ứng dụng thực tế: Quản lý hạn thanh toán, ngày hết hạn hợp đồng và báo cáo tài chính định kỳ.
Trong quá trình xử lý dữ liệu thời gian, đặc biệt là trong các báo cáo tài chính hoặc quản lý dự án, bạn thường xuyên phải tính toán ngày tháng trong tương lai hoặc quá khứ dựa trên một mốc thời gian cố định. Ví dụ đơn giản nhất là xác định ngày đáo hạn hợp đồng sau 3 tháng, 6 tháng hoặc 1 năm.
Nhiều người mới sử dụng Excel thường mắc sai lầm khi cố gắng cộng thủ công (ví dụ: cộng 30 ngày cho mỗi tháng). Tuy nhiên, phương pháp này không chính xác vì số ngày trong mỗi tháng không giống nhau (tháng 2 có 28 hoặc 29 ngày, trong khi các tháng khác là 30 hoặc 31 ngày).
May mắn thay, Excel cung cấp một hàm tích hợp sẵn mang tên EDATE. Đây là công cụ chuyên biệt giúp bạn thực hiện các phép toán cộng trừ tháng với độ chính xác tuyệt đối mà không cần quan tâm đến độ dài của từng tháng.
Bài viết này sẽ hướng dẫn bạn cách sử dụng hàm EDATE từ cơ bản đến nâng cao để quản lý dữ liệu ngày tháng hiệu quả.
Hàm EDATE Là Gì? Cấu Trúc Và Ý Nghĩa
Hàm EDATE trả về số sê-ri biểu thị ngày tháng, là kết quả của việc cộng hoặc trừ một số tháng nhất định từ một ngày bắt đầu.
Cú pháp hàm
=EDATE(start_date, months)Trong đó:
- start_date (Bắt buộc): Ngày mốc ban đầu mà bạn muốn tính toán. Ngày này phải được nhập ở định dạng mà Excel hiểu được (DATE format) hoặc là kết quả của một công thức khác.
- months (Bắt buộc): Số tháng bạn muốn cộng thêm hoặc trừ đi.
- Nhập số dương (ví dụ: 3) để tính ngày trong tương lai.
- Nhập số âm (ví dụ: -3) để tính ngày trong quá khứ.
- Nếu bạn nhập số thập phân (ví dụ: 1.5), Excel sẽ tự động cắt bỏ phần thập phân và chỉ giữ lại phần nguyên (tức là 1 tháng).
Hướng Dẫn Cộng Thêm Tháng Vào Một Ngày
Giả sử bạn đang quản lý một danh sách các dự án với “Ngày bắt đầu” (Start Date) và cần xác định “Ngày kết thúc” dựa trên thời gian thực hiện tính bằng tháng.
Hãy xem xét tập dữ liệu mẫu dưới đây. Cột A chứa ngày bắt đầu và Cột B chứa số tháng cần cộng thêm.
Dữ liệu mẫu gồm ngày bắt đầu ở cột A và số tháng cần cộng ở cột B trong Excel
Để tính ngày kết quả trong cột C, chúng ta sẽ sử dụng công thức EDATE tại ô C2 như sau:
=EDATE(A2, B2)Phân tích công thức:
- A2: Là ngày mốc (31/05/2021).
- B2: Là số tháng cần cộng (5 tháng).
Sau khi nhập công thức và nhấn Enter, bạn sẽ nhận được kết quả là ngày 31/10/2021.
Công thức EDATE được áp dụng để cộng thêm tháng vào ngày bắt đầu
Lưu ý rằng Excel đã tự động xử lý logic ngày tháng. Mặc dù tháng 5 có 31 ngày, nhưng nếu bạn cộng thêm 1 tháng vào ngày 31/01 (tháng 1), Excel sẽ trả về ngày 28/02 hoặc 29/02 tùy theo năm nhuận, đảm bảo ngày kết quả luôn hợp lệ.
Cách trừ tháng (Tìm ngày trong quá khứ)
Nguyên tắc hoạt động của hàm EDATE cho phép tính toán ngược thời gian rất đơn giản. Bạn không cần thay đổi tên hàm, chỉ cần thay đổi dấu của đối số thứ hai (months).
Trong ví dụ trên, nếu ô B3 có giá trị là -2, công thức =EDATE(A3, B3) sẽ lấy ngày ở A3 và lùi lại 2 tháng về quá khứ.
Đây là tính năng hữu ích khi bạn cần truy xuất ngày nhập hàng gốc dựa trên ngày hết hạn sản phẩm, hoặc xác định ngày bắt đầu hợp đồng dựa trên ngày kết thúc.
Lưu ý quan trọng về định dạng:
Kết quả trả về của hàm EDATE ban đầu có thể hiển thị dưới dạng một dãy số (ví dụ: 44347). Đây là cách Excel lưu trữ ngày tháng (Serial Number). Để xem dưới dạng ngày tháng quen thuộc, bạn cần:
- Chọn ô chứa kết quả.
- Nhấn tổ hợp phím
Ctrl + 1. - Trong thẻ Number, chọn Date.
- Chọn định dạng hiển thị mong muốn (ví dụ: dd/mm/yyyy).
Hướng Dẫn Cộng Năm Vào Một Ngày
Mặc dù Excel không có hàm “EYEAR” chuyên biệt, nhưng chúng ta có thể tận dụng sự linh hoạt của hàm EDATE để cộng hoặc trừ số năm.
Quy tắc rất đơn giản: 1 năm = 12 tháng.
Để cộng thêm n năm, bạn chỉ cần nhân số năm đó với 12 trong đối số thứ hai của hàm EDATE.
Giả sử bạn có dữ liệu như hình dưới, với cột A là ngày bắt đầu và cột B là số năm cần cộng thêm:
Bảng dữ liệu Excel với cột số năm cần cộng thêm vào ngày gốc
Công thức để tính ngày sau một số năm nhất định sẽ là:
=EDATE(A2, B2*12)Giải thích cơ chế:
- Tại ô C2, công thức lấy ngày 31/05/2021 (A2).
- Lấy số năm là 1 (B2) nhân với 12, kết quả là 12 tháng.
- Hàm EDATE cộng 12 tháng vào ngày gốc.
Kết quả nhận được là 31/05/2022, chính xác là 1 năm sau ngày bắt đầu.
Kết quả hiển thị sau khi dùng hàm EDATE nhân với 12 để cộng năm
Xử lý số năm lẻ (Số thập phân)
Một ưu điểm của phương pháp nhân 12 này là khả năng xử lý các khoảng thời gian không tròn năm.
Ví dụ: Bạn muốn tìm ngày đáo hạn sau 1.5 năm.
- Phép tính:
1.5 * 12 = 18tháng. - Công thức:
=EDATE(Start_Date, 18). - Excel sẽ trả về ngày chính xác sau 1 năm và 6 tháng.
Tuy nhiên, cần thận trọng nếu kết quả của phép nhân Số năm * 12 ra số lẻ (ví dụ: 1.3 năm * 12 = 15.6 tháng). Như đã đề cập, hàm EDATE sẽ cắt bỏ phần thập phân (.6) và chỉ cộng thêm 15 tháng. Để đảm bảo độ chính xác cho các trường hợp phức tạp, bạn nên làm tròn số trước khi đưa vào hàm.
Ứng Dụng Thực Tế Trong Doanh Nghiệp
Việc nắm vững hàm EDATE giúp bạn giải quyết nhiều bài toán nghiệp vụ một cách tự động và chính xác:
1. Quản lý nhân sự (HR)
- Tính ngày kết thúc thử việc: Nếu nhân viên bắt đầu vào ngày 15/01 và thử việc 2 tháng. Công thức
=EDATE("15/01/2024", 2)sẽ trả về 15/03/2024. - Xác định ngày nghỉ hưu: Dựa trên ngày sinh và tuổi nghỉ hưu quy định (ví dụ: 60 tuổi = 720 tháng).
2. Quản lý tài chính & Kế toán
- Lập lịch thanh toán định kỳ: Tạo danh sách các ngày thanh toán hóa đơn hàng tháng cho các dịch vụ subscription.
- Tính ngày đáo hạn trái phiếu/tiết kiệm: Xác định chính xác ngày đáo hạn của các khoản đầu tư có kỳ hạn 3 tháng, 6 tháng, 12 tháng, 24 tháng.
3. Chuỗi cung ứng
- Quản lý hạn sử dụng (Expiry Date): Từ ngày sản xuất, cộng thêm thời hạn sử dụng (thường tính bằng tháng) để in tem nhãn hoặc kiểm soát hàng tồn kho sắp hết hạn.
Các Lỗi Thường Gặp Và Cách Khắc Phục
Khi sử dụng hàm EDATE, đôi khi bạn sẽ gặp phải các mã lỗi. Dưới đây là ý nghĩa và cách xử lý:
Lỗi #VALUE!
- Nguyên nhân: Đối số
start_datekhông phải là một ngày hợp lệ. Ví dụ, bạn nhập ngày dưới dạng văn bản (Text) mà Excel không thể chuyển đổi, hoặc ô tham chiếu chứa chữ cái. - Khắc phục: Kiểm tra lại ô chứa ngày bắt đầu. Đảm bảo ô đó được định dạng là Date hoặc Number. Nếu nhập trực tiếp vào công thức, hãy đặt ngày trong dấu ngoặc kép hoặc dùng hàm DATE(), ví dụ:
=EDATE(DATE(2023,1,15), 3).
Lỗi #NUM!
- Nguyên nhân: Kết quả ngày tháng nằm ngoài phạm vi hỗ trợ của Excel. Excel chỉ hỗ trợ các ngày từ 01/01/1900 đến 31/12/9999.
- Khắc phục: Kiểm tra lại số tháng bạn cộng hoặc trừ. Có thể con số quá lớn khiến năm vượt quá 9999 hoặc trở về trước năm 1900.
Kết quả hiển thị số (ví dụ: 44125)
- Nguyên nhân: Ô kết quả đang để định dạng General hoặc Number.
- Khắc phục: Chuyển định dạng ô sang Short Date hoặc Long Date (Ctrl + 1).
So Sánh EDATE Và EOMONTH
Rất nhiều người nhầm lẫn giữa EDATE và EOMONTH. Mặc dù cả hai đều dùng để cộng trừ tháng, nhưng mục đích sử dụng khác nhau:
| Tiêu chí | Hàm EDATE | Hàm EOMONTH |
|---|---|---|
| Mục đích | Trả về ngày tương ứng sau N tháng. | Trả về ngày cuối cùng của tháng sau N tháng. |
| Ví dụ | Start: 15/01. Cộng 1 tháng. | Start: 15/01. Cộng 1 tháng. |
| Kết quả | 15/02 | 28/02 (hoặc 29/02 nếu năm nhuận) |
| Ứng dụng | Hợp đồng tròn tháng, kỳ hạn vay. | Báo cáo cuối tháng, chốt sổ kế toán. |
Sử dụng đúng hàm sẽ giúp dữ liệu của bạn phản ánh đúng bản chất nghiệp vụ, tránh sai sót trong việc tính lãi suất hoặc phạt chậm thanh toán.
FAQ: Câu Hỏi Thường Gặp
1. Tôi có thể dùng EDATE để cộng ngày không?
Không. Hàm EDATE chỉ hoạt động với đơn vị là tháng. Để cộng ngày, bạn chỉ cần dùng phép cộng đơn giản. Ví dụ: =A1 + 15 (Cộng 15 ngày vào ngày ở ô A1).
2. Hàm EDATE xử lý năm nhuận như thế nào?
EDATE rất thông minh trong việc này. Nếu bạn cộng 1 tháng vào ngày 31/01 của một năm nhuận, kết quả sẽ là 29/02. Nếu không phải năm nhuận, kết quả là 28/02. Nó tự động điều chỉnh về ngày hợp lệ cuối cùng của tháng đó.
3. Làm sao để tính ngày làm việc sau 3 tháng (bỏ qua cuối tuần)?
EDATE không loại bỏ ngày nghỉ. Để tính toán ngày làm việc, bạn cần kết hợp hàm WORKDAY hoặc WORKDAY.INTL. Tuy nhiên, nếu logic là “Cộng 3 tháng trước, sau đó nếu rơi vào cuối tuần thì dời sang thứ 2”, bạn có thể dùng công thức kết hợp: =WORKDAY(EDATE(A1,3)-1, 1).
4. Tại sao tôi nhập 1.5 tháng nhưng Excel chỉ cộng 1 tháng?
Hàm EDATE cắt bỏ phần thập phân của đối số months. Nó coi 1.5 là 1 và 1.9 cũng là 1. Nếu bạn muốn cộng 1 tháng rưỡi (khoảng 45 ngày), bạn nên dùng phép cộng ngày: =A1 + 45.
5. Tôi có thể dùng số âm cho năm không?
Có. Bạn có thể trừ năm bằng cách nhân số âm với 12. Ví dụ: =EDATE(A1, -2*12) sẽ trả về ngày này của 2 năm trước.
Nguồn tham khảo
- Microsoft Support: Hàm EDATE – Hỗ trợ của Microsoft
- ExcelJet: Excel EDATE Function Guide
- GcfGlobal: Excel Formulas: Working with Dates
- Ablebits: Add or subtract months to date in Excel
