如何在 Excel 中設定日期條件格式

如何在 Excel 中設定日期條件格式

Excel 中的條件格式設定 可以被認為是一種有價值的實用程序,它可以根據預定義的規則或條件應用特定的格式設置,從而幫助直觀地增強電子表格中的數據分析和演示。這篇文章將分享如何在 Excel 中設定日期條件格式。我們還提供了詳細的範例,您可以在閱讀指南時嘗試。

如何在 Excel 中設定日期條件格式

如何在 Excel 中設定日期條件格式

條件格式不僅有助於使用顏色代碼來突出顯示單元格,而且還可以以視覺上有意義且有吸引力的方式設定由日期組成的行、列和單元格的格式。

  1. 預定義
  2. 使用者自訂
  3. 建立自訂格式規則
  4. 在 Excel 中突出顯示假期
  5. 基於多個條件的日期的條件格式
  6. 突出顯示即將到來的日期和延誤

請務必仔細檢查 Excel 中的詳細資訊。它提供了許多選項,其中一些選項可能超出了本指南的範圍。

1]使用日期預定義條件格式化日期規則

在處理包含日期相關資訊的資料集時,使用日期進行條件格式化非常有用。 Excel 提供了近十種不同的選項來根據日期設定儲存格格式,如下所示:

  • 若要將日期設定為資料類型的儲存格格式,請按一下首頁 >條件格式>反白顯示單元格規則>發生日期
條件格式日期選單
  • 可以從下一個視窗的下拉式選單中選擇條件格式選項。第一個下拉清單讓我們選擇日期範圍,格式選項可以從第二個下拉清單中選擇。
條件格式化日期標準
  • 點選確定套用所選的格式設定規則。

2]使用者定義的條件格式化日期規則與日期

除了一組預先定義的規則外,Excel 還提供了根據使用者需求自訂這些規則的選項。設定自訂的方法有多種,如下所示:

使用自訂格式

  1. 依照上述步驟開啟發生日期格式視窗。
  2. 選擇日期範圍後,從格式選項下拉選單中選擇自訂格式
  3. 設定儲存格格式視窗將會彈出,我們可以在其中選擇儲存格的顏色、邊框、字型和效果選擇,然後按一下「確定」以應用變更。
條件格式日期自訂

建立新規則

此方法涉及為條件格式建立一個全新的規則,與上述方法不同,我們可以從一些預先定義的規則中進行選擇並僅建立自訂格式。建立新規則涉及:

  • 選擇要套用格式的儲存格。
  • 首頁標籤上,點選條件格式>新規則,這將開啟新格式規則視窗。
條件格式新規則
  • 在上述視窗中,我們可以從可用的規則類型中進行選擇,然後編輯規則描述以設定條件條件的標準格式化。
  • 完成後,可以點選視窗右下角的格式按鈕來選擇所述規則的格式。一個>
  • 在開啟的設定儲存格格式視窗中,我們可以根據需要選擇字型、邊框、儲存格顏色、效果等選擇並在此視窗中按一下「確定」一次,然後出現用於套用變更的新格式規則視窗。
條件格式新規則設定

3]如何建立帶有日期的自訂格式規則

在上面的範例中,我們導覽了根據已提供的條件建立條件格式新規則的步驟。 Excel 也允許在使用者定義的條件上套用上述格式規則。

例如,假設我們需要突出顯示日期早於 30 天或 60 天的儲存格。在這種情況下,也可以透過套用 Excel 中提供的日期公式和函數來實現相同的效果,如下所示:

A] 反白顯示目前和較舊的日期

在「條件格式」選項中的選擇新規則下,選擇使用公式來決定要格式化的儲存格,輸入:

=$H4=TODAY() 突顯目前日期

=$H4>TODAY() 突顯未來幾天

=$H4突出顯示前幾天

=$H4<=(TODAY()-30) 反白 30 天以上的日期。

=$H4<=(TODAY()-60) 反白 60 天以上的日期。

基於日期公式的條件格式

以上公式檢查目前日期的選取日期範圍,找到符合項目後,將背景顏色變更為從「格式」選項中選取的紫色。 $H 一起使用表示該列將保持不變,但是行是可變的。

  • 提及規則說明後,可以透過點選「格式」按鈕來選擇字型、顏色和效果。
  • 在“設定儲存格格式”視窗中按“確定”,然後在“新規則”視窗中按“確定”,以使更改生效。< /span>

B] 基於使用者定義的日期範圍反白顯示

可以應用類似的方法來格式化使用者指定的日期範圍。例如,如果我們需要突出顯示超過 30 天但小於 45 天的日期,則可以將 TODAY() 函數與 AND 等邏輯運算符結合使用來指定涉及兩個條件的條件:

  • 選擇需要套用格式的儲存格並開啟「新規則」視窗。
  • 編輯規則說明欄位中,輸入下列公式,

=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))

以上公式使用 AND 運算子以黃色突出顯示超過 30 天但少於 45 天的日期整個選擇,從單元格號開始。 H2。

條件格式日期範圍標準公式

C] 突出週末

在同一範圍內,使用 WEEKDAY() 公式,也可以突出顯示週末的日期:

  • 選擇需要套用格式的儲存格並開啟「新規則」視窗。
  • 編輯規則說明欄位中,輸入下列公式,

=WEEKDAY($H2, 2)>5

Weekday 公式的語法可以表示為 WEEKDAY(serial_number, [return_type]),其中serial_number 代表單元格編號我們嘗試檢查的日期(本例為SA1)。

return_types 參數表示從星期一 (1) 到星期日 (7) 的一週的類型,考慮一週的開始日期為星期一(以1 表示),一週的最後一天為星期日(以7 表示)。

