Forum Discussion
Highlight rows in excel based on Month and Year values
HI there, I Have 3 columns in my spreadsheet.
| Registered date | Refresh Date | Age |
| Sep-19 | 01/09/2024 | 4y 0m |
| Sep-19 | 01/09/2024 | 4y 0m |
| Sep-19 | 01/09/2024 | 4y 0m |
| Sep-19 | 01/09/2024 | 4y 0m |
| Sep-19 | 01/09/2024 | 4y 0m |
Column H (Registered Date) - is a date in the format dd/mm/yyyy
Column I (refresh date) - is a calculated date based on formula; =DATE(YEAR(H6) + 5, MONTH(H6), DAY(H6)) - basically adding 5 years to each cell in Column H
Column J - (Age) - is a calculated date in the format on YY and MM and formula is =DATEDIF(H6,$O$3,"y") & "y " & DATEDIF(H6,$O$3,"ym") & "m " where $O$3 is the cell where Todays Date is displayed.
what i would like to do is if Column H Value (Age):
* is less than 3y then highlight the row in green.
* If the value is greater than 3y 00months and less than 4yrs then highlight yellow
* if the value is greater than 4y then highlight in red
would appreciate if someone could guide me in the right direction on how to get started on this.
Thanks
Select the rows that you want to format.
I'll assume that row 6 is the top row of the selection, based on your example formula.
The active cell in that selection should be in the top row, i.e. in row 6.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=DATEDIF($H6,$O$3,"y")<3
Click Format...
Activate the Fill tab.
Select green as highlight color.
Click OK, then click OK again.Repeat these steps, but with
=DATEDIF($H6,$O$3,"y")=3
as formula and yellow as color.
Finally, repeat them again with the formula
=DATEDIF($H6,$O$3,"y")>3
and red as color.
