Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

conditional formatting dates

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional formatting dates

Conditional formatting dates

ResolvedVersion 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.

Excel tip:

Activating the formula bar with the keyboard in Excel 2010

The formula bar in Excel 2010 contains a number of different formulas. However, it is time consuming to open the formula bar every time and insert the formula using the mouse. Instead, you can activate the formula bar with the keyboard. Simply press the F2 button on the keyboard then the information in the cell will expand allowing you to edit the information in the cell.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.