Đánh giá lequocthai.com:
Các nhà phân tích tài chính thường dùng hàm EOMONTH để tính ngày đáo hạn cho các khoản phải trả và phải thu của công ty.
Cú pháp
Cú pháp của hàm EOMONTH như sau:
=EOMONTH(start_date, months)Đối số:
- start_date – Giá trị đại diện cho ngày bắt đầu. Nó phải là một ngày hợp lệ, tức là một số sê‑ri mà Excel nhận dạng được là ngày. Nếu nhập dưới dạng văn bản, Excel sẽ trả về lỗi.
- months – Số âm hoặc số dương biểu thị số tháng sẽ được cộng vào hoặc trừ khỏi giá trị trong start_date.
Các đặc điểm quan trọng của hàm EOMONTH
- Nhập một số dương vào đối số months sẽ cho ra ngày trong tương lai, còn nhập số âm sẽ cho ra ngày trong quá khứ.
- Nếu đối số start_date không phải là một số sê‑ri hợp lệ mà Excel công nhận là ngày, hàm EOMONTH sẽ trả về lỗi #NUM!. Nếu ngày được cung cấp không phải là ngày hợp lệ, hàm sẽ trả về lỗi #VALUE!.
- Nếu ngày trong đối số start_date có phần thời gian, hàm EOMONTH sẽ bỏ qua phần thời gian đó và chỉ giữ lại ngày.
- EOMONTH trả về ngày cuối cùng của tháng, được tính bằng cách cộng hoặc trừ n tháng vào ngày ban đầu. Để lấy một ngày n tháng sau ngày ban đầu, hãy dùng hàm EDATE.
- Ô chứa kết quả của hàm EOMONTH phải được định dạng dưới dạng ngày vì hàm sẽ trả về một số sê‑ri.
Ví dụ về hàm EOMONTH
Bây giờ chúng ta cùng xem một số ví dụ thực tế của hàm EOMONTH trong Excel.
Ví dụ 1 – Cộng n tháng vào hàm EOMONTH
Hãy bắt đầu với công thức cơ bản của hàm EOMONTH. Giả sử chúng ta có một ngày muốn cộng n tháng vào. Công thức sẽ là:
=EOMONTH(A2, 2)
Ngày start_date của chúng ta là 25‑12‑2001 và chúng ta cộng 2 tháng. Kết quả hiển thị là 28‑02‑2002, không phải 25‑02‑2002. EOMONTH luôn trả về ngày cuối cùng của tháng.
Ví dụ 2 – Trừ n tháng bằng hàm EOMONTH
Hàm EOMONTH cũng có thể trả về một ngày trong quá khứ. Chúng ta dùng cùng một công thức và cùng dữ liệu, chỉ thay đổi đối số months thành một số âm:
=EOMONTH(A2, -3)
Kết quả là 30‑09‑2001, tức là ngày cuối cùng của tháng, 3 tháng trước ngày ban đầu. Một lần nữa, kết quả luôn là ngày cuối cùng của tháng tính được.
Ví dụ 3 – Lấy ngày cuối tháng của start_date
Nếu chúng ta không muốn cộng hay trừ tháng nào, chỉ muốn biết ngày cuối cùng của tháng dựa trên ngày ban đầu. Công thức rất trực quan:
=EOMONTH(A2, 0)
Ở đây, EOMONTH chỉ lấy ngày 25‑12‑2001, cộng 0 tháng và trả về ngày cuối cùng của tháng đó là 31‑12‑2001.
Ví dụ 4 – Lấy ngày cuối tháng hiện tại bằng EOMONTH
Chúng ta có thể dùng hàm EOMONTH để lấy ngày cuối cùng của tháng hiện tại bằng cách lồng hàm TODAY làm đối số start_date:
=EOMONTH(TODAY(), 0)
Lúc viết bài này là tháng 5‑2021, vì vậy kết quả là 31‑05‑2021.
Ví dụ 5 – Lấy ngày đầu tháng hiện tại bằng EOMONTH
Nếu suy xét logic, mặc dù không thể dùng EOMONTH để trực tiếp trả về ngày đầu tháng, chúng ta có thể lấy ngày cuối tháng trước rồi cộng 1 ngày để được ngày đầu tháng hiện tại. Công thức:
=EOMONTH(TODAY(), -1) + 1
Hàm EOMONTH sẽ trả về ngày cuối tháng trước (30‑04‑2021). Cộng thêm 1 ngày sẽ cho chúng ta 01‑05‑2021.
Sử dụng EOMONTH cho ngày được lưu dưới dạng văn bản
Giả sử bạn có dữ liệu ngày ở dạng văn bản “yyyymmdd”. Bạn muốn tính ngày cuối tháng cho tháng cách 3 tháng sau mỗi ngày này.
Đầu tiên, cần chuyển văn bản sang ngày mà Excel nhận dạng bằng hàm DATE, rồi lồng hàm DATE vào đối số start_date của EOMONTH:
=EOMONTH(DATE(LEFT(A2, 4), MID(A2, 5, 2), RIGHT(A2, 2)), 3)
Hàm DATE sẽ chuyển 20011225 thành ngày hợp lệ 25‑12‑2001 và truyền cho EOMONTH. Cộng 3 tháng sẽ cho chúng ta tháng 3‑2002 và kết quả cuối cùng là 31‑03‑2002.
Sử dụng EOMONTH cùng SUMIFS
Bây giờ chúng ta sẽ thực hành một công thức thú vị. Giả sử có một cột ngày và một cột doanh thu tương ứng. Bạn muốn tổng hợp doanh thu theo tháng.
Để làm điều này, dùng hàm SUMIFS. Công thức có vẻ phức tạp lúc đầu, nhưng khi chia ra sẽ rất dễ hiểu:
=SUMIFS((B2:B7), (A2:A7), ">=" & D4, (A2:A7), "<=" & EOMONTH(D4,0))
Chi tiết công thức:
- Phần 1: (B2:B7) – dải dữ liệu cần cộng.
- Phần 2: (A2:A7), ">=" & D4 – chỉ lấy những ngày ≥ ngày đầu tháng trong ô D4.
- Phần 3: (A2:A7), "<=" & EOMONTH(D4,0) – chỉ lấy những ngày ≤ ngày cuối tháng trong ô D4.
Kết hợp Phần 2 và Phần 3 sẽ lọc các hàng nằm trong khoảng từ ngày đầu đến ngày cuối tháng D4, sau đó SUMIFS cộng tổng doanh thu trong cột B.
Lưu ý: Ô D4 phải nhập ngày dưới dạng 01/01/2001; sau đó bạn có thể định dạng ô thành "mmmm" để hiển thị chỉ tên tháng.
Sử dụng EOMONTH với hàm IF
Bạn đang điều hành một công ty bán nội thất sỉ, cung cấp hàng hoá cho các nhà bán lẻ với điều kiện thanh toán trong 60 ngày kể từ ngày giao.
Bạn có bảng ghi ngày giao, tên nhà bán lẻ và giá trị hàng hoá. Để theo dõi các khoản phải thu quá hạn, bạn muốn chia danh sách nhà bán lẻ thành ba nhóm:
- Các khoản chưa tới 60 ngày tính đến cuối tháng hiện tại.
- Các khoản từ 61‑90 ngày tính đến cuối tháng (quá hạn).
- Các khoản trên 90 ngày tính đến cuối tháng (quá hạn nghiêm trọng).
Để thực hiện, dùng ba hàm AND, IF, và EOMONTH với ba công thức riêng:
Nhỏ hơn 60 ngày vào cuối tháng hiện tại (chưa tới hạn thanh toán):
=IF(EOMONTH(TODAY(), 0) - A2 <= 60, C2, "")61‑90 ngày vào cuối tháng hiện tại (quá hạn):
=IF(AND(EOMONTH(TODAY(), 0) - A2 >= 61, EOMONTH(TODAY(), 0) - A2 <= 90), C2, "")Trên 90 ngày vào cuối tháng hiện tại (quá hạn nghiêm trọng):
=IF(EOMONTH(TODAY(), 0) - A2 >= 91, C2, "")Mặc dù các công thức này trông phức tạp, nhưng chúng rất dễ hiểu nếu bạn nắm rõ cách hoạt động của hàm IF và AND.
Hàm IF
- Có ba đối số: (1) kiểm tra logic trả về TRUE/FALSE; (2) giá trị trả về nếu TRUE; (3) giá trị trả về nếu FALSE.
Trong các công thức trên, kiểm tra logic là chênh lệch giữa ngày cuối tháng hiện tại (được tính bằng EOMONTH) và ngày giao (ô A2). Nếu thỏa mãn điều kiện, công thức trả về số tiền (ô C2); ngược lại trả về chuỗi rỗng.
Hàm AND
- Cho phép gộp nhiều kiểm tra logic. Nếu tất cả đều đúng, trả về TRUE; nếu có bất kỳ một điều kiện nào sai, trả về FALSE.
AND được dùng trong công thức thứ hai để đồng thời kiểm tra "≥61 ngày" và "≤90 ngày".
Những ví dụ trên hy vọng đã giúp bạn nắm vững hàm EOMONTH và khai thác tối đa sức mạnh của nó trong các bảng tính. Tiếp tục luyện tập, và ngay sau khi thành thạo EOMONTH, chúng tôi sẽ giới thiệu hàm tiếp theo để bạn mở rộng kỹ năng!
