Highlight the Dates if its over a year

This post will guide you how to highlight the dates that are over 1 year in excel. How do I highlight the dates over a year old using conditional formatting feature in excel. How to use conditional formatting to highlight if the dates are over a year in excel. How to highlight cells color when the date is over a year old in excel.

1. Highlight the Date that Are over 1 Year using Conditional Formating

Assuming that you have a list of date in the range of cells B1:B5, and you would like to make it easy to determine all dates which are older than 1 year, then highlight it. You can use the Conditional formatting feature to highlight the dates over a year old, just do the following steps:

Step1: select the range of cells that contain the dates

highlight dates if its over year1

Step2: go to HOME tab, click Conditional Formatting command under Styles group, then select the New Rule… from the drop-down menu list. The New Formatting Rule dialog will appear.

highlight dates if its over year2

Step3: select Use a formula to determine which cells to format in the Select a Rule type list, and type the following formula =B1<=TODAY()-365 into the textbox of the Format values where this formula is true.

highlight dates if its over year3

Step4: click Format button, the Format Cells dialog will appear.

Step5: switch Fill tab, select one color that you want to highlight the dates. Click OK button.

highlight dates if its over year4

Step6: let’s see the result:

highlight dates if its over year5

You will see that all the dates over a year have been highlighted with a specified color in the range of cells B1:B5.

2. Highlight the Date that Are over 1 year with VBA Code

If you want to highlight the dates that are over a year old using VBA code in Excel, you can follow these steps:

Step1: Open the Excel file that contains the dates you want to highlight.

Step2: Press Alt + F11 to open the Visual Basic Editor.

Adding Comma Character at End of Cells vba1.png

Step3: In the Visual Basic Editor, go to Insert > Module to create a new module.

Adding Comma Character at End of Cells vba1.png

Step4: Copy and paste the following VBA code into the module:

Sub HighlightOverYearDates_ExcelHow()
    Dim myRange As Range
    Set myRange = Application.InputBox("Please select a range of cells", Type:=8)
    
    Dim cell As Range
    For Each cell In myRange
        If IsDate(cell) Then
            If DateDiff("yyyy", cell, Now()) >= 1 Then
                cell.Interior.ColorIndex = 6 ' Change the color index as per your preference
            End If
        End If
    Next cell
End Sub

Step5: Go to Developer > Macros (or press Alt + F8) to open the Macros dialog box. Select the HighlightOverYearDates_ExcelHow macro from the list and click Run.

Highlight the Dates if its over a year vba 2.png

Step6: Select the range of cells that contains the dates you want to highlight.

Highlight the Dates if its over a year vba 3.png

Step7: The dates that are over a year old will be highlighted in the color you specified.

Highlight the Dates if its over a year vba 4.png

3. Video: Highlight the Dates If its over a Year

This video will demonstrate how to highlight dates if they are over a year old in Excel using both conditional formatting and VBA code.

Leave a Reply