Cách định Dạng Có điều Kiện Ngày Tháng Và Thời Gian Trong Excel

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

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

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

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

1. Để á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...

2. Chọn một trong các tùy chọn ngày từ danh sách thả xuống ở phần bên tay trái của cửa sổ, từ tháng trước đến tháng sau.
3. Cuối cùng, chọn một trong các định dạng có sẵn hoặc thiết lập định dạng của bạn bằng cách chọn các tuỳ chọn khác nhau trên tab Font, Border và Fill. Nếu bảng tính cơ bản không đủ, bạn có thể nhấp vào nút More colors…

4. Nhấp vào OK và tận hưởng kết quả! 🙂

Tuy nhiên, cách nhanh và trực tiếp này có hai hạn chế đáng kể – 1) chỉ hoạt động với các ô đã chọn và 2) định dạng có điều kiện luôn được áp dụng dựa trên ngày hiện tại.

Công thức định dạng có điều kiện của Excel cho ngày tháng

Nếu bạn muốn làm nổi bật các ô hoặc toàn bộ hàng dựa trên một ngày trong một ô khác, hoặc tạo các quy tắc cho các khoảng thời gian lớn hơn (tức là hơn một tháng kể từ ngày hiện tại), bạn sẽ phải tự tạo ra quy tắc định dạng có điều kiện của mình dựa trên một công thức . Dưới đây bạn sẽ tìm thấy một vài ví dụ về định dạng có điều kiện của Excel cho ngày tháng.

Xem thêm: Cách dùng hàm IF trong Exel: Công thức cho số, ký tự, dữ liệu và ô trống

Cách làm nổi bật ngày cuối tuần trong Excel:

Thật đáng tiếc, Microsoft Excel không có lịch trình tích hợp tương tự như Outlook. Chúng ta hãy xem làm thế nào để có thể tạo ra lịch tự động của riêng mình.

Khi thiết kế lịch, bạn có thể sử dụng hàm = DATE (năm, tháng, ngày) để hiển thị những ngày trong tuần. Đơn giản chỉ cần nhập năm và số tháng vào nơi nào đó trong bảng tính của bạn và tham chiếu những ô đó trong công thức. Tất nhiên, bạn có thể nhập các con số trực tiếp vào công thức, nhưng đây không phải là cách làm hiệu quả vì bạn phải điều chỉnh công thức cho mỗi tháng.

Hình bên dưới minh họa hàm DATE. Tôi đã sử dụng công thức = DATE ($B$2, $B$1, B$4) được sao chép qua hàng thứ 5.

Mẹo. Nếu bạn chỉ muốn hiển thị những ngày trong tuần như bạn thấy trong hình trên, hãy chọn các ô có công thức (dòng 5 trong trường hợp của chúng ta), nhấp chuột phải và chọn Format Cells …> Number> Custom. Từ danh sách tùy chọn dưới Type, chọn dddd hoặc ddd để hiển thị tên đầy đủ hoặc tên viết tắt, tương ứng. Lịch Excel của bạn đã gần xong, và bạn chỉ cần thay đổi màu sắc cuối tuần. Đương nhiên, bạn sẽ không phải làm thủ công. Chúng ta sẽ tự động định dạng các ngày cuối tuần bằng cách tạo quy tắc định dạng có điều kiện dựa trên hàm WEEKDAY.

1. Bạn bắt đầu bằng cách chọn lịch Excel của mình nơi bạn muốn đánh dấu những ngày cuối tuần. Trong trường hợp này, đó là vùng $B$4: $AE$10. Hãy chắc chắn bắt đầu lựa chọn với cột ngày đầu tiên – Colum B trong ví dụ này.
2. Trên tab Home, nhấp vào Conditional Formatting menu> New Rule.
3. Tạo ra một quy tắc định dạng mới dựa trên một công thức, như đã được giải thích trong Làm thế nào để tạo các quy tắc định dạng có điều kiện của Excel với một công thức.
4. Trong hộp “Format values where this formula is true”, hãy nhập công thức WEEKDAY sau đây để xác định ô nào là Thứ bảy và Chủ nhật: = WEEKDAY (B$5,2)> 5.
5. Nhấp vào nút Format … và thiết lập định dạng tùy chỉnh của bạn bằng cách chuyển giữa các tab Font, Border và Fill và các tùy chọn khác. Khi hoàn tất, nhấp vào nút OK để xem lại quy tắc.

Bây giờ, hãy để tôi giải thích ngắn gọn công thức WEEKDAY (serial_number, [return_type]) để bạn có thể nhanh chóng điều chỉnh nó cho bảng tính của riêng bạn.

