跳到主要內容

如何在Excel中條件格式日期小於/大於今天?

您可以在Excel中根據當前日期有條件地格式化日期。 例如,您可以格式化今天之前的日期,或者格式化大於今天的日期。 在本教程中,我們將向您展示如何以條件格式使用TODAY函數來詳細顯示Excel中的到期日或將來的日期。

條件格式日期在Excel之前的今天或將來的日期


條件格式日期在Excel之前的今天或將來的日期

假設您有一個日期列表,如下圖所示。 為了讓到期日和將來的日期未清,請執行以下操作。

1.選擇範圍A2:A15,然後單擊 條件格式 > 管理規則首頁 標籤。 看截圖:

2。 在裡面 條件格式規則管理器 對話框中,單擊 新規則 按鈕。

3。 在裡面 新格式規則 對話框,您需要:

1)。 選擇 使用公式確定在“選擇規則類型”中要格式化的單元格 部分;

2)。 對於 格式化比今天早的日期,請複制並粘貼公式 = $ A2格式化此公式為真的值 框;

格式化未來日期,請使用此公式 = $ A2>今天();

3)。 點擊 格式 按鈕。 看截圖:

4。 在裡面 單元格格式 對話框中,指定到期日期或將來日期的格式,然後單擊 OK 按鈕。

5.然後返回到 條件格式規則管理器 對話框。 並創建了到期日期格式規則。 如果您想立即應用規則,請點擊 登記 按鈕。

6.但是,如果要同時應用到期日期規則和將來日期規則,請通過重複上述步驟2至4,使用將來日期格式公式創建新規則。

7.返回到 條件格式規則管理器 再次出現對話框,您可以看到框中顯示了兩個規則,請單擊 OK 按鈕開始格式化。

然後,您可以看到成功格式化了比今天早的日期和比今天大的日期。

輕鬆地在選擇中每隔n行使用條件格式:

Excel的Kutools's 交替的行/列底紋 實用程序可幫助您輕鬆地將條件格式添加到Excel選擇中的每n行。
立即下載Kutools for Excel的全功能30天免費試用版!


相關文章:

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (24)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I want to highlight a cell if:
1. today's date is greater than the date in another cell or blank (=TODAY()>'APM Tracker'P14) AND ALSO;
2. If the date in P15 is greater than P14 same highlight color AND ALSO;
3. If the date in P15 is less than P14 I do not want the highlight
This comment was minimized by the moderator on the site
Hey All,

I want to get only past due dates in the particular column. Instead of highlighting the cells with color i need the dates to be picked in the column

Any solution for this....
This comment was minimized by the moderator on the site
Alright, I read through this thread and did not see this question asked/answered. I have a column of future dates. I would like to add conditional formatting to flag that date exactly 30 days before (1st color "Hey you should get this done its due in 30 day's") and 7 days ( 2nd color "You procrastinated and now you have 7 days to complete this"). Any help here?
This comment was minimized by the moderator on the site
Hi Michael,
I am not sure I understand your question clearly. If you want to hight the date which is 30 days and 7 days from today. Please use the following formulas to create two formula rules with different highlighted colors:
=D9=TODAY()+30
=D9=TODAY()+7

https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/conditional-formatting.png
If this is not what you need, please attach a screenshot of your data here.
This comment was minimized by the moderator on the site
To ignore cells that have blanks try this:

=AND($A2<>"",$A2<TODAY())

This assumes the cell you have selected is A2 (change this to whatever cell you have currently selected)
To copy this to the applicable cells, change the "Applies to" to the cell range you need.
This comment was minimized by the moderator on the site
Hi Andy Campbell,
Thank you for sharing.
This comment was minimized by the moderator on the site
Hi
I am trying to use condition formatting to allow a cell to be highlighted when an item is past due based on different turn around times (TAT). I have a column with a submitted date which is then used with the #of days from the TAT to automatically give the due date. Using the following formula, =IF(OR(E3="",A3=""),"",DATE(YEAR(A3),MONTH(A3),DAY(A3)+E3))
The condition formatting currently used is "cell value < today()" applies to +$G:$G.
What I would like to do is use the stop if rule to stop the condition formatting if the date entered in column "H" is < or = to todays date.
Thanks Michelle
This comment was minimized by the moderator on the site
if i want highlight the due date with the conditional : today less or equal 30 days from due date
This comment was minimized by the moderator on the site
I am trying to create conditional formatting that will highlight rows older than 14 days in yellow and highlight rows older than 21 days in red. My date column starts in A2, I have the following rules applied, but only yellow is highlighting and just the date column, not the entire row. Can you help? Scott

1. =ISBLANK(A2)=TRUE
2. =A2<TODAY()-14
3. =A2<TODAY()-21
This comment was minimized by the moderator on the site
I want to ignore blank cells which doesn't have a date i.e I do not want blank cells to be highlighted when I am comparing dates<Today(). Please help
This comment was minimized by the moderator on the site
Hi Madhu,
The first method in this artice can help you solve the problem. Please have a try.
https://www.extendoffice.com/documents/excel/2499-excel-conditional-formatting-ignore-blank-zero.html
This comment was minimized by the moderator on the site
1 colum has the dates in it i want another colum to change colour when this formula is applied.

any ideas?
This comment was minimized by the moderator on the site
Hi grant,
Supposing you want to change the cell color in column B based on dates in column A, please select the column B cells (such as B2:B21), and apply the conditional formatting function with the above formulas =$A2>Today() or =$A2<Today().
This comment was minimized by the moderator on the site
Hello! can anyone help me?

I have =TODAY() formula in A1 and need 3 more rules to work as per below

rule 1 all dates 8 to 14 days older than today

rule 2 : all dates older 14 to 30 days than today

rule 3: all dates 31 or more days older than today
This comment was minimized by the moderator on the site
it should be something like this:

if(*cell*+8>Today();if(*cell*+14<Today();"8-14 from today";"N/A"))
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations