How to highlight/conditional formatting dates older than 30 days in Excel?
When working with a list of dates in Excel, it's often necessary to highlight dates that are more than 30 days older than today's date. Manually identifying these dates can be time-consuming and error-prone, especially with large datasets. This guide introduces various effective methods for highlighting or managing dates older than 30 days: from using Conditional Formatting for automated highlighting, helper formulas for sorting and flagging, VBA macros for larger or dynamic ranges, and even leveraging specialized tools for streamlined workflows. Understanding these options will help you quickly identify overdue dates, monitor deadlines, and manage time-sensitive data with ease.
Highlight dates older than 30 days with conditional formatting
Easily select and highlight dates older than a specific date with an amazing tool
Automatically highlight dates older than 30 days with VBA macro
Use a helper column formula to flag dates older than 30 days
Highlight dates older than 30 days with conditional formatting
Excel’s Conditional Formatting feature enables users to automatically highlight dates older than 30 days in a selected range. This is especially useful for tracking overdue tasks, managing deadlines, or prioritizing items based on age. Follow the detailed steps below:
1. Select the range containing your dates, then navigate to Home > Conditional Formatting > New Rule. See screenshot:
2. In the New Formatting Rule dialog box, configure the following:
- 2.1) Choose Use a formula to determine which cells to format under the rule type options.
- 2.2) Enter this formula in the box labeled Format values where this formula is true: =A2<TODAY()-30
- 2.3) Click Format to specify the fill color for highlighting old dates.
- 2.4) Click OK to confirm and apply the rule. See screenshot:
Note: In this formula, A2 should be the topmost cell (upper-left corner) of your selected range, and 30 represents the threshold in days. You can adjust both according to your needs. If your data does not start in A2, update the cell reference accordingly. Be sure the reference points to the first row of your range.
Once set up, Excel will highlight all dates in the range that are more than 30 days before today using the color you specified. This instantly draws your attention to items that might require immediate action.
Tip: This formula compares each cell’s date to TODAY() minus 30. If you want to highlight different timeframes (e.g.,60 days), simply change “30” to your preferred number.
If your date list includes blank cells, you may notice that these are sometimes highlighted as well. To avoid highlighting empty cells:
3. Re-select your date range and go to Home > Conditional Formatting > Manage Rules.
4. In the Conditional Formatting Rules Manager, click New Rule to add a new rule for handling blanks.
5. In the Edit Formatting Rule dialog box:
- 5.1) Choose Use a formula to determine which cells to format.
- 5.2) Enter the following formula (replace A2 if your range starts elsewhere): =ISBLANK(A2)=TRUE
- 5.3) Confirm by clicking OK.
6. In the Rule Manager, make sure to select the Stop if True box for the new rule so that blank cells are excluded from other formatting rules. Click OK to finish.
The result: only real date values older than30 days will be highlighted, with blank cells ignored as intended.
Scenario & Tips: Conditional Formatting is ideal for interactive dashboards or reports where visualizing overdue items quickly is important. However, note that very large ranges or complex formatting may impact workbook performance. Always double-check date formats: the rule applies only if cells are recognized by Excel as dates.
Easily highlight dates older than a specific date with an amazing tool
If you need a quick, user-friendly way to select and highlight dates older than a particular date (for example, for custom reporting or manual batch operations), Select Specific Cells in Kutools for Excel offers an efficient solution. With just a few clicks, you can select all date cells older than any specified date, and then highlight or process them as required.
1. Select the date cells, click Kutools > Select > Select Specific Cells.
2. In the Select Specific Cells dialog box, you need to:
- 2.1) Select Cell in the Selection type section.
- 2.2) Choose Less than from the Specific type drop-down list and enter the cutoff date (for example,30 days ago or a specific date) in the box.
- 2.3) Click OK to select all matching date cells.
- 2.4) Confirm the selection count and proceed by clicking OK in the information dialog.
3. With the relevant dates selected, you can apply a fill color by going to Home > Fill Color to visually highlight them as needed.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Automatically highlight dates older than 30 days with a VBA macro
If you are working with large datasets or frequently need to highlight dates relative to today, a VBA macro can automate the process efficiently. This method is particularly valuable when you have very large ranges, want to repeatedly update highlighting, or need to clear previous formatting before applying new highlights.
1. Open the Excel workbook in which you want to apply the highlighting. Access the VBA editor by clicking Developer Tools > Visual Basic. If "Developer" is not visible, enable it from Excel Options. In the VBA window, click Insert > Module.
Sub HighlightOldDates()
Dim WorkRng As Range
Dim Rng As Range
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to check for old dates:", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
' Optional: Clear previous background coloring
WorkRng.Interior.ColorIndex = xlNone
For Each Rng In WorkRng
If IsDate(Rng.Value) Then
If Rng.Value < Date - 30 Then
Rng.Interior.Color = vbYellow ' Or choose any other color you prefer
End If
End If
Next
Application.ScreenUpdating = True
MsgBox "Highlighting complete.", vbInformation, xTitleId
End Sub2. Run the macro by selecting Run (the green triangle button in VBA editor) or press F5 after selecting the module. A dialog will prompt you to select the range of dates you wish to analyze. The macro will automatically clear any previous fill color and highlight cells with dates older than 30 days in yellow (you may modify the color as needed).
Practical notes: - This VBA solution works well for recurring tasks or when analyzing large spreadsheets. - Always save your workbook before running VBA code, especially if using macros that change formatting. - VBA macros require macro-enabled workbooks (.xlsm) and macro-enabled settings. For shared or online workbooks, consider using other methods discussed above.
Troubleshooting: If the macro does not appear to work, ensure date cells are formatted correctly and double-check the selected range. If non-date values are present, they're ignored.
Use a helper column with a formula to flag dates older than 30 days
For more flexibility in identifying old dates—such as filtering, sorting, or triggering additional actions—you can use a helper column with an Excel formula. This method is especially useful when you need to process or analyze the flagged results beyond just coloring cells.
1. Insert a new column next to your list of dates (e.g., if your dates start in column A, add the new column B and label it "Overdue Flag"). In the first row of the helper column (e.g., B2), enter the following formula:
=A2<TODAY()-30This formula checks if the date in cell A2 is more than 30 days before today. If true, it returns TRUE, otherwise FALSE.
2. Press Enter to apply the formula, then copy it down for all the rows in your data range. To do this quickly, select cell B2, drag the fill handle down, or double-click the handle if adjacent data is present.
3. Once complete, you can filter or sort by the TRUE/FALSE values. Rows with TRUE are those with dates older than 30 days.
Practical application: You can now filter the data, apply other formatting rules, or use the flagged column in further calculations or automated processes. This approach is very efficient when you need to take additional steps based on whether a date is overdue, such as generating reports or sending notifications.
Tip: Adjust the 30 in the formula to set a different threshold. Always ensure the formula cell references match your actual data range.
Where this works best: This method gives you granular control and auditability, making it ideal for larger datasets or when working with automated workflows.
When choosing the appropriate highlighting method, consider your needs: Conditional Formatting works best for dynamic visual cues; helper columns allow advanced processing; filter/sort is best for quick reviews without altering the sheet; VBA is perfect for recurring or high-volume tasks; and Kutools for Excel provides fast, flexible selection for manual or batch operations. Always be aware of date formatting and workbook sharing constraints, and save your file before applying changes, especially when using VBA or add-in tools. Combining methods can yield powerful solutions for complex workflows.
Relative Articles:
Conditional format dates less than/greater than today in Excel
This tutorial shows you how to use the TODAY function in conditional formatting to highlight due dates or future dates in Excel in details.
Ignore blank or zero cells in conditional formatting in Excel
Supposing you have a list of data with zero or blank cells, and you want to conditional format this list of data but ignore the blank or zero cells, what would you do? This article will do you a favor.
Copy conditional formatting rules to another worksheet/workbook
For example you have conditionally highlighted entire rows based on duplicate cells in the second column (Fruit Column), and colored the top3 values in the fourth column (Amount Column) as below screenshot shown. And now you want to copy the conditional formatting rule from this range to another worksheet/workbook. This article comes up with two workarounds to help you.
Highlight cells based on length of text in Excel
Supposing you are working with a worksheet which has list of text strings, and now, you want to highlight all the cells that the length of the text is greater than15. This article will talks about some methods for solving this task in Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in