• serial_number đại diện cho ngày mà bạn đang tìm kiếm. Bạn nhập một tham chiếu đến ô đầu tiên của bạn với một ngày, B$5 trong trường hợp của chúng tôi.
• [return_type] xác định loại tuần (dấu ngoặc vuông ngụ ý nó là tùy chọn). Bạn nhập 2 là loại trả về cho một tuần bắt đầu từ Thứ Hai (1) đến Chủ Nhật (7).
• Cuối cùng, bạn viết> 5 để chỉ các ngày thứ bảy (6) và chủ nhật (7).

Hình dưới đây cho thấy kết quả trong Excel 2013 – những ngày cuối tuần được đánh dấu bằng màu đỏ.

Mẹo:

• Nếu công ty bạn có ngày nghỉ không theo chuẩn, ví dụ: Thứ Sáu và Thứ Bảy, sau đó bạn cần tinh chỉnh công thức để bắt đầu đếm từ Chủ Nhật (1) và làm nổi bật ngày 6 (Thứ 6) và 7 (Thứ 7) – WEEKDAY(B$5,1)> 5.
• Nếu bạn đang tạo lịch ngang, hãy sử dụng cột tương đối (không có $) và dòng tuyệt đối (với $) trong tham chiếu ô vì bạn nên khóa tham chiếu của hàng – trong ví dụ trên là hàng 5, do đó Chúng tôi đã nhập vào B$5. Nhưng nếu bạn đang thiết kế lịch theo hướng dọc, bạn nên làm ngược lại, tức là sử dụng một cột tuyệt đối và hàng tương đối, ví dụ: $B5 như bạn thấy trong hình dưới đây:

Làm thế nào để làm nổi bật ngày nghỉ trong Excel:

Để cải thiện lịch của bạn hơn nữa, bạn cũng có thể đánh dấu các ngày lễ. Để làm điều đó, bạn sẽ cần liệt kê các ngày lễ bạn muốn làm nổi bật trong cùng một bảng tính hoặc một số bảng tính khác.

Ví dụ: tôi đã thêm các ngày lễ sau vào cột A ($A$14: $A$17).

Một lần nữa, bạn mở Conditional Formatting> New Rule. Trong trường hợp nghỉ lễ, bạn sẽ sử dụng hàm MATCH hoặc COUNTIF:

• = COUNTIF ($A$14: $A$17, B$5)> 0
• = MATCH (B$5, $A$14: $A$17,0)

Chú thích. Nếu bạn đã chọn màu khác cho ngày nghỉ, bạn cần di chuyển danh sách ngày nghỉ lễ lên đầu thông qua Conditional Formatting> Manage Rules…

Hình dưới đây cho thấy kết quả trong Excel 2013:

Định dạng có điều kiện một ô khi giá trị ô được thay đổi thành một ngày:

Không phải là một vấn đề lớn khi định dạng có điều kiện một ô khi một ngày được thêm vào ô đó hoặc bất kỳ ô nào khác trong cùng hàng miễn là không có loại giá trị khác. Trong trường hợp này, bạn chỉ cần sử dụng một công thức để làm nổi bật khoảng trống, như được mô tả trong các công thức có điều kiện của Excel đối với các khoảng trống và không trống. Nhưng nếu những ô đó có giá trị, ví dụ: văn bản, và bạn muốn thay đổi màu nền khi văn bản được thay đổi thành một ngày?

Nhiệm vụ  nghe có vẻ phức tạp, nhưng giải pháp rất đơn giản.

1. Trước hết, bạn cần phải xác định mã định dạng của ngày. Đây chỉ là một vài ví dụ:

• D1: dd-mmm-yy hoặc d-mmm-yy
• D2: dd-mmm hoặc d-mmm
• D3: mmm-yy
• D4: mm / dd / yy hoặc m / d / yy hoặc m / d / yy h: mm
• Bạn có thể tìm thấy danh sách đầy đủ mã số ngày trong bài viết này.

2. Chọn cột nơi bạn muốn thay đổi màu của các ô hoặc toàn bộ bảng trong trường hợp bạn muốn đánh dấu các hàng.
3. Và bây giờ tạo một quy tắc định dạng có điều kiện bằng cách sử dụng công thức tương tự như sau: = CELL (“format”, $ A2) = “D1”. Trong công thức, A là cột có ngày tháng và D1 là định dạng ngày tháng.

Nếu bảng của bạn có ngày ở 2 hoặc nhiều định dạng, sau đó sử dụng hàm OR, ví dụ: =OR(cell(“format”, $A2)=”D1″, cell(“format”,$A2)=”D2″, cell(“format”, $A2)=”D3″)

