Trong bài viết này, Học Excel Online sẽ phân tích và lý giải cách áp dụng những quy tắc được xây đắp trong Excel với tạo các định dạng có điều kiện của riêng bạn dựa trên những công thức.

Bạn đang xem: Điều kiện ngày tháng trong excel

Và hiện giờ chúng ta đã tận dụng loài kiến ​​thức này và tạo ra các bảng tính minh bạch giữa những ngày vào tuần với cuối tuần, làm khá nổi bật các thời điểm dịp lễ chính thức cùng hiển mốc thời gian sắp tới. Nói giải pháp khác, chúng ta sẽ vận dụng định dạng có đk Excel đến ngày. Nếu bạn có một số trong những kiến ​​thức cơ phiên bản về các công thức Excel, chúng ta có thể quen với một trong những hàm thời giờ như NOW, TODAY, DATE, WEEKDAY, vv Trong lý giải này, chúng ta sẽ tò mò sâu hơn các hàm nhằm định dạng có đk ngày theo cách bạn muốn.


Xem nhanh


Định dạng có đk cho tháng ngày (các quy tắc thiết lập sẵn)Công thức để gia công nổi nhảy ngày sau đây

Định dạng có đk cho tháng ngày (các quy tắc tải sẵn)

Microsoft Excel hỗ trợ 10 tùy chọn để định dạng những ô được chọn dựa trên ngày hiện tại.

Để vận dụng định dạng, bạn chỉ cần vào tab Home> Conditional Formatting>Highlight Cell Rules và chọn A Date Occurring...

*
Định dạng có điều kiện ngày tháng trong Excel dựa vào ngày hiện nay tại:

Như bạn có thể biết Microsoft Excel hỗ trợ hàm TODAY () cho những phép tính khác nhau dựa trên ngày hiện tại tại. Đây chỉ là 1 trong những vài lấy ví dụ về cách chúng ta có thể sử dụng nó nhằm định dạng có điều kiện trong Excel.

Ví dụ 1. Làm nổi bật ngày bằng, to hơn hoặc nhỏ tuổi hơn ngày hôm nay

Để định dạng gồm điều kiện những ô hoặc tổng thể hàng dựa trên ngày hôm nay, bạn áp dụng hàm TODAY như sau:

Bằng với ngày hôm nay: = $ B2 = TODAY ()

Lớn rộng ngày hôm nay: = $ B2> TODAY ()

Nhỏ rộng ngày hôm nay: = $ B2 Ví dụ 2. Định dạng có đk ngày dựa trên nhiều điều kiện

Tương tự như vậy, chúng ta cũng có thể sử dụng hàm TODAY kết hợp với các hàm khác nhằm xử lý những tình huống tinh vi hơn. Ví dụ: bạn có thể muốn phương pháp định dạng có đk tô màu sắc cột Invoice khi Ngày ship hàng bằng hoặc to hơn ngày lúc này nhưng bạn muốn định dạng trên bặt tăm khi nhập số hoá đơn.

