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.
Table of Contents
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
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.
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.
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.
Step6: let’s see the result:
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.
Step3: In the Visual Basic Editor, go to Insert > Module to create a new module.
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 SubNote: you can modify the ColorIndex property as per your preference. The code above sets the color to yellow.
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.
Step6: Select the range of cells that contains the dates you want to highlight.
Step7: The dates that are over a year old will be highlighted in the color you specified.
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.