Conditional formatting dates
I would like to find a way to automatically highlight dates in a worksheet that have expired. It is quite a large worksheet and different columns have different expiry dates e.g. some 1 year, some 3 years. So I am just wondering if there is a way to format each column so that the dates in that column automatically turn red after a year of the inputted date without manually creating formula's for each individual cell?
RE: Conditional formatting dates
Hi Jessie,
Thank you for your question. The best way to do this is to make use of a combination of the Edate and Today functions.
Select your first date. (In my example my first date is in A2.) In Conditional Formatting, go to New Rule, then select 'Use a formula to determine which cells to format'. Click in the field below 'Format values where this formula is true'.
In this field, type the following formula: =$A2<EDATE(TODAY(),-12)
**Please note that there is no $ symbol before the 2 in the cell reference, otherwise Excel won't apply the formatting to other rows.**
This formula assumes that you have dates in Column A starting from Row 2. It also assumes you want to format dates older than 1 year (-12). The Edate function returns a date x number of months before or after a given start date. The Today function references the current date. Set the formatting as red. Click OK to close all boxes.
Then, go to the bottom right corner of that cell and drag the fill handle down the column. Select the Auto Fill Options box in the corner and then select 'Fill formatting only'. This should apply your rule to all the dates in that column.
For other columns where you need to look further back in time, simply change the -12 in the formula to -24, -36, etc.
The formula will evaluate all your dates against the current date, so it is dynamic.
I hope this helps!
Kind regards
Marius Barnard
STL