Hình bên dưới minh họa kết quả của quy tắc định dạng có điều kiện cho ngày tháng.

Làm thế nào để làm nổi bật các hàng dựa trên một ngày nhất định trong một cột nhất định:

Giả sử bạn có một bảng tính Excel lớn có chứa hai cột ngày tháng (B và C). Bạn muốn làm nổi bật mỗi hàng có một ngày nhất định, ví dụ 13-May-14, trong cột C.

Để áp dụng định dạng có điều kiện cho một ngày nào đó, trước tiên bạn cần phải tìm giá trị số. Như bạn có thể biết, Microsoft Excel lưu trữ các ngày như là số thứ tự, bắt đầu từ ngày 1 tháng 1 năm 1900. Vì vậy, 1 tháng 1 năm 1900 được lưu trữ là 1, 2 tháng 1 năm 1900 được lưu trữ là 2 … và 13-May-14 là 41772.

Để tìm số ngày tháng, nhấp chuột phải vào ô, chọn Format Cells> Number và chọn định dạng General. Ghi lại số bạn thấy và nhấp Cancel bởi vì bạn không thực sự muốn thay đổi định dạng ngày.

Đó thực sự là phần chính của công việc và bây giờ bạn chỉ cần tạo ra một quy tắc định dạng có điều kiện cho toàn bộ bảng với công thức rất đơn giản này: = $C2 = 41772. Công thức hàm ý rằng bảng của bạn có tiêu đề và hàng 2 là hàng đầu tiên có dữ liệu.

Cách khác là sử dụng hàm DATEVALUE để chuyển đổi ngày thành định dạng số, ví dụ: = $C2 = DATEVALUE (“5/13/2014”)

Cho dù công thức bạn sử dụng, nó sẽ có kết quả như nhau:

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

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

Xem thêm: Tài liệu excel cơ bản hay nhất một thời đại

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 có điều kiện các ô hoặc toàn bộ hàng dựa trên 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 hơn ngày hôm nay: = $ B2> TODAY ()

Nhỏ hơn ngày hôm nay: = $ B2 <TODAY ()

Hình bên dưới minh họa các quy tắc trên.

Ví dụ 2. Định dạng có điều kiện ngày dựa trên nhiều điều kiện

Tương tự như vậy, bạn có thể sử dụng hàm TODAY kết hợp với các hàm khác để xử lý các tình huống phức tạp hơn. Ví dụ: bạn có thể muốn công thức định dạng có điều kiện tô màu cột Invoice khi Ngày Giao hàng bằng hoặc lớn hơn ngày hôm nay nhưng bạn muốn định dạng trên biến mất khi nhập số hoá đơn.

Xem thêm: Hàm IF và cách sử dụng hàm IF trong Excel

