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

Bạn đang xem: Công thức nhắc nhở ngày tháng trong excel

Và bây chừ chúng ta đã tận dụng con kiến ​​thức này và tạo thành các bảng tính phân biệt giữa các ngày trong tuần cùng cuối tuần, làm rất nổi bật các thời điểm dịp lễ chính thức với hiển mốc thời gian sắp tới. Nói phương pháp khác, chúng ta sẽ áp dụng định dạng có điều kiện Excel đến ngày. Nếu khách hàng có một vài kiến ​​thức cơ bạn dạng về những công thức Excel, chúng ta cũng có thể quen với một trong những hàm thời giờ như NOW, TODAY, DATE, WEEKDAY, vv Trong trả lời này, bọn họ sẽ tìm hiểu sâu hơn những hàm nhằm định dạng có điều kiện 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 tải sẵn)Công thức để gia công nổi bật ngày sau đây

Định dạng có điều kiện cho ngày tháng (các quy tắc sở hữu sẵn)

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

Để áp 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 mon trong Excel dựa vào ngày hiện tại tại:

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

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

Để định dạng bao gồm điều kiện các ô hoặc tổng thể hàng dựa vào ngày hôm nay, bạn sử 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ừ bỏ như vậy, chúng ta có thể sử dụng hàm TODAY kết phù hợp với các hàm khác nhằm xử lý các tình huống tinh vi hơn. Ví dụ: bạn có thể muốn phương pháp định dạng có điều kiện tô màu sắc cột Invoice khi Ngày ship hàng bằng hoặc lớn hơn ngày bây giờ nhưng bạn muốn định dạng trên bặt tăm khi nhập số hoá đơn.

Đối với trọng trách này, các bạn sẽ cần một cột bổ sung 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 hiện tại và không tồn tại số trong cột Hóa đơn, phương pháp sẽ trả về 1, nếu không thì số đó là 0.

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

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

Giả sử chúng ta có định kỳ trình dự án liệt kê những nhiệm vụ, ngày ban đầu và thời gian thực hiện của chúng. Những gì bạn muốn là ngày xong cho mỗi các bước đượ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 xem như lại với từng cuối tuần. Ví dụ: nếu như ngày bước đầu là 13 tháng 6 năm 2014 và số ngày làm việc (Duration) là 2, ngày xong xuôi sẽ là vào ngày 17 tháng 6 năm 2014, do 14-tháng 6 cùng 15-tháng 6 là sản phẩm công nghệ 7 và nhà Nhật .

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

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

Và cuối cùng, bạn có thể muốn làm nổi bật các mặt hàng tùy nằm trong 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 sau đây làm nổi bật ngày sắp xong 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 nổi bật tất cả những ngày về sau (nghĩa là ngày lớn hơn ngày hiện tại 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: 50+ Hình Xăm 3D Nghệ Thuật 3D Tạo Nên Những Hình Đẹp Y Như Thật

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

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

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

Làm ráng nào để làm nổi nhảy ngày mon trong một phạm vi ngày:

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

Bạn bao gồm thể chấm dứt nhiệm vụ này bằng phương pháp sử dụng công dụng TODAY (). các 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 số ví dụ bên dưới đây.

Công thức để triển khai nổi nhảy ngày tháng vừa quaHơn 30 những năm trước đây: = TODAY () – $A2> 30Từ 30 mang lại 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 bây giờ và ngẫu nhiên ngày tháng tương lai nào ko được đánh màu.

*
Công thức để triển khai nổi bật ngày trong tương lai Sẽ xẩy ra trong hơn 30 ngày tính từ lúc bây giờ: = $A2-TODAY ()> 30Trong 30 mang lại 15 ngày, gồm những: = và ($A2-TODAY ()> = 15, $A2-TODAY () Trong chưa đầy 15 ngày: = và ($A2-TODAY ()> = 1, $A2-TODAY ()

Ngày bây giờ và bất kỳ ngày tháng vừa rồi nào ko được đánh màu.

*

Làm nắm nào để tô màu những khoảng trống và khoảng thời gian:

Trong ví dụ sau cuối này, bọn họ sẽ áp dụng thêm một hàm date không giống của Excel – DATEDIF (start_date, end_date, interval). Hàm này thống kê giám sát sự khác biệt giữa nhị ngày dựa trên khoảng thời hạn quy định. Nó khác với tất cả các hàm khác mà cửa hàng chúng tôi đã đàm luận trong chỉ dẫn này theo phong cách mà nó cho phép bạn bỏ qua 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 để ý đến về nó bởi một cách khác … mang sử các 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ằng hữu của bạn. Bạn có muốn biết tất cả bao nhiêu ngày cho đến ngày sinh nhật tiếp theo? rộng nữa, còn bao nhiêu ngày nữa là đến ngày cưới và những sự khiếu nại khác nhưng bạn không muốn bỏ lỡ? dễ 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” sinh hoạt cuối của phương pháp được thực hiện để bỏ lỡ năm và chỉ còn tính chênh lệch giữa những ngày. Để có danh sách khá đầy đủ các các loại khoảng thời hạn khả dụng, hãy quan sát vào đây.

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

Và hiện nay hãy chế tác một quy tắc format có điều kiện để tô màu những khoảng trống không giống nhau với các màu không giống nhau. Vào trường thích hợp này, chúng ta nên áp dụng Excel Scales color (Conditional formatting> màu sắc Scales) rộng là tạo ra một luật lệ riêng cho từng khoảng chừng thời gian.

Hình bên dưới đây cho biết thêm kết quả trong Excel – một chuỗi màu với khoảng độ từ màu xanh lá cây lá cây đến màu đỏ thông qua màu sắc vàng.

*

Ngoài ra để vận dụng Excel vào quá trình một cách kết quả thì chúng ta còn cần sử dụng tốt các hàm, các công cố gắng khác của Excel.

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

SUMIF, SUMIFS để tính tổng theo 1 điểm kiện, các điều kiệnCOUNTIF, COUNTIFS nhằm thống kê, đếm theo một điều kiện, nhiều điều kiệnCác hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò search 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 đk với Conditional formattingThiết lập đk nhập dữ liệu với Data ValidationCách đặt Name và sử dụng Name vào 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 ko nào? cục bộ những kiến thức và kỹ năng này chúng ta đều rất có thể học được trong khóa học EX101 – Excel từ cơ bản tới chăm 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 và kỹ năng một phương pháp đầy đủ, bỏ ra tiết. Rộng nữa không hề 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, tiện lợi tra cứu vớt lại kiến thức và kỹ năng khi cần. Hiện giờ hệ thống đang xuất hiện ưu đãi khôn cùng lớn cho mình khi đăng ký tham gia khóa học. Chi tiết xem tại: socialgame.vn.Online