Olga giữ sách cho một trường tư thục. Đối với mỗi học sinh ghi danh, cô ấy có ngày nhập học và ngày bỏ học. Cô ấy cần tìm ra bao nhiêu tháng để lập hóa đơn cho mỗi học sinh. Nếu học sinh đã ở trong lớp ít nhất năm ngày, thì tháng đó sẽ được đưa vào hóa đơn. Nếu ít hơn năm ngày, chúng không được lập hóa đơn cho tháng đó. Cô ấy cũng cần phải loại trừ các ngày lễ và cuối tuần.

Tích hợp cho bất kỳ giải pháp nào cho vấn đề này sẽ là việc sử dụng hàm NETWORKDAYS. Hàm này, như được mô tả trong ExcelTips khác, tính toán số ngày làm việc thực giữa hai ngày. Nó tính đến các ngày cuối tuần và, tùy chọn, ngày lễ.

Vì vậy, giả sử bạn có ngày nhập học của học sinh ở A1 và ngày rớt ở A2, điều duy nhất bạn cần làm là thiết lập danh sách các ngày nghỉ. Bạn có thể làm điều đó bằng cách bắt đầu nhập ngày nghỉ vào một loạt ô. Nhập một ngày cho mỗi ô, sau đó chọn phạm vi. Xác định tên để tham chiếu đến phạm vi, chẳng hạn như MyHolidays.

Sau đó, bạn có thể sử dụng một công thức như sau:

=(NETWORKDAYS(A1, DATE(YEAR(A1), MONTH(A1) + 1,0), MyHolidays) >= 5)

+ (NETWORKDAYS(DATE(YEAR(A2), MONTH(A2),1), A2, MyHolidays) >= 5)

+ DATEDIF(DATE(YEAR(A1), MONTH(A1) + 1, 1), DATE(YEAR(A2), MONTH(A2), 1), "m")

Công thức khá dài và mang một số kiểm tra. Lưu ý rằng bên cạnh hàm NETWORKDAYS, nó cũng sử dụng hàm DATEDIF, được sử dụng để xác định sự khác biệt giữa hai ngày và trả về khoảng thời gian theo những cách khác nhau. Trong trường hợp này, nó được sử dụng với “m”

, có nghĩa là nó trả về khoảng thời gian dưới dạng một số tháng — chính xác là những gì Olga cần.

Phần đầu tiên của công thức (công dụng đầu tiên của hàm NETWORKDAYS) được sử dụng để xác định có bao nhiêu ngày giữa ngày nhập (trong ô A1) và cuối tháng mà ngày nhập xảy ra. Nếu giá trị này lớn hơn hoặc bằng 5 (mức cắt của Olga), thì giá trị 1 được trả về, vì giá trị này được tính là một tháng có thể lập hóa đơn.

Phần tiếp theo của công thức (lần sử dụng thứ hai của hàm NETWORKDAYS) được sử dụng để xác định xem có ít nhất năm ngày học trong tháng mà ngày giảm điểm xảy ra hay không. Nếu vậy, giá trị 1 sẽ được trả về, một lần nữa vì đây là tháng có thể lập hóa đơn.

Sau đó, hàm DATEDIF được sử dụng để trả về số tháng đầy đủ giữa tháng nhập và tháng giảm. Kết quả của bạn là số tháng sẽ được lập hóa đơn cho học sinh.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (9514) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: