Trong một bảng tính, ô được tô màu có thể thu hút sự chú ý của bạn đến nội dung bên trong, cảnh báo về một ngày quan trọng, mốc quan trọng hoặc một điều tương tự. Trước khi giải thích cách tô màu ô dựa trên ngày hết hạn trong Google Sheets, hãy để tôi cung cấp một số ngữ cảnh.
Tôi duy trì một tệp Google Sheets để ghi lại thu nhập của mình, các chi phí hàng ngày và thông tin quan trọng khác như ngày đến hạn của tiền bảo hiểm của mình, các ngày hết hạn của tên miền và kế hoạch lưu trữ, v.v. Mỗi danh mục có thẻ bảng riêng của mình.
Tôi sử dụng định dạng có điều kiện trong một số tab bảng để dễ dàng kiểm tra ngày hết hạn của việc đăng ký tên miền, tiền bảo hiểm, v.v., bởi vì những ngày này quan trọng đối với tôi.
Trong các tab bảng chứa dữ liệu như vậy, tôi đã áp dụng quy tắc định dạng có điều kiện để tô màu ô với hai màu khác nhau dựa trên thời hạn hết hạn.
Tôi đặt màu ô là màu Xanh lá cây nếu ngày hết hạn sau một tháng. Tuy nhiên, nếu ngày hết hạn trong một tháng, tôi đặt tô sáng màu Đỏ.
Hệ thống này rất hữu ích, giúp tiết kiệm công sức kiểm tra từng ngày một. Một cái nhìn, tôi có thể xác định liệu mình có nằm trong thời gian an toàn cho đăng ký và ngày đến hạn hay không.
Bây giờ, hãy đi sâu vào cách tô màu ô dựa trên ngày hết hạn trong Google Sheets. Bạn sẽ học cách sử dụng một quy tắc định dạng có điều kiện đơn giản nhưng hiệu quả sử dụng chức năng EDATE của Google Sheets.
Cách tô màu ô dựa trên ngày hết hạn có thể hữu ích cho bạn như thế nào?
Bạn có thể áp dụng quy tắc định dạng có điều kiện liên quan đến ngày hết hạn trong Google Sheets trong các trường hợp khác nhau. Đây là một số ví dụ:
- Xác định màu sắc của ngày hết hạn của các giấy phép đăng ký công ty hoặc tài liệu.
- Theo dõi ngày hết hạn hộ chiếu và giấy phép lái xe của bạn hoặc nhân viên của bạn.
- Tô sáng ngày đến hạn phí học phí cho con bạn.
- Quản lý ngày đến hạn thẻ tín dụng của bạn.
- Theo dõi ngày đến hạn của tiền bảo hiểm của bạn.
- Đảm bảo thanh toán vay kịp thời.
- Đối với những người viết blog quản lý nhiều blog, việc này giúp dễ dàng theo dõi các ngày hết hạn lưu trữ và tên miền sắp tới.
Có vô số tình huống mà bạn có thể sử dụng định dạng có điều kiện liên quan đến thời hạn hết hạn trong Google Sheets.
Việc thanh toán phí hoặc các khoản phí dịch vụ đúng hạn không chỉ giúp bạn tiết kiệm tiền bằng việc tránh phí trễ hạn mà còn nâng cao sự đáng tin cậy của bạn.
Mẹo để tô màu ô dựa trên ngày hết hạn trong Google Sheets
Như đã đề cập ở đầu, chức năng EDATE của Google Sheets có thể được sử dụng ở đây. Mặc dù tôi đã cung cấp một hướng dẫn chi tiết về tất cả các chức năng liên quan đến ngày trong Google Sheets, tôi sẽ giới thiệu lại chức năng EDATE ở đây.
Cú pháp và đối số:
EDATE(start_date, [months])
- Trả về một ngày là một số tháng cụ thể (tháng) trước hoặc sau một ngày được chỉ định (start_date).
Ví dụ:
Cell A1 chứa ngày 31/12/2017. Kiểm tra chức năng EDATE bên dưới và kết quả của nó.
=EDATE(A1, 1) // trả về 31/01/2018
Công thức khác:
=EDATE(A1, 2) // trả về 28/02/2018
Tương tự, chức năng này cũng có thể trả về ngày trước ngày được tham chiếu.
=EDATE(A1, -1) // trả về 30/11/2017
Bây giờ, hãy quay trở lại hướng dẫn của chúng tôi và tìm hiểu cách tô màu ô dựa trên ngày hết hạn trong Google Sheets.
Phương pháp thứ nhất: Tô màu ô dựa trên ngày hết hạn sử dụng công thức trong quy tắc đã được định sẵn
Tôi đang áp dụng định dạng có điều kiện này vào cột A trong phạm vi A1: A5. Bạn có thể áp dụng nó vào phạm vi bạn muốn.
Nếu bạn đang tìm kiếm các công thức và cách sử dụng chúng, đây là chúng:
Công thức:
=EDATE(HÔM_NAY(), 1)
Hãy xem ảnh chụp màn hình dưới đây, nơi tôi đã đặt ba quy tắc tùy chỉnh (công thức) trong menu “Định dạng” > “Định dạng có điều kiện”. Tất cả đều sử dụng cùng một công thức, nhưng ứng dụng khác nhau.
a. Ngày hết hạn trong một tháng (màu Đỏ):
- Trong bảng “Quy tắc định dạng có điều kiện”, dưới “Áp dụng vào phạm vi”, nhập A1: A5.
- Chọn “Ngày trước”, sau đó chọn “Ngày chính xác” trong “Quy tắc định dạng”.
- Nhập công thức EDATE.
- Chọn màu nền Đỏ và nhấp “Hoàn tất”.
b. Ngày hết hạn sau một tháng (màu Xanh lá cây):
- Trong “Áp dụng vào phạm vi”, nhập A1: A5 (bạn có thể nhấp “Thêm một quy tắc nữa trước”).
- Chọn “Ngày sau”, sau đó chọn “Ngày chính xác” trong “Quy tắc định dạng”.
- Nhập công thức EDATE.
- Chọn màu nền Xanh lá cây và nhấp “Hoàn tất”.
c. Ngày hết hạn chính xác trong một tháng (màu Xanh nước biển):
- Trong “Áp dụng vào phạm vi”, nhập A1: A5 (bạn có thể nhấp “Thêm một quy tắc nữa trước”).
- Chọn “Ngày là”, sau đó chọn “Ngày chính xác” trong “Quy tắc định dạng”.
- Nhập công thức EDATE.
- Chọn màu nền Xanh nước biển và nhấp “Hoàn tất”.
Nếu bất kỳ ô nào được tô màu với màu Đỏ, điều đó có nghĩa là ngày đáo hạn trong vòng một tháng, chỉ ra rằng bạn nên chú ý đến việc thanh toán đó. Nếu nó là màu Xanh lá cây, bạn có thể bỏ qua các ô như vậy.
Giải thích cho các quy tắc đã áp dụng:
Nếu ngày hôm nay là ngày 19/01/2024:
Bất kỳ nơi nào ngày nhỏ hơn ngày 19/02/2024 (chỉ ra trong vòng một tháng), công thức (quy tắc định dạng) sẽ tô màu nền cho các ô đó trong cột A (A1: A5) màu Đỏ.
Nếu bất kỳ ngày nào trong cột A là ngày 19/02/2024, ô đó sẽ được tô màu nền màu Xanh nước biển.
Nếu bất kỳ ngày nào trong cột A lớn hơn ngày 19/02/2024, công thức sẽ tô màu nền cho các ô đó trong cột A màu Xanh lá cây.
Như vậy, bạn có thể tô màu ô dựa trên ngày hết hạn trong Google Sheets.
Các quy tắc định dạng có điều kiện trên dựa trên một ngày cắt đứt là một tháng. Nếu bạn muốn đặt một ngày cắt đứt 6 tháng, chỉ cần thay đổi công thức tùy chỉnh như sau:
=EDATE(HÔM_NAY(), 6)
Do đó, việc tô màu sẽ đại diện cho ngày hết hạn trong vòng 6 tháng, chính xác vào tháng thứ 6 và hết hạn sau 6 tháng.
Phương pháp 2: Tô màu ô dựa trên ngày hết hạn bằng cách sử dụng công thức tùy chỉnh
Các quy tắc định dạng có điều kiện trên có một số hạn chế, quan trọng nhất là chúng chỉ kiểm tra các ngày trong phạm vi “Áp dụng vào”. Ví dụ, bạn không thể sử dụng chúng để so khớp một ngày trong một ô cho ngày hết hạn và tô màu một ô khác.
Ví dụ:
Tôi có ngày đăng ký Apple Music trong ô C3. Tôi muốn tô màu ô B3, chứa mô tả của đăng ký, nghĩa là “Đăng ký Apple Music”. Làm thế nào chúng ta có thể làm điều đó?
Đầu tiên, xem các công thức sau đây:
=AND(ISDATE(C3), C3 < EDATE(HÔM_NAY(), 1)) // hết hạn trong một tháng
=AND(ISDATE(C3), C3 > EDATE(HÔM_NAY(), 1)) // hết hạn sau một tháng
=AND(ISDATE(C3), C3 = EDATE(HÔM_NAY(), 1)) // hết hạn chính xác trong một tháng
Các công thức phù hợp nếu giá trị trong ô C3 là một ngày hợp lệ và cũng là ngày trong vòng một tháng, sau một tháng hoặc chính xác một tháng kể từ khi hết hạn.
Bạn cần áp dụng các công thức một cách tuần tự như trước, nhưng trong “Công thức tùy chỉnh” trong “Quy tắc định dạng” trong Định dạng có điều kiện.
“Áp dụng vào phạm vi” có thể là B3 nếu bạn chỉ có một ngày trong ô C3.
Nếu bạn có nhiều ngày đăng ký hơn trong C3:C, “Áp dụng vào phạm vi” phải là B3:B. Không có thay đổi nào trong các công thức.
Ngoài ra, và quan trọng nhất, nếu bạn muốn tô màu toàn bộ hàng trong “Áp dụng vào phạm vi”, ví dụ, B3: Z3 thay vì chỉ B3, thay thế tất cả các sự xuất hiện của C3 trong các công thức bằng $ C3.
Tài nguyên
Hướng dẫn này khám phá hai phương pháp tô màu ô dựa trên ngày hết hạn trong Google Sheets. Đây là những gợi ý khác về định dạng có điều kiện sẽ giúp bạn thêm nhiều quy tắc.
Hãy nhớ rằng áp dụng định dạng có điều kiện cho một phạm vi lớn có thể làm chậm xử lý bảng tính của bạn.
- Các quy tắc định dạng có điều kiện liên quan đến ngày trong Google Sheets.
- Tô sáng một hàng hoàn toàn trong Định dạng có điều kiện trong Google Sheets.
- Cách định dạng có điều kiện trùng lặp trên các tab bảng trong Google Sheets.
- Google Sheets – Tô sáng giá trị lớn nhất trong mỗi nhóm.
- Cách tô sáng 3 giá trị lớn nhất trong mỗi hàng trong Google Sheets.
- Tô sáng các trùng lặp phần bước trong Google Sheets.
- Cách tô sáng kết quả giá trị Vlookup trong Google Sheets.
- Tô sáng các trùng lặp hoặc khác biệt trong hai danh sách trong Google Sheets.
- Tô sáng các trùng lặp trong một cột, nhiều cột và tất cả các ô trong Google Sheets.
Hy vọng rằng hướng dẫn này đã giúp bạn hiểu cách tô màu ô dựa trên ngày hết hạn trong Google Sheets. Hãy thử áp dụng nó vào công việc của bạn và tận hưởng lợi ích của việc theo dõi ngày hết hạn một cách dễ dàng!