Đối với nhiệm vụ này, bạn sẽ cần một cột bổ sung với công thức sau (trong đó E là cột Deliveryvà cột F là Invoice:

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

Nếu ngày giao hàng lớn hơn hoặc bằng ngày hiện tại và không có số trong cột Hóa đơn, công thức sẽ trả về 1, nếu không thì số đó là 0.

Sau đó bạn tạo một quy tắc định dạng điều kiện đơn giản cho cột Invoice có công thức = $G2 = 1 trong đó 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 những ngày sắp tới và đã qua

Giả sử bạn có lịch trình dự án liệt kê các nhiệm vụ, ngày bắt đầu và thời gian thực hiện của chúng. Những gì bạn muốn là ngày kết thúc cho mỗi công việc được tính toán tự động. Một thách thức nữa là định dạng này được xem lại với mỗi cuối tuần. Ví dụ: nếu ngày bắt đầu là 13 tháng 6 năm 2014 và số ngày làm việc (Duration) là 2, ngày kết thúc sẽ là vào ngày 17 tháng 6 năm 2014, vì 14-tháng 6 và 15-tháng 6 là Thứ 7 và Chủ Nhật .

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

Trong công thức, chúng ta nhập 1 làm tham số thứ 3 vì nó chỉ ra ngày thứ 7 và chủ nhật là ngày lễ. Bạn có thể sử dụng một giá trị khác nếu ngày cuối tuần của bạn khác, giả sử, Thứ Sáu và Thứ Bảy Theo tùy chọn, bạn cũng có thể sử dụng tham số thứ 4 [ngày lễ], là một bộ ngày tháng (phạm vi ô) cần được loại trừ khỏi lịch làm việc.

Và cuối cùng, bạn có thể muốn làm nổi bật các hàng tùy thuộc vào khoảng thời gian đến hạn. Ví dụ: các quy tắc định dạng có điều kiện dựa trên hai công thức sau đây làm nổi bật ngày sắp kết thúc và ngày kết thúc gần đây, tương ứng:

= VÀ ($ D2-TODAY ()> = 0, $ D2-TODAY () <= 7) – đánh dấu tất cả các hàng có End date (cột D) trong 7 ngày tiếp theo. Công thức này thực sự hữu ích khi theo dõi ngày hết hạn sắp tới hoặc thanh toán. = VÀ (TODAY () – $ D2> = 0, TODAY () – $ D2 <= 7) – đánh dấu tất cả các hàng trong đó End Date (cột D) là trong 7 ngày qua. Bạn có thể sử dụng công thức này để theo dõi các khoản thanh toán quá hạn và những chậm trễ khác.

Dưới đây là một vài ví dụ về công thức có thể được áp dụng cho bảng ở trên:

= $ D2 <TODAY () – làm nổi bật tất cả các ngày đã qua (tức là ngày ít hơn ngày hiện tại). Có thể được sử dụng để định dạng các đăng ký hết hạn, thanh toán quá hạn vv

= $ D2> TODAY () – làm nổi bật tất cả các ngày trong tương lai (nghĩa là ngày lớn hơn ngày hiện tại). Bạn có thể sử dụng nó để làm nổi bật các sự kiện sắp tới.

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 thuộc vào nhiệm vụ cụ thể của bạn. Ví dụ:

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

= $ D2 = TODAY () – 14 – nhấn mạnh các ngày xảy ra chính xác 2 tuần trước.

Làm thế nào để làm nổi bật ngày tháng trong một phạm vi ngày:

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

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

• Công thức để làm nổi bật ngày tháng vừa qua
• Hơn 30 ngày trước đây: = TODAY () – $A2> 30
• Từ 30 đến 15 ngày trước, bao gồm: = AND (TODAY () – $A2> = 15, TODAY () – $A2 <= 30)
• Chưa đến 15 ngày trước: = AND (TODAY () – $A2> = 1, TODAY () – $A2 <15)

Ngày hiện tại và bất kỳ ngày tháng tương lai nào không được tô màu.

Công thức để làm nổi bật ngày trong tương lai

• Sẽ xảy ra trong hơn 30 ngày kể từ bây giờ: = $A2-TODAY ()> 30
• Trong 30 đến 15 ngày, bao gồm: = AND ($A2-TODAY ()> = 15, $A2-TODAY () <= 30)
• Trong chưa đầy 15 ngày: = AND ($A2-TODAY ()> = 1, $A2-TODAY () <15)

Ngày hiện tại và bất kỳ ngày tháng vừa qua nào không được tô màu.

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

Trong ví dụ cuối cùng này, chúng ta sẽ sử dụng thêm một hàm date khác của Excel – DATEDIF (start_date, end_date, interval). Hàm này tính toán sự khác biệt 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ác mà chúng tôi đã thảo luận trong hướng dẫn này theo cách mà nó cho phép bạn bỏ qua tháng hoặc năm và chỉ tính sự khác biệt giữa các ngày hoặc tháng, tùy theo bạn chọn.

Hãy suy nghĩ về nó bằng một cách khác … Giả sử bạn có một danh sách các ngày sinh của các thành viên gia đình và bạn bè của bạn. Bạn có muốn biết có bao nhiêu ngày cho đến ngày sinh nhật tiếp theo? Hơn nữa, còn bao nhiêu ngày nữa là đến ngày cưới và những sự kiện khác mà 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 gian “yd” ở cuối của công thức được sử dụng để bỏ qua năm và chỉ tính chênh lệch giữa các ngày. Để có danh sách đầy đủ các loại khoảng thời gian khả dụng, hãy nhìn 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 tạp đó, bạn có thể sử dụng công thức phức tạp này: = 365-DATEDIF ($A2, TODAY (), “yd”). Nó tạo ra kết quả chính xác như nhau, chỉ cần nhớ thay thế 365 thành 366 trong năm nhuận:)

Và bây giờ hãy tạo một quy tắc định dạng có điều kiện để tô màu các khoảng trống khác nhau với các màu khác nhau. Trong trường hợp này, bạn nên sử dụng Excel Scales Color (Conditional formatting> Color Scales) hơn là tạo ra một quy tắc riêng cho từng khoảng thời gian.

Xem thêm: Khóa học VBA tại Hà Nội

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

Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online.

Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel.

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

• SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
• COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
• Cá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ò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay sử dụng như:

• Định dạng theo điều kiện với Conditional formatting
• Thiết lập điều kiện nhập dữ liệu với Data Validation
• Cách đặt Name và sử dụng Name trong công thức
• Lập báo cáo với Pivot Table…

Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có 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 cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online