Đối với nhiệm vụ này, bạn sẽ cần một cột bổ sung cập nhật với bí quyết sau (trong kia E là cột Deliveryvà cột F là Invoice:

 = IF (E2> = TODAY (), IF (F2 = “”, 1, 0), 0)

Nếu ngày ship hàng lớn hơn hoặc bằng ngày lúc này và không tồn tại số vào cột Hóa đơn, công thức sẽ trả về 1, nếu như không thì số chính là 0.

Sau đó chúng ta tạo một quy tắc định dạng điều kiện đơn giản cho cột Invoice có bí quyết = $G2 = 1 trong những số ấy G là cột bổ sung cập nhật của bạn. Tất nhiên, các bạn sẽ có thể ẩn cột này sau.

*
Ví dụ 3. Đánh dấu hầu như ngày sắp tới đây và vẫn qua

Giả sử bạn có lịch trình dự án công trình liệt kê các nhiệm vụ, ngày ban đầu và thời hạn thực hiện tại của chúng. Hầu hết gì bạn có nhu cầu là ngày dứt cho mỗi quá trình được giám sát và đo lường tự động. Một thử thách nữa là định dạng này được coi như lại với mỗi cuối tuần. Ví dụ: nếu ngày bước đầu là 13 tháng 6 năm 2014 và số ngày thao tác làm việc (Duration) là 2, ngày dứt sẽ là vào trong ngày 17 mon 6 năm 2014, vày 14-tháng 6 cùng 15-tháng 6 là sản phẩm công nghệ 7 và công ty Nhật .

Để làm điều này, shop chúng tôi sẽ áp dụng WORKDAY.INTL (start_date, days, , ), đúng mực hơn = WORKDAY.INTL(B2, C2,1).

*
Trong công thức, họ nhập 1 làm cho tham số máy 3 do nó đã cho thấy ngày lắp thêm 7 và công ty nhật là ngày lễ. Bạn cũng có thể sử dụng một cực hiếm khác giả dụ ngày cuối tuần của người tiêu dùng khác, mang sử, máy Sáu cùng Thứ Bảy Theo tùy chọn, chúng ta có thể sử dụng tham số đồ vật 4 , là 1 trong bộ ngày tháng (phạm vi ô) nên được loại bỏ khỏi lịch làm cho việc.

Và cuối cùng, bạn cũng có thể muốn làm nổi bật các sản phẩm tùy thuộc vào khoảng thời hạn đến hạn. Ví dụ: các quy tắc định hình có đk dựa trên nhị công thức dưới đây làm khá nổi bật ngày sắp ngừng và ngày hoàn thành gần đây, tương ứng:

= VÀ ($ D2-TODAY ()> = 0, $ D2-TODAY () = 0, TODAY () – $ D2 TODAY () – làm rất nổi bật tất cả các ngày về sau (nghĩa là ngày to hơn ngày hiện nay tại). Chúng ta cũng có thể sử dụng nó để làm nổi bật các sự kiện sắp tới.

Xem thêm: Xông Lá Xông Trị Cảm Sốt - 7 Loại Lá Xông Cảm Cúm Có Sẵn Trong Vườn Nhà Bạn

Tất nhiên, có các biến thể vô hạn của các công thức trên, tùy nằm trong vào nhiệm vụ rõ ràng của bạn. Ví dụ:

= $ D2-TODAY ()> = 6 – làm rất nổi bật ngày xẩy ra trong 6 ngày trở lên.

= $ D2 = TODAY () – 14 – nhấn mạnh những ngày xảy ra đúng đắn 2 tuần trước.

Làm cố gắng nào để gia công nổi bật ngày mon trong một phạm vi ngày:

Nếu chúng ta có một danh sách dài những ngày trong bảng tính của mình, chúng ta có thể muốn làm trông rất nổi bật các ô hoặc hàng bên trong phạm vi ngày độc nhất định, tức là nhấn mạnh toàn bộ các ngày nằm trong lòng hai ngày độc nhất vô nhị định.

Bạn gồm thể kết thúc nhiệm vụ này bằng cách sử dụng tính năng TODAY (). bạn sẽ chỉ rất cần phải xây dựng một cách làm tinh vi hơn một chút như trong các ví dụ bên dưới đây.

Công thức để làm nổi bật ngày tháng vừa quaHơn 30 ngày trước đây: = TODAY () – $A2> 30Từ 30 mang đến 15 ngày trước, gồm những: = & (TODAY () – $A2> = 15, TODAY () – $A2 chưa tới 15 ngày trước: = and (TODAY () – $A2> = 1, TODAY () – $A2

Ngày lúc này và ngẫu nhiên ngày tháng tương lai nào ko được sơn màu.

*
Công thức để triển khai nổi nhảy ngày sau này Sẽ xảy ra trong hơn 30 ngày kể từ bây giờ: = $A2-TODAY ()> 30Trong 30 cho 15 ngày, gồm những: = & ($A2-TODAY ()> = 15, $A2-TODAY () Trong chưa đầy 15 ngày: = & ($A2-TODAY ()> = 1, $A2-TODAY ()

Ngày lúc này và bất kỳ ngày tháng vừa qua nào ko được đánh màu.

*

Làm ráng nào để tô màu các khoảng trống và khoảng tầm thời gian:

Trong ví dụ sau cùng này, họ sẽ thực hiện thêm một hàm date không giống của Excel – DATEDIF (start_date, end_date, interval). Hàm này đo lường và thống kê sự biệt lập giữa hai ngày dựa trên khoảng thời gian quy định. Nó khác với tất cả các hàm không giống mà shop chúng tôi đã thảo luận trong lí giải này theo cách mà nó chất nhận được bạn bỏ qua mất tháng hoặc năm và chỉ còn tính sự khác hoàn toàn giữa các ngày hoặc tháng, tùy thuộc vào bạn chọn.

Hãy quan tâm đến về nó bởi một phương pháp khác … trả sử bạn có một danh sách những ngày sinh của những thành viên mái ấm gia đình và bạn bè của bạn. Bạn có muốn biết bao gồm bao nhiêu ngày cho tới ngày sinh nhật tiếp theo? hơn nữa, còn từng nào ngày nữa là mang lại ngày cưới và phần nhiều sự khiếu nại khác nhưng bạn không muốn bỏ lỡ? dễ dàng dàng!

Đây là công thức bạn cần (trong đó A là cột Ngày của bạn):

= DATEDIF (TODAY (), DATE ((NĂM (TODAY ()) + 1), MONTH ($A2), DAY ($A2)), “yd”)

Loại khoảng thời hạn “yd” ở cuối của cách làm được sử dụng để làm lơ năm còn chỉ tính chênh lệch giữa những ngày. Để bao gồm danh sách đầy đủ các các loại khoảng thời hạn khả dụng, hãy chú ý vào đây.

Mẹo. Nếu bạn quên mất hoặc đặt sai tên công thức phức hợp đó, chúng ta cũng có thể sử dụng công thức phức hợp này: = 365-DATEDIF ($A2, TODAY (), “yd”). Nó tạo thành kết quả đúng chuẩn như nhau, chỉ cần nhớ thay thế sửa chữa 365 thành 366 trong năm nhuận:)

Và hiện thời hãy sinh sản một quy tắc định hình có đk để đánh màu những khoảng trống không giống nhau với các màu khác nhau. Trong trường phù hợp này, bạn nên áp dụng Excel Scales color (Conditional formatting> color Scales) rộng là tạo nên một phép tắc riêng mang đến từng khoảng chừng thời gian.

Hình dưới đây cho thấy kết trái trong Excel – một chuỗi màu với tầm độ từ màu xanh lá cây lá cây đến màu đỏ thông qua color vàng.

*

Ngoài ra để ứng dụng Excel vào quá trình một cách tác dụng thì chúng ta còn đề xuất sử dụng xuất sắc các hàm, những công cố khác của Excel.

Một số hàm cơ bạn dạng thường gặp mặt như:

SUMIF, SUMIFS nhằm tính tổng theo 1 điều kiện, những điều kiệnCOUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, những điều kiệnCác hàm xử lý tài liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò kiếm tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ thường được sử dụng như:

Định dạng theo điều kiện với Conditional formattingThiết lập đk nhập dữ liệu với Data ValidationCách để Name và áp dụng Name trong công thứcLập báo cáo với Pivot Table…

Rất nhiều kỹ năng và kiến thức phải không nào? toàn cục những kỹ năng và kiến thức này chúng ta đều rất có thể học được trong khóa học EX101 – Excel trường đoản cú cơ bạn dạng tới siêng gia của học Excel Online. Đây là khóa học giúp cho bạn hệ thống kiến thức một cách đầy đủ, đưa ra tiết. Hơn nữa không còn có số lượng giới hạn về thời gian học tập nên bạn có thể thoải mái học bất kể lúc nào, thuận lợi tra cứu vãn lại kỹ năng và kiến thức khi cần. Hiện thời hệ thống đang xuất hiện ưu đãi khôn xiết lớn cho bạn khi đăng ký tham gia khóa học. Cụ thể xem tại: tsttmobile.vn.Online