98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional formatting dates
Conditional formatting dates
Resolved · Medium Priority · Version 2016
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
Fri 23 Feb 2018: Automatically marked as resolved.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Hiding and unhiding columns using the keyboardCTRL + 0 hides your columns and CTRL + SHIFT + ) unhides them although you would need to highlight the column letters either side as per normal |