但是,return_types 在公式中是可選的,並在第三個大括號中表示。 >5 用於反白指定返回類型的星期六(6) 和星期日(7)。

條件格式週末突出顯示規則

D] 根據特定日期突出顯示行

在處理需要突出顯示特定日期的大量資料時,此選項非常方便。透過確定日期的數值或使用 DATEVALUE() 函數也可以實現相同的目的。由於 Excel 將日期永久儲存為連續的序號,因此必須先確定數值才能對特定日期套用條件格式。要找出特定日期的數值,

  • 右鍵點選包含日期的儲存格,點選設定儲存格格式 > 數字,,然後選擇一般。儲存格上會顯示 5 位數字值,您可以將其記錄下來以供將來參考。
Excel 中的日期數字
  • 記下數字後,按一下“取消”,因為我們不需要更改儲存格的日期格式。
  • 點選條件格式>新規則> 選擇新規則> 使用公式決定要設定格式的儲存格並輸入下列公式,

=$G5=45249

條件格式特定日期選擇值
  • 使用DATEVALUE()函數,也可以透過輸入下面提到的公式來完成相同的操作,

=$G5=DATEVALUE("11/19/2023")

C 旁邊使用的 $ 表示列將保持不變,但行將可變。

條件格式特定日期選擇公式

4]在 Excel 中突出顯示假期

由於假期可能​​會根據人口統計而有所不同,因此 Excel 沒有任何內建函數來突出顯示公共假期。因此,為了識別假期,我們需要將日期記錄在不同的欄位上,並套用 countif 公式來檢查假期日期的出現在資料表中。

例如,我們在D 列中提到了一些日期,在G 列中提到了假期列表。透過條件格式,我們可以透過將D 列中提到的假期與G 列中提到的假期日期進行比較,以紅色反白D 列中的假期(如果有)使用下面提到的步驟:

  • 開啟新規則對話方塊並選擇使用公式決定要設定格式的儲存格< / a>選擇規則類型。
  • 輸入公式=COUNTIF($G$3:$G$5,$D3)
  • 點選格式>填滿並從調色盤中選擇顏色紅色,然後按一下確定< /span>.
條件格式假日亮點

countif() 檢查單元格G3 – G5 中提到的日期與從單元格D3 開始的D 列中提到的日期的出現情形.

5]基於多個條件的日期的條件格式化

讓我們假設一個工作表,其中我們對各種家居用品提出了投訴。該表包含提出投訴的日期以及截止日期和截止日期(如適用)。

使用條件格式,讓我們將已關閉的投訴標記為白色,將已過了截止日期但尚未關閉的投訴標記為紅色,將尚未到達截止日期的投訴標記為藍色。

這個過程將涉及為相同的數據建立兩個條件格式設定規則,一個用於尚未達到截止日期的數據,另一個用於已超過截止日期的數據。為此,

  • 開啟條件格式新規則窗口,然後選擇使用公式決定要設定格式的儲存格選擇規則類型。
  • 輸入下面提到的公式

=AND($I10="",$H10>$K$6)

臨近截止日期的多重標準
  • 點選格式,從調色盤中選擇藍色,然後點選「確定」。
  • 建立另一個具有相同規則類型的新規則並輸入下列公式

=AND($I10="",$H10<$K$6)

多重標準未能滿足截止日期
  • 按一下“格式”,從調色板中選擇“紅色”,然後按一下“確定”。

在此公式中,我們首先檢查「結束日期」列是否為空,以確定投訴是否仍在處理中AND,然後檢查如果到期日大於或小於當前日期,則確定到期日是否接近 ($H10>$K$6) 或超過 ($ H10 <$K$6)。儲存格 K6 中的日期代表目前日期。

6] 突顯即將到來的日期和延誤

為了了解如何在 Excel 工作表中突出顯示與日期相關的延遲,讓我們考慮一個範例,其中我們有一個股票清單及其名稱和到期日期。透過條件格式化,如果我們需要將即將過期的標記為綠色,將已經過期的標記為紅色,

  • 選擇資料範圍並開啟新規則對話框。
  • 選擇僅設定包含的儲存格格式選擇規則類型
  • 編輯規則說明中,選擇單元格值大於=Now()+30(在對應的在下拉清單中)。
  • 點選格式,從調色盤中選擇綠色 然後點選確定
條件格式臨近截止日期
  • 再次開啟新規則並選擇相同的規則類型< a i=4 >如上所述。
  • 編輯規則說明中,選擇單元格值小於各個下拉清單中的=Now()
  • 點選格式並從顏色中選擇紅色托盤,然後按一下確定
多重標準未能按時完成

結論

本教學課程旨在全面介紹可用於在基於日期的 Excel 工作表中套用條件格式的選項和函數。它可以幫助了解如何應用格式化規則來增強資料視覺化和分析。掌握條件格式還可以幫助識別趨勢、檢查截止日期並設定任務優先級,從而幫助做出明智的決策。

如何在 Excel 中根據日期建立條件格式?

首先,選擇日期儲存格以根據日期在 Excel 中建立條件格式。點選“首頁”> ‘條件格式’> 「新規則」。選擇“設定包含的儲存格格式”,在規則下選擇“發生日期”,選擇特定的日期條件,然後透過點擊“格式”設定您的首選格式。點選「確定」即可申請。這可以快速突出顯示資料中的關鍵日期。

如何在 Excel 中設定截止日期的條件格式?

首先,選擇包含日期的儲存格,以在 Excel 中設定截止日期的條件格式。然後,按一下「主頁」標籤中的「條件格式」。選擇“新規則”,選擇“僅設定包含下列內容的儲存格格式”,將規則設為“儲存格值”和“小於”,然後輸入截止日期或公式。按一下“格式”,選擇格式樣式,然後按“確定”套用。

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *