Công thức chung
=(date2-date1)>n
Tóm lược
Để đánh dấu những ngày "quá hạn", bạn có thể sử dụng quy tắc định dạng có điều kiện để kiểm tra xem liệu phương sai giữa hai ngày có lớn hơn một số ngày nhất định hay không. Trong ví dụ được hiển thị, ba quy tắc định dạng có điều kiện đã được áp dụng cho phạm vi D5: C12 với các công thức sau:
=(D5-C5)<3 // rule 1 (green) =(D5-C5)=10 // rule 3 (red)
Lưu ý: các quy tắc định dạng có điều kiện được đánh giá tương ứng với ô phía trên bên trái trong vùng chọn tại thời điểm tạo quy tắc, trong trường hợp này là D5.
Tính toán phương sai theo ngày
Các phương sai trong cột E được tính bằng cách trừ ngày gốc cho ngày hiện tại với công thức sau:
=D5-C5
Kết quả là sự khác biệt về ngày giữa ngày ban đầu và ngày hiện tại. Một sự khác biệt dương thể hiện một ngày trễ (tức là một "lần trượt" trong lịch trình). Một sự khác biệt âm cho biết ngày hiện tại trước thời hạn. Điều này hoạt động, vì ngày tháng trong Excel là số sê-ri.
Phương sai hiển thị trong cột E chỉ để tham khảo trong ví dụ này và không được sử dụng bởi các quy tắc định dạng có điều kiện. Tuy nhiên, nếu bạn coi cột E là cột trợ giúp, bạn có thể viết các quy tắc định dạng có điều kiện đơn giản hơn sử dụng phương sai trực tiếp.
Giải trình
Trong ví dụ này, chúng tôi muốn áp dụng ba màu khác nhau, tùy thuộc vào mức độ thay đổi của ngày gốc so với ngày hiện tại:
- Màu xanh lá cây nếu phương sai nhỏ hơn 3 ngày
- Màu vàng nếu phương sai từ 3 đến 10 ngày
- Màu đỏ nếu phương sai lớn hơn 10 ngày
Đối với mỗi quy tắc, chúng tôi tính phương sai bằng cách trừ ngày ban đầu cho ngày "hiện tại" (như đã giải thích ở trên). Sau đó, chúng tôi kiểm tra kết quả bằng một biểu thức logic. Khi một biểu thức trả về TRUE, định dạng có điều kiện sẽ được kích hoạt.
Bởi vì chúng tôi muốn có ba màu riêng biệt, mỗi màu có một kiểm tra logic, chúng tôi sẽ cần ba quy tắc định dạng có điều kiện riêng biệt. Màn hình bên dưới hiển thị cách các quy tắc đã được định cấu hình để áp dụng định dạng màu xanh lá cây, vàng và đỏ. Lưu ý rằng hai quy tắc đầu tiên đã đánh dấu "dừng nếu đúng":
Các quy tắc được đánh giá theo thứ tự được hiển thị. Quy tắc 1 kiểm tra nếu phương sai nhỏ hơn 3 ngày. Quy tắc 2 kiểm tra xem phương sai có nhỏ hơn 10 ngày hay không. Quy tắc 3 kiểm tra xem phương sai có lớn hơn hoặc bằng 10 ngày hay không. Cả quy tắc 1 và quy tắc 2 đều được bật "dừng nếu đúng". Khi một trong hai quy tắc trả về TRUE, Excel sẽ ngừng kiểm tra các quy tắc bổ sung.
Quá hạn n ngày kể từ hôm nay
Bạn có thể muốn so sánh ngày đến hạn với ngày hôm nay. Để kiểm tra xem ngày có quá hạn ít nhất n ngày kể từ hôm nay hay không, bạn có thể sử dụng công thức như sau:
=(TODAY()-date)>=n
Công thức này sẽ chỉ trả về TRUE khi một ngày cách đây ít nhất n ngày. Khi một ngày trong tương lai, sự khác biệt sẽ là một số âm, vì vậy quy tắc sẽ không bao giờ kích hoạt.
Để biết thêm thông tin về cách xây dựng tiêu chí công thức, hãy xem hơn 50 ví dụ về tiêu chí công thức.
